Calculating Year to date purchases

[Colby0]

I need help to calculate a year to date purchse field any help would be greatly appreciated

Cole

[BobWeaver]

Can you give a bit more information about how your file is set up?

[Colby0]

I have a invoice system where customers are in one database and the invoice info is in another and I would like to be able to look at a customers name and have it calculate the year to date purchases from the invoice database. Thanks so much

[BobWeaver]

You will have these fields in the invoice table:

InvoiceDate - Date of invoice

InvoiceAmount - Amount of purchase

CustomerID - unique ID number of customer

You will also have CustomerID in the customers table.

In your customer table you will need to create an unstored calculation field YearStart with this formula:

Date(1;1;Year(Get(CurrentDate)))

Next, make a relationship named YTD from the customer table to the invoices table with the following match fields

Customers::YearStart <= Invoices::InvoiceDate

AND

Customers::CustomerID = Invoices::CustomerID

Then make a new calculated field in Customers, YTDTotal with the formula:

Sum(YTD::InvoiceAmount)

This should give you your year to date totals by customer