3 Replies Latest reply on Sep 28, 2015 11:40 PM by serval

    How to address a desired record in a different table?


      I have two tables with their own layouts: Invoice (for billing), and Locations (a list of locations where work is performed).


      Normally, I click a Location button on the Invoice layout, and a script takes me to the Locations layout, where I pick the location. I click on that Location’s This_One button, and the name, address, etc. of that Location is copied into the script, the layout switches back to the Invoice, and the copied location data entered into the Invoice layout.


      That all is working fine.


      Now I want to mark one of the locations as the Default Location so that when I create a New Invoice, the default location is automatically entered.


      I have the marking of the Location working:

      I have Auto-Entered Serial Numbers as a key field for Location.

      A checkbox on the Location layout indicates which Location is to be the default, and the serial number of the checked Location is stored in a Global variable, DefaultLocation.


      My final problem, where I am stuck, is how does the New Invoice script operate on the correct Location record? I have the Serial Number of the Location record I want in that DefaultLocation Global variable, but how does the New Invoice use that to target the correct Location record? Everything I have tried so far gets data from the wrong Location record.

        • 1. Re: How to address a desired record in a different table?

          Your Invoice table should have a foreign locationID that you use to create a relationship between Invoice and Location:


          one Location --< many Invoices

          Location::id = Invoice::id_location


          then reference a specific location in a Invoice by setting the foreign key it to the value of the primary key of the desired location record.


          Basically, all you need to do is check in your New Invoice script if $$defaultLocationID is set; if so, set the foreign key field to that locationID (or maybe use a dialog to confirm) and have the relevant fields (address) fill in themselves by using an auto-enter calculation that pulls in the data via the relationship.*


          Decide on what to do should you find yourself without a default location.


          *Note that you should only copy data that are bound to change over time; since a location is defined by its address, this is not going to change, so you store it with the Location and reference it in Invoice; that's what a relational database does.


          On the other hand, a client company has an address, but that may change; that where it makes sense to store the company's address at the time of the invoicing with the invoice (same as with VAT rate, product prices, discount rates …)

          • 2. Re: How to address a desired record in a different table?

            Errolst said .. Decide on what to do should you find yourself without a default location.


            i would add "Decide on what to do should you find yourself with more than one default location".

            • 3. Re: How to address a desired record in a different table?

              Thanks for the advice. I’m going to have to read those chapters in my Missing Manuals book to understand how to do that. I thought I was doing something like that, as I had used Serial Numbers to generate a key field in the Locations table and made the key of the default location record available to my New Invoice script. My problem was that I couldn’t figure out how access a Location record from knowing its key.


              Until I read the manual and get a better understanding of how relational databases work, I have implemented a more primitive method: The Set Default Location script copies the fields I need into a set of  Global fields. Then my New Invoice script can copy the data from those Global fields and put it into the new invoice.


              Probably once I understand how to do what you have suggested, I will also see that there is a more elegant way to do the non-default address entry than the method I described in my original question.


              As for what to do if there is no default location or more than one, if there is no default location, then the New Invoice script doesn’t enter any location info in the invoice, and I use the method I described in my original question to put in the location (as well as the option to just type the location into the invoice). There won’t be more than one default because the Set Default Location script turns off the previous default indicator when a new one is set.