7 Replies Latest reply on Jan 7, 2012 11:03 AM by KylePutzier

    One to many but portal to show only unique

    KylePutzier

      I have a Sales Order layout that can have many line items related to it. I also have an Invoice layout that can also have many line items related to it. The Invoice line items may or may not all be related to the same Sales Order.

       

      On the Sales Order window, I would like to have a portal show all invoices that are associated with it.

       

      In the Invoice table. I have used the List() function to gather all of the Sales Order numbers from the Invoice line items table. Normally I could use a text field of values for the right side of a relationship, except I cannot index the List() calculation field. If I change it to a Calculated Value text field, how can I get it to update (recalculate) automacally when an Invoice item is added?

       

      Kyle

        • 1. Re: One to many but portal to show only unique
          comment

          KylePutzier wrote:

           

          On the Sales Order window, I would like to have a portal show all invoices that are associated with it.

           

          What's stopping you from doing exactly that?

          • 2. Re: One to many but portal to show only unique
            KylePutzier

            If I create a relationship from Sales orders to Invoice Line Items, I will have a portal row for each Invoive line items that the sales order relates to. These invoice line items may or may not be on the same invoice. I only want to the portal to show the unique invoices that are associated with the sales order.

             

            e.g.

             

            Lets say there are 2 sales orders. Sales order #1 has 3 line items on it. Sales order #2 has 4 line items on it. There is a an Invoice that has all 3 items from sales order #1 and all 4 items from sales order #2. In my scenerio, I would like each sales order to show that Invoice in a single portal row.

             

            Kyle

            • 3. Re: One to many but portal to show only unique
              comment

              If I understand correctly, your relationships are:

               

              Invoices -< LineItems >- SalesOrders

               

              i.e. each LineItem is (or can be) linked to one Invoice and one SalesOrder. If so, all you need to do is place a portal to Invoices on a layot of SalesOrders.

              • 4. Re: One to many but portal to show only unique
                KylePutzier

                Thank you. That's the answer.

                I wanted a portal to show Invoices, so my current Sales Order relationships only went to the Invoice as a whole and my brain got stuck right there.

                 

                SalesOrders  >  Invoices    (wrong)

                 

                Going through the InvoiceItems was the answer.

                 

                SalesOrders  >  InvoiceItems  >  Invoices       (correct)

                 

                Kyle

                • 5. Re: One to many but portal to show only unique
                  comment

                  Actually, you could link to Invoices directly from SalesOrders - but the unstored calculation must be on the SalesOrders side, in the form of =

                   

                  List ( LineItems::InvoiceID )
                  
                  • 6. Re: One to many but portal to show only unique

                    My sense is that your second method would be more efficient but it is mere speculation.

                     

                    I believe that, through the primary relationship (displaying a portal from Invoices through LineItems), all related LineItems records (and all its fields) must be loaded and then all of the Invoice records (and all its fields) for those LineItems.

                     

                    I believe that utilizing a secondary Invoices table occurrence attached to an unstored calculation in SalesOrders would not evaluate unless needed for the parent record being viewed and List() does not require all fields for all LineItems be downloaded first (it just gathers the IDs) and only those related Invoices would load and display in the portal. 

                     

                    This is prime example where understanding how the records and relationships are evaluated is important, particularly when served.  Perspective to help Kyle decide which to use would be great.

                    • 7. Re: One to many but portal to show only unique
                      KylePutzier

                      There will never be that many Invoices tied to any one Sales Order so any efficiencies gained by any particular method would be insignificant. Also, viewing Invoices from a Sales Order is not a primary feature of the program. It may seldom be used. I have similar features elsewhere in the program, so to keep continuity, I will have it here also.