calculate column in datagrid

[bsouthuk]

Hi

Wonder is someone can help.

I am trying to calculate a total sum within a COLUMN of a datagrid and am using the following code:

Code: Select all

       set the text of field "Rev12" to \
                  the (uSumOfColumn ["Commission Total"] of group "TariffDataGrid")  

This works perfectly well. However I only want the sum calculated if the text within the field in a different COLUMN within the same row of the datagrid is for instance "TRUE".

If the text is not "True" then this particular row within the datagrid should not be calculated.

Hope this makes sense and would appreciate if somebody could help me with the additional code.

many thanks

Daniel

[dunbarx]

Hi.

I would extract the data in the clear using the "dgText", set my itemDel to tab, and run a loop. You would examine the appropriate item (the "true/false" item) in each line to determine whether to add the summing item. Should take a few ticks to do the whole thing.

I am sure there is a method within the dg itself, using its "native" properties, (and I use datagrids) but I would be more comfortable managing the clear data.

Craig Newman

[bsouthuk]

Hi

Can somebody supply the code thats required here?

[dunbarx]

Hi.

Code: Select all

put the dgText of group "yourDataGrid" into temp
set the itemlDel to tab
repeat with y = 1 to the number of lines of temp
  if item theColumnNumberWithTheConditional of line y of temp = "true" then add item theColumnNumberWithTheData of line y of temp to accum
end repeat
answer accum

Fun. And so compact, in the way only LC can do it.

Craig

[bsouthuk]

Thats great thank you very much.

Though i've realised that what I have is bit more complicated than that.

I need my system to calculate the items in each Line of column 2 if the items in column 1 match and then put the total in the item of column 3.

For example:

COL1 COL2 COL3
1 12 22
1 6 22
1 4 22
2 3 6
2 1 6
2 2 6
3 5 19
3 1 19
3 9 19
3 4 19

Hope this makes sense?

Thank you for your help

Daniel

[dunbarx]

Dan.

So you read down the items in column 1, and sum the adjacent items in column 2 as long as item 1 data are the same? Do I assume that the items in column 1 are always properly sorted? An array would be better if they are not, but that may be another story.

This is only slightly more involved than what I gave earlier. But more importantly, where are you with learning LC? You made your query in an intermediate section, and this is more basic a task. I only ask so that I know whether to annoy you with hints, or give you more.

In any case, you must tell me what you think the procedure ought to be, at least in general procedural terms. Then we can get going...

Craig

[bsouthuk]

Thank you Craig, yes that is correct and the items in column 1 will always be properly sorted.

I have recently started developing again so am very rusty.

I think once I've this repeat loop sorted then it'll help me a lot with other repeat loops that I need for my software.

I appreciate your help on this Craig.

Many thanks

Daniel

[dunbarx]

OK.

Work on it, keep your items in line, and write back if you get stuck.

Craig

[bsouthuk]

Hi Gary

I am completely stuck on this - I just do not know the code to use for this.

I have literally banged my head against the wall over the weekend.

Could you help me with the additional code that's needed?

Many thanks

Daniel

[dunbarx]

Hi.

OK. There are a couple of ways to do this. One involves a careful management of repeat loops. The other uses an array. I took a short cut and used an array. It does a lot of the parsing of data and other stuff by its intrinsic nature.

However, it would be a terrific lesson to do it wholly "in the clear" in a loop. Challenge?

I assume your data is derived from a dataGrid, so that the elements in each line are tab delimited. In a button:

Code: Select all

on mouseUp
   get group "yourDataGrid"
    set the itemDel to tab

   repeat with y = 1 to the number of lines of it
      add item 2 of line y of it to myArray[item 1 of line y of it]
   end repeat
   combine myArray by return and tab
   
   repeat with x = 1 to the number of lines of it
      put the last item of line lineOffset(item 1 of line x of it,myArray) of myArray into item 3 of line x of it
   end repeat
   answer it
end mouseUp

Try this with your example data (again, assuming it is tab and return delimited), and step through the handler. Several wordy hints and tips in here; watch how variables develop.

Craig

[Klaus]

Code: Select all

...
get group "yourDataGrid"
...

Come on, Craig, you can do better!

[dunbarx]

Hmmm.

Code: Select all

put grp "yourDataGrid" into klaus

??

[Klaus]

Hmmm.

Code: Select all

put grp "yourDataGrid" into klaus

??

Ts, ts, ts...

What's wrong with:
...
put the dgtext of grp "yourDataGrid" into klaus
...
?
The variable must not neccessarily be named "klaus", of course, but "klaus" is much better than "IT"

[dunbarx]

Klaus



You know, I did not have his dataGrid, of course, so simply made up a little data of my own. Worked perfectly, of course, and I just, you know, stuck a line or two into the handler to accommodate.

Dan.

You absolutely, positively, have to:

Code: Select all

on mouseUp
   get the dgText of group "yourDataGrid"
    set the itemDel to tab

   repeat with y = 1 to the number of lines of it
      add item 2 of line y of it to myArray[item 1 of line y of it]
   end repeat
   combine myArray by return and tab
   
   repeat with x = 1 to the number of lines of it
      put the last item of line lineOffset(item 1 of line x of it,myArray) of myArray into item 3 of line x of it
   end repeat
   answer it
end mouseUp

[dunbarx]

Klaus.

but "klaus" is much better than "IT

I just re-read. The problem was that just getting the dataGrid would have placed an array into it. I needed to have that info in the clear.

It wasn't just "it" that bugged you, was it? Though I agree that "it" is fragile.

Craig