valid record lookup formula

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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? 

Photo of Marcel

Marcel

  • 0 Points

Posted 4 years ago

  • 0
  • 1
So,

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).
Photo of Marcel

Marcel

  • 0 Points
Thanks for your prompt Answer!

Very good and will run the test. 

Second issue I have is the following; Need to run a report of product coming out of warranty that do not have a contract yet. 

How to run this report from the Product table with right Filter? 

Thanks in advance
That report would be run off the join table - the Product Contracts Table.
Photo of Marcel

Marcel

  • 0 Points
My table structure is slightly different (attached). From my products table reporting I don't have the possibility to use Contract fields to set filters. 
Is your Products Table just a free form entry type field or do you have standard Products, like a Product Master?  Your structure suggests that your Products Table is just free form entry.

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.
Photo of Marcel

Marcel

  • 0 Points
The product is a specific unit with unique serial number and is entered once if not already available. Each product is connected with a customer.

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).
You can do a summary count up the number of active contracts for a product. The filter would be where the expiry date on the contract is on or after today.
Photo of Marcel

Marcel

  • 0 Points
Understood. Many thanks again.