## Data Grid - how to count the data per column

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Data Grid - how to count the data per column

Hi Everyone,

Please teach me on how to count the data per column in datagrid. i was able to get on how to count the number of lines per row. on my example below i want to know the total number of male and female under the column of gender in data grid..

Thank you in advance.
Attachments
Thank you & God Bless Everyone

Regards,
lemodizon

Klaus
Posts: 12075
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

### Re: Data Grid - how to count the data per column

Hi Lemuel,

one way to solve this problem:

Code: Select all

``````   ...
## dgText = TAB and CR delimited TEXT representation of data in a datagrid!
## But we fetch the datagrid data as an ARRAY:
put the dgdata of grp "your datagrid here..." into tData

## Now we loop through that ARRAY and check all the "Gender"
put 0 into tMales
put 0 into tFemales
repeat for each key tKey in tData
put tData[tKey]["Gender"] into tGender
if tGender = "Male" then
add 1 to tMales
end if

## Maybe we could just use an ELSE case, but maybe some entries are just empty!?
## So better check explicitely for FEMALE also
if tGender = "Female" then
add 1 to tFemales
end if
end repeat
answer "Number of males:" && tMales & CR & "Number of females:" && tFemales
...``````
Out of my head, but should do the job.

Best

Klaus

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi Klaus,

Thank you for the information and for the script. i was able to understand how to fetch the data in data grid thru your script and through the guide of script editor (debug: breakpoint).

I want to put a background color in each cell for ex.if female is color Pink then Male is Blue. i tried to input in your example script below

Code: Select all

``````   ...
## dgText = TAB and CR delimited TEXT representation of data in a datagrid!
## But we fetch the datagrid data as an ARRAY:
put the dgdata of grp "your datagrid here..." into tData

## Now we loop through that ARRAY and check all the "Gender"
put 0 into tMales
put 0 into tFemales
repeat for each key tKey in tData
put tData[tKey]["Gender"] into tGender
if tGender = "Male" then
add 1 to tMales
set backgroundcolor....  cell into Blue
end if

## Maybe we could just use an ELSE case, but maybe some entries are just empty!?
## So better check explicitely for FEMALE also
if tGender = "Female" then
add 1 to tFemales
set backgroundcolor... cell into Pink
end if
end repeat
answer "Number of males:" && tMales & CR & "Number of females:" && tFemales
...

``````
just like in this link viewtopic.php?f=7&t=13025&p=156188#p62226

Thank you & God Bless Everyone

Regards,
lemodizon

Klaus
Posts: 12075
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

### Re: Data Grid - how to count the data per column

Hi Lemuel,

just like in that link you can only do this -> on FillInData:

Code: Select all

``````on FillInData pData
if pData["gender"] = "Male" then
set the backgroundcolor of fld "gender" of me to 0,0,0
end if
## etc...
end FillInData``````
Best

Klaus

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi Klaus,

i tried it but it doesn't work.. how come there is a field in the data grid control?
Thank you & God Bless Everyone

Regards,
lemodizon

bangkok
VIP Livecode Opensource Backer
Posts: 926
Joined: Fri Aug 15, 2008 7:15 am

### Re: Data Grid - how to count the data per column

This lesson explains how to do it :

http://lessons.livecode.com/m/datagrid/ ... -to-a-cell

or the stack here (card "colors and drag drop") : DG_EXAMPLES 1.4

viewtopic.php?f=7&t=15540

Klaus
Posts: 12075
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

### Re: Data Grid - how to count the data per column

Hi Lemuel,
lemodizon wrote:
Mon Sep 09, 2019 5:31 am
Hi Klaus,

i tried it but it doesn't work.. how come there is a field in the data grid control?
yep, sorry, I had a datagrid of type FORM in mind, but you have a TABLE there.

OK, so you can do it either with a custom behavior as the lesson Bangkok pointed to or you could:
1. Create a function which calculates the age from a given date.
You have done this as we know

2. Then use this function in a repeat loop whcih will calculate the age "on the fly", something like:

Code: Select all

``````...
put the dgText of grp "your datagrid here..." into tData
set itemdel to TAB
## Now loop though all lines and create a new list with the calculated age:
repeat for each line tLine in tData
put item 1 of tLine into tDate

## We pass the date to your functiuon, which will return the correct age:
put your_age_function_here(tDate) into tAge
put tDate & TAB & tAge & CR after tNewList
end repeat

## Get rid of trailing CR
delete char -1 of tNewList

## Now we just replace the complete content of the datagrid with the new list:
set the dgtext of grp " "your datagrid here..." to tNewList
...``````
Maybe in this case this is a bit easier than to create a new custom behaviour for your column.

Best

Klaus

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi bangkok,

Thanks for the examples regarding datagrid. its a big help to us.
bangkok wrote:
Mon Sep 09, 2019 4:38 pm
This lesson explains how to do it :

http://lessons.livecode.com/m/datagrid/ ... -to-a-cell

or the stack here (card "colors and drag drop") : DG_EXAMPLES 1.4

viewtopic.php?f=7&t=15540
i will try to insert in my example.
Thank you & God Bless Everyone

Regards,
lemodizon

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi Klaus,

Thank you very much i tried to input my age function it works by changing the date in my computer

My only concern is the leap year it doesn't work in my age function

Klaus wrote:
Mon Sep 09, 2019 5:53 pm
Hi Lemuel,
lemodizon wrote:
Mon Sep 09, 2019 5:31 am
Hi Klaus,

i tried it but it doesn't work.. how come there is a field in the data grid control?
yep, sorry, I had a datagrid of type FORM in mind, but you have a TABLE there.

OK, so you can do it either with a custom behavior as the lesson Bangkok pointed to or you could:
1. Create a function which calculates the age from a given date.
You have done this as we know

2. Then use this function in a repeat loop whcih will calculate the age "on the fly", something like:

Code: Select all

``````...
put the dgText of grp "your datagrid here..." into tData
set itemdel to TAB
## Now loop though all lines and create a new list with the calculated age:
repeat for each line tLine in tData
put item 1 of tLine into tDate

## We pass the date to your functiuon, which will return the correct age:
put your_age_function_here(tDate) into tAge
put tDate & TAB & tAge & CR after tNewList
end repeat

## Get rid of trailing CR
delete char -1 of tNewList

## Now we just replace the complete content of the datagrid with the new list:
set the dgtext of grp " "your datagrid here..." to tNewList
...``````
Maybe in this case this is a bit easier than to create a new custom behaviour for your column.

Best

Klaus
Thank again have a nice day
Thank you & God Bless Everyone

Regards,
lemodizon

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi Klaus,

I tried this script code it doesn't work.
Klaus wrote:
Sun Sep 08, 2019 1:28 pm
Hi Lemuel,

just like in that link you can only do this -> on FillInData:

Code: Select all

``````on FillInData pData
if pData["gender"] = "Male" then
set the backgroundcolor of fld "gender" of me to 0,0,0
end if
## etc...
end FillInData``````
Best

Klaus
Thank you & God Bless Everyone

Regards,
lemodizon

Klaus
Posts: 12075
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

### Re: Data Grid - how to count the data per column

lemodizon wrote:
Wed Sep 11, 2019 6:11 am
Hi Klaus,

I tried this script code it doesn't work.
Klaus wrote:
Sun Sep 08, 2019 1:28 pm
Hi Lemuel,

just like in that link you can only do this -> on FillInData:

Code: Select all

``````on FillInData pData
if pData["gender"] = "Male" then
set the backgroundcolor of fld "gender" of me to 0,0,0
end if
## etc...
end FillInData``````
Best

Klaus
Yes, but you DID read my last posting?

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi Klaus,

Sorry my mistake.... I thought i have to create a custom function in my button so that it will display the color in a particular cell of the datagrid and I was not able to checked the link and sample datagrid given by bangkok now i know there is called "default column behaviour" that's why there is a fld in the sample script. sorry again.

Code: Select all

``````on FillInData pData
-- This message is sent when the Data Grid needs to populate
-- this template with the column data. pData is the value to be displayed.
set the text of the long ID of me to pData ## temp workaround for

set the text of field 1 of me to pData

if the text of field 1 of me is "MALE" then
set the opaque of me to true
set the backgroundColor of me to 182,216,255
else
if the text of field 1 of me is "FEMALE" then
set the opaque of me to true
set the backgroundColor of me to 251,182,255
end if
end if

end FillInData
``````
Capture.PNG (10.68 KiB) Viewed 2188 times
Thank you for the help Klaus and Bangkok.
Thank you & God Bless Everyone

Regards,
lemodizon

Klaus
Posts: 12075
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

### Re: Data Grid - how to count the data per column

Glad you got it working!

lemodizon
Posts: 77
Joined: Thu Apr 05, 2018 3:33 pm

### Re: Data Grid - how to count the data per column

Hi everyone,

I'm still exploring the data grid of livecode. i created a sqlite database see picture above. where i click the button check age it will automatic update the age. my problem is how can i put the updated age to my sqlite database per column. excuse me in my coding if you find it messy.

Code: Select all

``````////here is the code in my checkage button
on mouseUp
put the dgText of grp "StudentList" into tData
set itemdel to TAB

repeat for each line tLine in tData
put item 5 of tLine into tDate
put age(tDate) into tAge
put  item 1 of tLine  & tab & item 2 of tLine  & tab&  item 3 of tLine  & tab&  item 4 of tLine  & tab & tDate & Tab &tAge & cr after tNewList

end repeat

if  item 1 of tLine is not empty then
UpdateStudentInfo item 1 of tLine,  tAge
else
exit top
end if

delete char -1 of tNewList
set the dgtext of grp "StudentList" to tNewList
end mouseUp

``````

Code: Select all

``````////here is the command to update in the database

[img][/img]
local tSQLStatement
global sDatabaseID

repeat with y = 1 to the number of lines of tData
put "UPDATE MyStudents SET  Age = '"&pStudentAge&"' WHERE ID = '"&pStudentID&"' " into tSQLStatement
revExecuteSQL sDatabaseID, tSQLStatement

end repeat

if the result is an integer then
answer "Age successfully UPDATED!"
else
answer "Sorry, there was a problem in UPDATING the information"
end if