I have created an app for service call dispatch. Tables are linked;
Calls to Product
Calls to Customer
Product to Contract
The Product can have a related Contract record (if sold) that has an end and start date.
When loading a new Call and selecting the involved product I need to pull in the info if this specific product has and active Contract. We would need to check the related record(s) in the Contract table to see if for those that match there is at least one record that has a end date after today.
How to accomplish this?
I assume that this is your table structure - or this is what it should be
1 Product has Many Calls
1 Customer has many calls.
1 Customer has Many Contracts
1 Contract has many Product Contract Assignments
1 Product has many Product Contract Assignments
The Product Contract Assignment table is the join table for the Many to many Relationships between Products and Contracts, as I assume that 1 Contract has Many Products on it, but 1 Product will also be used on many Contracts.
The Product Contract Assignment Table will have a field for [Record ID# of Related Customer] which is pulled down from the Contract.
The Calls form will have a lookup field of the [Related Customer] as I assume that each call will first identify the Customer. The field property for [Related Product Contract Assignment] would be set to be Conditinal wehre the related Customer] equals the [ [Record ID# of Related Customer] on the Products contract record.
On the form property for Calls, you would not use the default record picker for choosing a Project Contract Assignment. instead you would make a report where Contract expiry date is on or after today, and set the form property to use that report, hence only offering up contracts for that customer (done by the conditional drop down of the field property) which have a future expiry date (done by the report).
But based in your structure, you could do a summary of the number of contracts that a product has, on the relationship where 1 product has many contracts. If the summary count is zero, then the Product does not have a Contract. Then you can run he report off the Products Table.
Now a product can get a contact assignment for one year but it could be so that that year after the customer does not buy/renew. The contracts found can be one or more but it needs to be checked that the relative contract is actually active (Contract end date checking).