So Customer and Contacts are the same but the tables are not related? Trying to understand...
Easiest is to perform such a search on a layout based on the contacts table as it holds the date data on which to base your search.
If this search includes criteria to be entered into the invoices layout, then you should add a relationship to the contacts table (either directly to invoices or indirectly via Customer.)
The key question is to figure out what you need to find? Records of Invoices to customers that haven't been contacted? Or records of customers that haven't been contacted in a given amount of time (regardless of invoicing data)?
Thanks so far - I need records of custs that havn't been contacted lately, and some other Invoice data (which led me to script from invoices)
My question I guess is, How can I find all the lately contacted custs? Do I have to use a script in Custs that loops through all records & checks if contacted lately? Can't I do a Last(Contacted) kind of query (borrowing from another language) I Hope I am clear?
Would it be possible that everytime a new entry is made in the contacts table, it automatically triggers a script to update a field value (Customer::LastContactDate) for the customer number receiving a new contact entry?
This is what I did : everytime a new entry is made in the contacts table, it automatically triggers a script to update a field value (Customer::LastContactDate) for the customer number receiving a new contact entry
thanks for the help
Details depend on how you want to define "lately". Would this be the same criteria in every case or something that might vary?
You can find all customers that have not been contacted in the last 5 days or you can set this up so that users can specify 5 days one time, 6 another and so forth. The concept and implementation are the same, but the details can differ a bit here.
Here's a script that will find all customer records not contacted in the last 5 days that have an Invoice amount greater than $500:
You need this set of relationships, in order for this to work:
Customer::CustomerID = Contacts::CustomerID
Customer::CustomerID = Invoices::CustomerID
Go To layout [customer]
enter Find mode  //clear the pause check box
Set field [Invoices::InvoiceTotal ; "< 500"]
Set Field [Contacts::CustomerID ; "*" ]
Set field [Contacts::contactDate ; "≥" & Get ( Currentdate ) - 5 ]
Set Error capture [on]
Note that literal values like 500 and 5 can be replaced by references to global fields if you want to set this up in a way that allows the user to specify these values instead of using exactly the same criteria each time.
I forgot about that method (the easy way) - what I did (see 2 up) works as well - compared that (new) field in the customer table to my requirements, also easy to quickly see last date of contact. First time I used a trigger!