adding together the contents of the same field from various records

[sjd22]

Hi. I've just joined this forum, so hello folks.

I have created a FM database on a friend's Mac to record invoices that I receive from one employment agency. I have fields for invoice number, staff name, no. of hours and hourly rate, milage, and the cost which is a calculation field.

 

 

Quite often, I have up to five records with the same invoice number and invoice date, but different work dates, hours worked etc as several days may be on one invoice.

 

I want to create a field that adds the end total cost of each field that shares the same invoice number but am struggling.

 

I would also be interested to know if it is possible to make FM stop asking for a "date paid" if an invoice has not been paid. I have a copy of the database with false names etc which I am using to try various things.

 

 

If anybody has any advice, I would be extremely grateful.

 

 

Thanks

Sarah

Cornwall, England

[kirkrr]

Check out the fm11 FAQ on self-joins. If you create a global field on the parent side of the relationship with an invoice number entered in this field, the relationship will filter the child table to only include the records of that invoice number. Another field in the parent can calculate the total costs from the child records that match.

 

If you want this to be less dynamic and transitory, consider writing a script that does a find and a sum into a new record in another table.