how to group records and find a sum for each group

[thulli]

Hi,

I' ve got an Order that has many lines of product ordered.

I would like to apply a rebate based on this condition :

find sets of records (of the Line table) that have the same value for 2 fields (group, template). I imagine doing that with a GROUP BY clause in SQL.

and among each of these sets, calculate the sum of a field (quantity ordered) and copy it to another field ("fake" quantity) into each of the lines belonging to this set.

And then, depending on the fake quantity, I can apply the rebate but it's another story

I'm new to FM and its scripts so I don't know how to do it but I already wrote this algorithm in PHP.

Thanks for your help

[Fitch]

One way to do it is to use a couple of global fields, gGroup and gTemplate,

and a relationship with multiple criteria, ( order ID = order ID, gGroup = group, gTemplate = template ),

so your script would be:

Set field ( gGroup, "valueA" )

Set field ( gTemplate, "valueB" )

Set field ( fake quantity, related::quantity ordered )

Hope that makes sense... not sure I correctly interpreted the question.

[thulli]

There's a misunderstanding. Let me show you an example

definition of a Line :

totalQty Number

fakeQty Number

group Number

template Text

before running this script, totalQty, group and template are filled :

12 |  | 1 | A

5  |  | 2 | A

10 |  | 2 | B

6  |  | 1 | A

9  |  | 2 | B

first step : get the lines above grouped by group and template :

12 |  | 1 | A

6  |  | 1 | A



5  |  | 2 | A



10 |  | 2 | B

9  |  | 2 | B

second step : add the totalQty values for each group and put it into the fakeQty field :

12 | 18 | 1 | A

6  | 18 | 1 | A



5  | 5 | 2 | A



10 | 19 | 2 | B

9  | 19 | 2 | B

Is this clear now? I think the first step can be done with summary but I have no idea how to manipulate the results after.

[Fitch]

For future reference, you should be aware of the GetSummary ( summaryField ; breakField ) function. However, the multi-criteria relationship outlined above can be workable for this, either as

a. a calculated field in the related file, Sum(related::totalQty), using a self-relationship ( order ID = order ID, group = group, template = template ), or;

b. a script that runs in the related file, setting the fakeQty based on the relationship above, or;

c. a script that loops through the portal, setting the fakeQty based on a similar relationship defined in the parent db. This would be a slightly more complex script. I think we'd still need globals as in my original post:

Go to portal row(first)

Loop

. Set field ( gGroup, line::group )

. Set field ( gTemplate, line::template )

. Set field ( fake quantity, Sum( related::quantity ordered ) )

. go to portal row(next; exit after last)

End Loop

[thulli]

Thank you very much Fitch for your help.

I' m trying to understand what is the related table you are speaking about in the a or b solutions. The only relationship I've got is the one to link a Line to an Order but here it's not used at all.

[Fitch]

In FileMaker 7, relationships can have multiple criteria. This can be a powerful way to query or summarize records. For example to find all records between a given start and end date for a particular location, you would make a relation where ( selectedDate >= startDate, selectedDate <= endDate, location = location ). This could be used for a portal, or for an aggregate function e.g. Sum(related::price), etc. You can have as many relationships as you like between any two tables, or a single table and itself. Create a new Table Occurrence in the Graph -- a TO in the TOG as we say -- and create a relationship using the criteria I described previously.

[thulli]

Thank you fitch.

I succeeded in making this thing works.

I needed to create a layout in order for this script to run but at least it works