Caculation fields that find

[Andy_]

Hi,

I have a sales file with fields for company, month, year, invoice number, total items sold, price and invoice total. The further details of each sale are stored in a separate sales details file.

I want to make a page or layout where I can compare sales in a grid of month and year but so far have been unable to do this.

I was thinking a grid of calculation fields might do this but not sure how to make a field only show the total items sold in Jan 2003 or March 2004 for example. Can I somehow use 'finds' within calculation fields?

Does anyone have any suggestions for how I could do this?

Andy

[Ugo DI LUCA]

Hi,

You can, but you'd need to nest a lot of relationships together, therefore a bunch of fields and relationships necessary.

That's in fact exactly where I'd use repeating fields for a quick scripted reporting, with a loop to switch the Left Key and another to move from reps.

There's another technique shown in the Bowers and Moyer "Advanced FileMaker" book which would use a loop and pipe charatcers to build an Array, if this help.

[Andy_]

Thanks for the advice. Unfortunately I don't have Bowers and Moyer "Advanced FileMaker" book !

I am also unsure about "loop and pipe characters".

Any other help ?

[Ugo DI LUCA]

Well, sales would be captured by a Sum(RelationshipToMonthYear::TotalSales)

where your RelationshipToMonthYear would use

- a compound key of type Year&Month for the right side

- a compound key of type gYear&gMonth for the left side.

This left key will allow you to dynamically trasfer each Count within a loop where either Month and/or Year would increment.

If this is still obscure, let me know.? I don't have time right now, but could draw a little sample if you need so.