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?
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.
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.
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.
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)
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 )
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.
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.