3 Replies Latest reply on Jul 21, 2012 10:21 PM by philmodjunk

    Getting additional data from related table?



      Getting additional data from related table?


      Back again with yet another simple question with an obvious answer that everyone but me knows.


      I have an Invoices table layout with a portal that displays related records from the Invoice Details table. This portal contains a popup populated with data from a related Rates table. Selecting a category from this popup fills in the category field in the related Invoice Details record. All well and good. 

      But the Rates table also contains other data that I want to have automatically added to the Invoice Details record, so that selecting a category automatically fills in the Rate and Unit associated with that category.

      I tried using the following script (attached to the Category popup) which, aside from the last line, doesn't work. Perhaps someone can tell me why and what I need to do to fix it?

      Set Field [Invoice Details::Rate; Invoice Details::Rate = Rates::Rate]

      Set Field [Invoice Details::Unit; Invoice Details::Unit = Rates::Unit]

      Go to Field [Invoice Details::Count]


      Thanks! (no, this is not the last line of the script!)



        • 1. Re: Getting additional data from related table?

          The script steps should have been written as:

          Set Field [Invoice Details::Rate; Rates::Rate]
          Set Field [Invoice Details::Unit; Rates::Unit]

          But you don't need a script at all.

          Find Invoice Details::Rate in Manage | Database | Fields.

          Doubleclick it to open Field Options

          Click the auto-enter tab

          Select the Looked up value options and specify that the value be copied from Rates::Rate.

          Do the same for the Unit field

          • 2. Re: Getting additional data from related table?

            Tried the auto-enter trick, but it didn't work. Perhaps because the Invoice Details record was already created at a point before selecting a Category (which would establish a related record)?  

            The script using Set Field worked somewhat...it did manage to copy both Rate and Unit, but only from the first record in the related Rates table, regardless of the choice made in the Category popup. That also seems to indicate that a related record has not be established. Now I'm wondering if the relationships are set up incorrectly. 

            • 3. Re: Getting additional data from related table?

              There's no relationship that matches by category. If category determines the rate, you need this relationship:

              InvoiceDetails::Category = Rates::Category

              But I suspect that it is the ClientID AND the Category that determines the Rate. That will be a bit of a problem as there is no field in InvoiceDetails that identifies the client.

              Try this (May not work for looked up value, but should with scripts):

              Define cClientID in InvoiceDetails as: Invoices::Client

              Then define the relationship as:

              InvoiceDetails::cClient = Rates::Client AND
              InvoiceDetails::Category = Rates::Category

              Try the looked up value option first and if it doesn't work, then use the scripted method.

              To keep your other relationships unchanged, click Rates to select it then click the duplicate button (two green plus signs) to make a new occurrence of Rates and then you can use Rates in the above relationship and then also refer to this new occurrence with any script or looked up value setting.