1 Reply Latest reply on Jan 26, 2010 9:41 AM by philmodjunk

    Relationship Question  ::  Do I need a new table?



      Relationship Question  ::  Do I need a new table?


      Hello All, 

      I currently have a relationship structure as follows:

      Products > Line Items > All Invoices > Contact Management.


      The All Invoices layout displays a portal that auto-populates the values from the Line Items table, as well as fields that display client info from the Contact Management table.


      Now, I want to have another layout called 'All PO's' that does essentially the same thing as the All Invoices layout, except reports wholesale values instead of retail ones.  I have copied the layout and renamed it, but am stuck with relationship graph....I'm not sure where/how to make the links.


      Is this enough information for anyone to help? 





      PRO 10 / Mac OS 10 

        • 1. Re: Relationship Question  ::  Do I need a new table?

          Well, you have more than one option here. Where are you storing the whole sale values? Hopefully in a separate field in Products (one field for retail, one for wholesale).


          You could define new tables for POs and PO line items so that your PO line item records look up the wholesale unit price instead of retail--but that really isn't the best approach.


          There are significant advantages to putting all your invoices and POs in one table however, and this can also be done. The advantage here is that it's easier to generate some kinds of reports if your line items are all in one table and your Invoices and PO's are all in another.


          Try this:

          Add a field, Type, to AllInvoices that you use to identify it as either an invoice or a PO.

          Define a matching field in LineItems so Line Item records can also be labeled as "invoice" or "Purchase Order".

          Update your relationship between All Invoices and Line Items to include this new pair of fields:


          AllInvoices::InvoiceID = LineItems::InvoiceID AND

          AlllInvoices::Type = LineItems::Type


          Add a Wholesale Price field to LineItems and set it to look up the whole sale price instead of the retail from Products.

          Modify your calculations in Line Items to use LineItems::Type to determine whether to use the retail or whole sale price to compute the item's cost.


          Now one layout can be used for both Invoices and PO's. You just add AllInvoices::Type to your layout and format it with radio buttons or a pop-up menu so you can select the type when you first create the invoice.