Page 1 of 1

Data Grid - how to count the data per column

Posted: Sat Sep 07, 2019 11:46 am
by lemodizon
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.

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

Posted: Sat Sep 07, 2019 12:01 pm
by Klaus
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

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

Posted: Sun Sep 08, 2019 6:40 am
by lemodizon
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

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

Posted: Sun Sep 08, 2019 1:28 pm
by Klaus
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

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

Posted: Mon Sep 09, 2019 5:31 am
by lemodizon
Hi Klaus,

i tried it but it doesn't work.. how come there is a field in the data grid control?

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

Posted: Mon Sep 09, 2019 4:38 pm
by bangkok
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

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

Posted: Mon Sep 09, 2019 5:53 pm
by Klaus
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

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

Posted: Wed Sep 11, 2019 5:15 am
by lemodizon
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.

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

Posted: Wed Sep 11, 2019 5:25 am
by lemodizon
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

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

Posted: Wed Sep 11, 2019 6:11 am
by lemodizon
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

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

Posted: Wed Sep 11, 2019 9:10 am
by Klaus
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?

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

Posted: Wed Sep 11, 2019 9:49 pm
by lemodizon
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 7069 times
Thank you for the help Klaus and Bangkok.

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

Posted: Thu Sep 12, 2019 11:41 am
by Klaus
Glad you got it working!

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

Posted: Thu Sep 12, 2019 5:13 pm
by lemodizon
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