5 Replies Latest reply on Mar 8, 2014 6:44 AM by willrollo

    Refer to other records in report

      Title

      Refer to other records in report

      Post

           I have the following relationship - Customers---->Invoice details. 

           I want to be able to print a report that will list the Invoice details ::ID number and data from the field invoice details::reference, where are more than one invoice details record for that customer..

           For example, 

           "Thank you for your orders Invoice details::ID (Invoice details::reference) and Invoice details::ID (Invoice details::reference"

           I am sure there is a simple solution but I cant think what it is!

           Thank you

        • 1. Re: Refer to other records in report
          philmodjunk

               I don't understand your description here. Your example would seem to show data from a single related record, yet you want to list data from multiple related records. How does this example work for multiple related records? Do you want to list multiple items within a single copy of this text or will you list the same text over and over again, but each time with a different invoice? Or did you have something else in mind?

          • 2. Re: Refer to other records in report

                 Hi Phil - What I want, is to be able to print a letter from the Customers table based layout. I do want to list multiple items within a single copy of this text, as I want to be able to refer the relevant orders that link to the particular customer. Does this make sense? 

                  

                 Eg. Customer 1 has had three orders (related invoices details records) created. So I want to print a letter from the customers table, that will mention the IDs and references of these three orders, as part of the text.

                 Customer 2 has one order, so the same again, but addressed to customer 2 and the one related order mentioned in the text..

                 I know what I want to do, but although it seems simple to me, describing it in detail here is a little more tricky!

            • 3. Re: Refer to other records in report
              philmodjunk

                   OK, but how will that look in this text? Like this?

                   "Thank you for your orders Invoice details::ID (Invoice details::reference), Invoice details::ID (Invoice details::reference), Invoice details::ID,  (Invoice details::reference), and Invoice details::ID (Invoice details::reference) ."

                   IF so, you'd need to define cDetailsReference in Invoice Details as:

                   ID & "(" & Reference & ")"

                   Then define in customer:
                   Let ( [ L = List ( InvoiceDetails::cDetailsReference ) ;
                              Vc = ValueCount ( L )
                             ];
                              IF ( Vc > 1 ; Substitute ( LeftValues ( L ; Vc - 1 ) ; ¶ ; ", " ) & "and " & GetValue ( L ; Vc ) ; L )
                          ) // let

              • 4. Re: Refer to other records in report

                     Crumbs that is complicated! But yes, that is the kind of thing I am after... 

                     will cDetailsReference be a calc text field? 

                     The Let script that you have written- do I make a calc field in Customers for this? 

                      

                     Thank you

                • 5. Re: Refer to other records in report

                       Got it working! Thank you so much - I would never have been able to work that out on my own