Which means that you want to see if there are any records where the invoice date is less than or equal to Get ( CurrentDate ) - 14
You can set up an unstored calculation field set to return date with that calculation and use it as an additional match field in a relationship between Customers and a new occurrence of invoiceDetails.
And that Match field would use < instead of = in the relationship.
I shall try it out and let you know if I have any issues...
Just to confirm, I need to create this new field in the customers record and the invoice details record?
Trying to get my head around how it works.. could you briefly explain if you have a moment?
Just the customer's record and I am not sure If I have specified exactly what you need. I might have guessed wrong so check things. This calculation field would match to the date field that you already have in InvoiceDetails.
Will that mean that this new field will be conintually overwritten, or is this why there is a table occurance of invoice details\?
It's an unstored calculation. As the date changes, it will update accordingly.
A little stuck..
I have created a new T.O. Called customers_INVOICEDETAILS_quote.
I have created a new relationship between the two, linked by the ContactPK =invoice details fk.
In this same relationship box, i have added a < match field between the new customers:quotedate field and the invoicedetails:date field.
There is how an eggtimer on its side type icon on the diagram.
The calculated field, customers::quotedate has the properties: Get(CurrentDate)- Customers_INVOICEDETAILS_quote::Quote Date ≤ 14
from context of customers...
I know the above is wrong as it doesnt work- pretty sure it is the calculated field part..but that is the extent of my attempt to remedy!
You can't use a reference to the related table in the calculation for a match field to the same related table.
Get ( CurrentDate ) - 14
For that unstored calulation field.
Thank you. I have altered the calc field (set as date) to Get ( CurrentDate ) - 14.
Now, when I create a new invoice in invoicedetails, every customer in customers table contains today's date less 14 days in the field
Is this the expected result? What I want to see is that if any related Invoicedetails::quote date records are less then 14 days from current date (i.e. 14 days or more recent) then the print script I want to use will come up with an error message...
So maybe a dont need a calculation of Get ( CurrentDate ) - 14, as that can be in my script to test how old/new the most recent invoice is????
That is the expected result. You will recall that I said that I was guessing as to what you needed and that you should check the results.
So with Today's date of March 14, you'd want all records from March 1st to Today, correct?
Assuming that you have no records with a date in the future, you need all records where the date on the invoice is greater than or equal to the date of 14 days earlier than today.
Customers::QuoteDate < InvoiceDetails::Date
Sorry for late response.
So with Today's date of March 14, you'd want all records from March 1st to Today
Yes - If there are any invoice records related to the customer record I am viewing, then I would like a positive result in the new customers record (customers::quotedate) so that I can present a dialogue to the user warning him that the invoice he is about to chase is less than 14 days old.
At the moment, every customer record has the 4/03/2014 in the new customer field. I would have thought that the field will only have data in if this new field is less than or equal to the related invoice details record, minus the 14 days we entered as part of the calculation...
Have I made a mistake somewhere or has there been a miscommunication on my part do you think? Both equally likely!
This is only a match field for a special purpose use. It should have the same date in every record as it is designed to calculate a date from the current date as set by your computer's system clock. To see the data you are looking for use the relationship that includes this field as a match field to check the data from the related table occurrence.
I read your post again on table occurrences to refresh my memory...All working now - thank you