3 Replies Latest reply on Jan 13, 2012 11:08 PM by DiegoCadogan

    accessing fields of a related record in a calculation

    DDab

      Title

      accessing fields of a related record in a calculation

      Post

      Hi,

      How can I access fields of a related record inside a  calculation dialog box?

      Example:

      Table A and B are related. I have a field in table A whose type is of Calculation. Inside that calculation, I want to use a field from Table B. How can I achieve this?

      Thanks

      D

        • 1. Re: accessing fields of a related record in a calculation
          philmodjunk

          Select Table B from the drop down. Then double click the field when it appears in the list below. You can also just type in the field name provided you include the correct table occurrence name with the field.

          Field1 + Table B::Field2

          (A table occurrence name is the name of a box in Manage | Database | Relationships. By selecting or entering the table occurence name, you identify the relationship that controls which record in the related table will supply the value you need in the calculation.)

          • 2. Re: accessing fields of a related record in a calculation
            LaRetta_1

            "Inside that calculation, I want to use a field from Table B"

            It can be dangerous using Table A and Table B instead of indicating real table names because 'A & B' does not provide a clear context in how the tables are related. If you say Invoices and LineItems then we understand the perspective.  If you say Customers and Addresses, we understand as well.  

            If, from Table A, you refer to a field in Table B then you will always only get the first related Table B record (according to the sort at the relationship level as defined in the relational graph).   So if the relationship from Table A to Table B is 1:n (one-to-many) or n:n (many-to-many) then you might not get the results you want by simply referencing a field in Table B. 

            You could use GetNthRecord() to specifiy a certain related record (can be a bit slow depending upon how it is used) or you can sort the relationship by the field you want so that the first related record is the first or, you can use a non-equijoin to 'filter' the relationship, i.e. <, > etc.  You can also use aggregate functions such as Min(), Max() or use repeating functions such as Last().

            So if you could explain what "Inside that calculation, I want to use a field from Table B" means (explain the context and value that you expect to retrieve) it would help us help you. Laughing

            • 3. Re: accessing fields of a related record in a calculation
              DiegoCadogan

              I'm trying to do something similar. I have my Address Book and Service Library as related fields in my Quote Library, and would like have the contact info auto-populate from my Address Book and certain details from the Service Library into the Quote Library, but need the form to look nice. So what I'm trying to figure out is how I can do this:

              1) Have 2 forms inside my Quote library. The first (called "Related") for related fields and the second (called "Quote") will the actual form I will print for my clients.

              2) The second form ("Quote") will be auto-populated with with the contact and service info from the related fields in the first form ("Related").

              Here are more details. Both the Address Book and Service Library are made of several field types; more related fields, Simple List, and Texts.

              How can I make this work?

              Thank you!!