Date range and total

[Peter Landon]

Hi, I'm a complete newbie so please forgive me if there's an obvious answer to this. I've tried and failed to find an answer so far. I have a table containing seven years invoice totals with their dates and customer IDs. I would like to clear that table down so that it only has the last two years data by making up a new table linked by the customer ID and with fields for the total sales for each old financial year. (Those run from 01/10/200* - 30/09/200* and they're in UK format). Can anybody suggest a calculation which will produce a total for each year and therefore populate the fields in the new table?

[Ocean West]

you wish to have a record for each customer by year? You could export this data sub summarized resulting with one record for each customer by year. Which you could then reimport back into your new table.

Since this is a "typical" one time task just export and import. No need to make a complex calculation or script.

[Peter Landon]

Thanks for the quick response. Do you mean export to Excel? Or is there an FM procedure I've missed so far?

[Fenton]

Export as FileMaker would be my preference. The procedure is:

1. Sort by Customer_ID

2. Export. In the top right box, there will be an option to [x] Group by Customer_ID

Customer_ID is the "break" field, to group the summary results.

When you put the Amount_SUM in the export order, a new "field" appears, "Amount_SUM by Customer_ID". FileMaker assumes you want this, and you do.

Export that instead of the Amount_SUM (which is going to be ALL the amounts), and the Customer_ID and the date field (it's going to be 1st date for the Customer_ID).

Create an Sales_archive table. In the Sales table, Find for the dates exported. In latest versions of FileMaker you can just Find for ≤2007 (easy as pie -)

Switch to the Sales_archive table (same window). Import.

Switch back to the Sales table. Delete Found Records.

Now Import the file you exported, of the summarized totals. Yes, they will go at the end of the records in creation order, but that doesn't really matter; you can sort your portal(s) so that they are in date-ascending order.

If you want to see the old records, in "yearly" portals, you can create a calculation field in the Sales_archive table,

_cYear = Year (date field)

In the viewing table (parent of portal), create a calculation field, 2005 (just the number). Another calculation field, 2006. These can have their Options, Indexing unchecked [ ], but it doesn't much matter.

Create relationships from each of these fields to your Sales_archive _cYear field. Something like that :-]

[Peter Landon]

Many thanks. That sounds good.