2 Replies Latest reply on Oct 9, 2012 2:58 AM by mikethecreative

    Line Items in Portal



      Line Items in Portal


           My file contains an Invoice, Line Items, and Products table. When a new Invoice is created, a script automatically creates 30 Line Items on the Invoice - each one corresponding to the 30 Products we sell.

           This is what I would like to happen: when a new Invoice is created, all Products are automatically shown on the Invoice. When I enter in the quantity of Products purchased, then a Line Item for that Product is created. Right now, every Invoice automatically has 30 Line Items created, whether or not a customer buys only one or multiple products.

           How can I make this happen?


           * My Line Items are shown on my Invoice via a Portal

           * Each Line Item has a SKU number associated with it that links it to the Product

        • 1. Re: Line Items in Portal

               If you are going to list your products in the line items portal, then you are going to need 30 line items records. But you can get what you want by using a portal to a different table that then links to the line items portal so that when you enter a quantity in the quantity field, a related record in line items is created for you.

               Your basic relationships to start:


               Invoices::__pkInvoice = LineItems::_fkInvoiceID
               Products::SKU = LineItems::SKU

               What we'll do is add some new table occurrences of Products and LineItems with new relationships:

               Invoices-----<AllProducts-------ProductLineItems    (---< means one to many, ----- means one to one.)

               Invoices::anyField X AllProducts::Anyfield (use any field on each side of the relationship)
               AllProducts::SKU = ProductLineItems::SKU AND
               AllProducts::gInvoiceID = ProductLineItems::_fkInvoiceID

               Enable "allow creation of records via this relationship" for ProductLineItems.

               ProductLineItems is a new occurrence of LineItems. AllProducts is a new occurrence of Products. gInvoiceID should be defined in the Products table and have global storage specified.

               On your Invoices layout, replace the portal to LineItems with the portal to AllProducts. Add the Quantity field from ProductLineItems to your AllProducts portal. Add any Product fields such as price and description from AllProducts to your portal row.

               One final detail:

               Write this script:

               Set Field [Products::gInvoiceID ; Invoices::__pkInvoiceID]
               Commit Records

               In Layout Setup, use the OnRecordLoad Script Trigger to perform this script.

          • 2. Re: Line Items in Portal


                 Thank you so much for your thorough answer. It worked!