How to retrieve a column array from a 2-D array?

[sritcp]

The manual doesn't give much info about arrays, so I have a question for you:

Consider a 2-dimensional array; the rows are records (1,2,3,....); the columns are info types ("name", "email", "phone", ...)
Now, tArray[1] will return an array containing the first record -- the value of each column for that record.
If I wanted to refer/retrieve just the emails of all the records as a sub-array of the larger array (i.e., a column array), is that possible?
In other words, is there a way to work with a whole column of a table of data?

Thanks,
Sri.

[atout66]

Hi Sri,

Could you provide an example of the array you plane to build ?
Is it something like:
put "name" into tArray[1][1]
put "email" into tArray[2][1]
put "phone" into tArray[3][1]
?

Kind regards, Jean-Paul.

[sritcp]

Hi Jean-Paul:

I have over 1500 records ("rows") of people who have attended our workshops.
Each row has about 14 columns: last name, first name, email address, .........
I will need to be able to add new people, make collections of people who have registered for a particular workshop, etc.
I would often need to copy a column (copy the email column and paste it into an email, to send a bulk email, for instance).

Until recently, I was using Bento (made by FileMaker people). As you may be aware, they have recently discontinued Bento since last year. Now, the old Bento version is struggling to work with newer OSX versions, especially Mavericks. There are no adequate replacements in the market for Bento. I tried TapForms, but each minor change (such as changing the column display) takes over 5 minutes to update! It is not a professional product.

So, I decided to make myself a Bento replacement using LiveCode. That is the background story!

In this task, I find that the array manipulation capabilities of LiveCode are inadequate. I understand, of course, that LiveCode was not built for such purposes. (I am not a programmer in the traditional sense, but 20 years ago, I used to use Gauss, a matrix language for my academic research. That must have colored my expectation). I can process element by element, of course, but I think that would be very inefficient.

Thanks for listening,
Sri.

[FourthWorld]

Associative arrays are useful for many things, but they are not intrinsically a database, and are not designed for easy querying across all elements.

With only 1500 records you may find it very easy to just use a tab-delimited list, or if you're thinking about future scalability you may consider storing the data in an SQLite database.

If simple delimited text will do the trick:

Code: Select all

function GetColumn pColNum, pData
  set the itemdel to tab
  repeat for each line tLine in pData
    put item pColNum of tLine &cr after tList
  end repeat
  delete last char of tList
  return tList
end GetColumn

Then again, doing that with an array will also take only a millisecond or two:

Code: Select all

function GetArrayColumn pColName, pDataA
   repeat for each element pElemA in pDataA
      put pElemA[pColName] &cr after tList
   end repeat
   delete last char of tList
   return tList
end GetArrayColumn

[sritcp]

Thanks, Richard.
Yes, I knew I could retrieve a column element by element; I was wondering if there was an in-built, faster way to reference a column directly.
I wondered if element-wise enumeration would be inefficient, but the dictionary does say that the use of the element keyword (in your example above) is "much faster" than manually iterating.
I assume this is not the case if I used the basic table.

Also, I should be able to manipulate the order of the columns in sub-collections (tables made out of selected rows and columns of the master data table). I see that I can do this by resetting the dgProp["columns"] property when I use an array. Not so simple with basic table, I guess.

Regards,
Sri.

[sritcp]

It may be a good idea for LiveCode to add a special form of Array named "Matrix," a two-dimensional array where the row keys are sequential numbers and the column keys can be unordered strings.

Add syntax to manipulate columns and rows, as well as basic spreadsheet-like operations where the data type permits.

I see a good cost-benefit ratio for such an addition.

Regards,
Sri.

[FourthWorld]

I was wondering if there was an in-built, faster way to reference a column directly.

How fast do you need it?

I wondered if element-wise enumeration would be inefficient, but the dictionary does say that the use of the element keyword (in your example above) is "much faster" than manually iterating.
I assume this is not the case if I used the basic table.

I don't understand; the manipulation of data in memory is a separate matter from its display in an object like a field.

Also, I should be able to manipulate the order of the columns in sub-collections (tables made out of selected rows and columns of the master data table). I see that I can do this by resetting the dgProp["columns"] property when I use an array. Not so simple with basic table, I guess.

The DataGrid was written in LiveCode. Anything it does, you can do for other purposes however you need.

[sritcp]

I don't understand; the manipulation of data in memory is a separate matter from its display in an object like a field.

Oh, I just meant that manipulating an array (using "for each element" as you have demonstrated) is probably much faster than manipulating a list (which underlies a Basic Table).

As for manipulation of columns, I was exploring if it would be possible to drag the columns to re-order them, as in Bento (not possible with DataGrid). Of course, I understand this is a UI issue, not a data manipulation issue.

Thanks,
Sri.

[bn]

Hi Sri,

have a look at the attached stack. It is not Bento but a simple field that you fill with 2000 records and 4 columns. Then you switch column 1 and 4.
It indicates the time it takes.

If you want to go the Livecode way you will have to code. On the other hand there is still e.g FileMaker

Kind regards

Bernd

[sritcp]

Hi Bernd:

Thanks, I'll take a look at your stack.

The problem is not only switching columns (that was an example) but many things: e.g., making a collection of a subset of records and columns, and still be linked to the master data -- so that, when I edit a collection, the master data is updated. I am trying to code these in LiveCode; let's see how it goes!

Bento and FileMaker are made by the same company. FileMaker too expensive (and feature-rich) for my needs.

Regards,
Sri.

[jacque]

It may be a good idea for LiveCode to add a special form of Array named "Matrix," a two-dimensional array where the row keys are sequential numbers and the column keys can be unordered strings.

Add syntax to manipulate columns and rows, as well as basic spreadsheet-like operations where the data type permits.

I see a good cost-benefit ratio for such an addition.

Regards,
Sri.

Just a note that it's already possible to create an array with numbered keys. If you do not provide a secondary delimiter, that's what you get.

For the subject of this topic, see "combine" in the dictionary, particularly "combine by column", which could be helpful in extracting the column you want.

[sritcp]

Hi Jacque:

Thanks for the tip. However, I am having some trouble understanding what the dictionary says:
Combining an array by row converts the array into a table with rows separated by the rowDelimiter property. Each row in the resulting string is the contents of the corresponding key in the array.
Combining an array by column converts the array into a table with columns separated by the columnDelimiter property. Each column of the resulting string is the contents of the corresponding key in the array.

I tried the following code and it doesn't work. Where am I going wrong?

Code: Select all

on mouseUp
local a
   
   put "blue" into a[1][1]
   put "green" into a[2][1]
   put "yellow" into a[3][1]
   put "red" into a[4][1]
   put "lemon" into a[1][2]
   put "orange" into a[2][2]
   put "peach" into a[3][2]
   put "pineapple" into a[4][2]
   
   combine a by row
   put a into msg
end mouseUp

Thanks,
Sri

[jacque]

Say I create an array, for example:

Code: Select all

 put "blue,green,yellow,red,lemon,orange,peach,pineapple" into tData
 split tData by comma

gives a numbered array with the color names as the element contents. If you combine that by row, you get back a list of elements, basicalliy arranged in a column:

blue
green
yellow
red
lemon
orange
peach
pineapple

If instead you combine the array by column, you get a single row with the column data:

blue green yellow red lemon orange peach pineapple

The default delimiter is tab, but if I'd set it to comma the returned list would have been identical to the original.

[sritcp]

Hi Jacque:

I see that this works with a one-dimensional array.
It doesn't seem to work with 2-D array, even if the keys are numerical (as my example in my previous post shows).

I think, in such cases, we can map a given 2-D array to another 2-D array with numeric keys and numeric data.
We can then use the 'transpose' function to extract one or more columns and then map back to the original array.

Regards,
Sri.

[jacque]

Oh right. Combine only works with a one dimensional array. So I guess you'll have to to try your second method.