7 Replies Latest reply on Mar 6, 2013 7:08 AM by philmodjunk

    Auto-Entry calculations and lookups



      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.

        • 1. Re: Auto-Entry calculations and lookups

               Can you explain why this is necessary?

               Generally speaking, mass updates of primary and secondary match fields are not a good idea and something that is best avoided.

               If you are changing the Vendor, shouldn't this be selected for a new report record?

               Why do you need to include the Vendor ID in the relationship linking the Reports and Data records? (Perhaps you need two separate relationships, one that matches only by Item and one that also matches by vendor id.)

               Please describe your set up for your conditional value list in more detail as it is not clear to me why such a change should impact the function of your conditional value list.

          • 2. Re: Auto-Entry calculations and lookups

                 Sorry, I'm not the best at clearly describing my situations.  To make a long story short I want to be able to select a service from a drop down menu from the vendor that is selected in my Report Maintenance table.  This Item field lives in the maintenance report data table and I'd like the other fields in the maintenance report data table to update themselves via look ups.  Note that I cant leave the relationship as is Item = Item because lots of the parts and services will replicate themselves from one vendor to the other and forcing unqiue values would just make for extremely long and funny looking service names and super long lists.

                 In my head the only way to do that is to have a vendor ID in the maintenance report data table and a relationship to the products table item = itme and vendor id = vendor id.

                 I'm hoping there's a simpler way :)

            • 3. Re: Auto-Entry calculations and lookups

                   From the context of the Maintenance Report Record, Replace Field Contents can update the Vendor ID field for every related record in the Maintenance Report Data table. And Replace Field Contents may be scripted.

                   Test this idea very carefully on copies of your file to make sure that you have it working correctly before putting it into regular use.

              • 4. Re: Auto-Entry calculations and lookups

                     Shoot, I was worried there would be no other way ... just seems a little cumbersome but I suppose its; the only to have the look ups work properly.

                • 5. Re: Auto-Entry calculations and lookups


                       This can be as simple as a vendor ID in a drop down on the report layout. A script trigger on the drop down would then perform a script that does the replace field contents operation.

                  • 6. Re: Auto-Entry calculations and lookups

                         Hello Phil, I thought a little more about you initial questions in regards to the necessity of having all this in place and decided to avoid the triggers this time around and simply add the VENDOR ID to the relationship between the Maintenance Report and the Maintenance Report Data.  Once a Vendor Id is chosen I've added a script step that doesn't let the individual change it.  I will test this out first and see how this works .... if changing the vendor is a regular need then I will change over the replace method.


                         Thank you for your help!

                    • 7. Re: Auto-Entry calculations and lookups

                           That definitely sounds like a better design option.