Auto-Entry calculations and lookups
I have currently have four tables, Vendors, Maintenance Reports, Maintenance Report Data and Products.
From the maintenance Report standpoint, I have a relationship with the Vendor table where I can pull up each vendor select one and a trigger replaces the current VENDOR ID with which ever new one I've selected.
I also have a relationship between the maintenance report data and the maintenance report tables, this obviously houses all the individual entries for one Maintenance Report. All the various types of products are stored in the product table and the maintenance report data calls upon the products via lookups to get information like prices, taxes etc.
Given that we deal with several different vendors offering several of the same types of products and services I figured it would be beneficial to create a value list that only displays the products and services offered by one particular vendor. That is easy to setup. Currently I have:
Maintenance Reports Data::Item = Products_Maintenance Reports Data::Item
and I would like to add:
Maintenance Reports Data::VENDOR ID MATCH FIELD = Products_Maintenance Report Data::VENDOR ID MATCH FIELD
No problem and the lookups would work perfectly fine with this relationship because if I have 8 different Items called "Oil Change" it would only see the one with the same vendor ID.
My trouble is in the Maintenance Report Data::VENDOR ID MATCH FIELD, is there an easy way to have this field update itself when a maintenance reports vendor changes from one to another with auto-entry functions? All my tests are not working out and the only solution I can currently think of are more elaborate script triggers that search out every matching maintenance report data record and loop replace the VENDOR ID every time a new one gets selected.