LiveCode Forums.

Posted: **Tue Feb 07, 2012 7:06 pm**

First off I am a new to LiveCode and still evaluating it.

I have been trying to put data from a big database into a list with 2 columns.
This I am able to do with no problem. I query the database and place the data
using: put rev/DataFromQuery(tab, return, sDatabaseID, tSQLQuery) into field "CGNameListField"

Now this works just fine. It puts 2 items of data (CGName and CGID) into 2 columns in the CGNameListField.
Only the CGName is viewable to the user.

Now this is where my problem starts. I need to query the database for 3 items of data and display
it into the 2 columns. I know how to set up the query to get the 3 items of data but I can't figure
out how to display it in the 2 column CGNameListField as I want.

What I need to show in the list is the date and CGName in the first column and the CGID in the second column.
There can be many lines of this data so somehow I need to loop and place the data into the CGNameListField
for each record. Column 2 is never shown to the user. It is the ID number for each record.

CGNameListField
Column 1...............|Column 2
--------------------------|--------
10/20/2012 CGName |CGID
10/20/2013 CGName |CGID
10/20/2014 CGName |CGID

Can I do this or am I stuck using: put rev/DataFromQuery(tab, return, sDatabaseID, tSQLQuery) into field "CGNameListField"

Posted: **Tue Feb 07, 2012 7:43 pm**

Shouldn't be too difficult. Heres 1 way to do it, i'm sure there are tons of others. (untested but should work as written)

Instead of putting the data directly into the field with using: put rev/DataFromQuery(tab, return, sDatabaseID, tSQLQuery) into field "CGNameListField"

put it into a temp variable instead and then:

Code: Select all

set the itemdelimiter to tab
repeat for each line tLine in tempVar -- grab each line and put it into tLine
put item 1 of tLine & space & item 2 of tline & tab & item 3 of tLine & return after newTempVariable --split it up as needed
end repeat
delete the last char of newTempVariable --delete the extraneous return

put newtempVariable into field "CGNameListField" -- fill up the field.

Posted: **Tue Feb 07, 2012 8:44 pm**

Thank you Sturgis

That worked very good and is exactly what I needed.

Very much appreciated.

Posted: **Tue Feb 07, 2012 9:37 pm**

The SQL concat function is your friend.

SELECT CGName,Concat(CGID,' - ',theThirdColumn) from myTable

Posted: **Tue Feb 07, 2012 9:56 pm**

Oh cool! Thanks bangkok! I'll file this away. NOw that I know what to look for, apparently || is a concat operator for sqlite so you could do

select firstcolumn || ' ' || secondcolumn, thridcolumn from tablename -- the ' ' is to put a space between first and second column.

Should be MUCH easier, and faster than doing the loop method. Thanks again for the pointer bangkok!

LiveCode Forums.

SQL / List help needed

SQL / List help needed

Re: SQL / List help needed

Re: SQL / List help needed

Re: SQL / List help needed

Re: SQL / List help needed