Jump to content
Sign in to follow this  
thulli

how to group records and find a sum for each group

Recommended Posts

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 wink.gif

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.