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: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
lemodizon
Posts: 175
Joined: Thu Apr 05, 2018 3:33 pm

Data Grid - how to count the data per column

Post by lemodizon » Sat Sep 07, 2019 11:46 am

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
Capture1.PNG
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

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

Post by Klaus » Sat Sep 07, 2019 12:01 pm

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: 175
Joined: Thu Apr 05, 2018 3:33 pm

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

Post by lemodizon » Sun Sep 08, 2019 6:40 am

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

thanks in advance
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

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

Post by Klaus » 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

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

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

Post by lemodizon » 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?
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

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

Post by bangkok » 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

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

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

Post by Klaus » 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

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

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

Post by lemodizon » Wed Sep 11, 2019 5:15 am

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 :wink:

Regards,
lemodizon

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

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

Post by lemodizon » Wed Sep 11, 2019 5:25 am

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 :wink:

Regards,
lemodizon

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

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

Post by lemodizon » 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
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

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

Post by Klaus » Wed Sep 11, 2019 9:10 am

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: 175
Joined: Thu Apr 05, 2018 3:33 pm

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

Post by lemodizon » Wed Sep 11, 2019 9:49 pm

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
Capture.PNG (10.68 KiB) Viewed 7059 times
Thank you for the help Klaus and Bangkok.
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

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

Post by Klaus » Thu Sep 12, 2019 11:41 am

Glad you got it working!

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

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

Post by lemodizon » Thu Sep 12, 2019 5:13 pm

SqliteDb.PNG
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.

Thanks in advance.

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]
command UpdateStudent pStudentID,pStudentAge
   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
end UpdateStudent

Thank you & God Bless Everyone :wink:

Regards,
lemodizon

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”