(SOLVED) Change all values of Elements in Key of Array

[Xero]

Hi,
I feel like this should be a really straight forwards process, but for the life of me, I can't work out how to get it done! Arrays!!!
Ok, I have a database of accounts (SQL), that I am loading into a livecode file and displaying in a datagrid. I have scripted all of the SQL search, change, write, etc. functions and they are working well. The data looks a little like this:
ID Date Name Paid Notes
1 31/1/2020 John Blog $12.00 Nil
2 25/2/2020 Jane Blog $15.00 Nil
...
THEN... I decide I need to report a date segment (financial year, etc.), and I search my SQL, only to find that, even though the "Date" column is formatted for Dates, the actual data doesn't fit the SQL format of YYYY-MM-DD (it's not DD/MM/YYYY, like a normal human!) So, I can't search the SQL to get into the datagrid to show me the report.
Grrr...
So now I am going through to change the date format so it can be recognised by SQL. Easy... but I have 3 years of accounts, and way too many to bother doing by hand 1 by 1.
What I essentially need to do is this:
Search all entries in SQL (can do, easy as)
Display all entries in Datagrid on screen (can do.)
Go through each item of the 'Date' column and reorganise the items and change the delimiters. (maybe... and where I am getting stuck.)

Here's my thoughts:
Code a temporary button that does this:

Code: Select all

on mouseup
put the dgdata of group "Accounts" into theDataA --put into array
repeat for each element of theDataA["Date"]
set the item delimiter to "/"
put item 3 of ??? &"-"& item 2 of ??? &"-"& item 1 of ??? into NewDate --not sure what to reference here! Item 1 of which element?
put newDate into ??? --not sure where to put it once I have it!
end repeat
--put this back into the datagrid "accounts"
end mouseup

Then use my SQL update script to update all of the entries in the SQL database

Is this the way to do it? Is there an easier way, or am I just over-complicating it? Should I just do it in the SQL? Can I do it in the datagrid, or do I need to go through the array to change it?

Thanks in advance!
X

[dunbarx]

Hi,

You are mixing array stuff with "ordinary" stuff.

To transpose ordinary data, say, "X,Y,Z" into "Y,Z,X" you migh (Pseudo)t:

Code: Select all

put item 2 of the original into item 1 of the final
put item 3 of the original into item 2 of of the final
put item 1 of the original into item 3 of the final

But if you want to stay in array notation(pseudo):

Code: Select all

put myArray[ID] into myArray{Date]
 etc.

But this requires more work than appears here.

The real problem, as I see it, is that the element you are trying to transpose contains multiple ordered items. It is a single element of the array to be sure, but has internal complexity. That is not easy to manage at all.

So I would get the "dgText" of the dataGrid, which yields an "ordinary", not an array, variable, do the transposing, and then set the dgData of the dataGrid to the now reworked dataSet. Stay away from fooling around inside the array entirely.

Craig

[Klaus]

Hi Xero,

not sure, but sounds like this is what you are after:

Code: Select all

on mouseup
   put the dgdata of group "Accounts" into theDataA --put into array
   put the keys of theDataA into tKeys
   
   ## Only neccessary ONCE and outside of the loop:
   set the itemdelimiter to "/"
   
   ## Now we loop through the complete array and modify the DATE columns
   repeat for each line tKey in tKeys
      put theDataA[tKey]["Date"] into tDate
      put item 3 of tDate &"-"& item 2 of tDate &"-"& item 1 of tDate into NewDate --not sure what to reference here! Item 1 of which element?
      
      ## Not sure what exactly you want here?
      ## put newDate into ??? -
      ## If you want to just replace/overwrite the previous content of that column you do:
      put tNewData into theDataA[tKey]["Date"]
   end repeat
   
   ## put this back into the datagrid "accounts"
   set the dgdata of group "Accounts" to theDataA
end mouseup

"repeat for each element tElement in tArray" is not the right way for this task.

Best

Klaus

[Xero]

Thanks Craig.
I think I have no idea what you just said.
However,
I managed to get a script written that kind of does what I need it to.
But kind of doesn't...

Code: Select all

on mouseup
   repeat with x= 1 to the number of fields of group "Accounts"
      if field x of group "Accounts" contains "/" then
         set the itemdelimiter to "/"
         put item 3 of field x of group "Accounts" &"-"& item 2 of field x of group "Accounts" &"-"& item 1 of field x of group "Accounts" into newDate
         put newDate into field x of group "accounts"
      end if
   end repeat
end mouseup

When I do this, I get the datagrid on screen to show what I want it to, showing 02/07/2018 as 2018-07-02... but
When I scroll down, the rest of the dates show the old way. When I scroll up, the ones that changed have changed back again...
What am I missing?
X

[Xero]

Danke Klaus!
here's the code I ended up using to change the date in each line from dd/mm/yyyy to yyyy/mm/dd

Code: Select all

   put the dgdata of group "Accounts" into theDataA --put into array
   put the keys of theDataA into tKeys
   set the itemdelimiter to "/"
   repeat for each line tKey in tKeys
      put theDataA[tKey]["Date"] into tDate --work on Date column only
      put item 3 of tDate &"-"& item 2 of tDate &"-"& item 1 of tDate into NewDate -- take the year and put it first, then month, then day
      put NewDate into theDataA[tKey]["Date"] -- put the new date into the same field that it just came from (i.e. change it.)
   end repeat
   set the dgdata of group "accounts" to theDataA --show new data in datagrid
end mouseup

Worked a treat!
X

[dunbarx]

Hi.

Good work.

But you did exactly what I said, to work outside the array. You extracted the element (turning it into and "ordinary" variable) transposed it, and put it back into the array.

The important thing to learn is that if you have "X,Y,Z" in myArray["test"], you cannot easily modify the three items there; the element "test" of that array does not respond to the text and chunk power within LiveCode. Only ordinary text, or the contents of an ordinary variable, do.

Perhaps that is also not particular clear.

Craig

[dunbarx]

When I do this, I get the datagrid on screen to show what I want it to, showing 02/07/2018 as 2018-07-02... but
When I scroll down, the rest of the dates show the old way. When I scroll up, the ones that changed have changed back again...
What am I missing?

You probably need to refresh the dataGrid:

Code: Select all

send "resetList" to group "yourDataGrid" 

This is required every time you mess with small pieces of a dataGrid, as opposed to setting the dgText or dgData, which does it automatically.

Craig