1 Reply Latest reply on Jan 28, 2010 2:19 PM by philmodjunk

    Importing into multiple tables

    Volos

      Title

      Importing into multiple tables

      Post

      Hi everyone, I've looked for a solution to this and can't find one, so forgive me if an awnser already exists.  I'm using 10.0V3 on an XP machine @ work.  I am trying to import a good deal of order information so that multiple lines (rows in Excel) from an csv or xls can come into one record.

      The main fields I am dealing with are [there are others but they can piggy back these]:

      Creation Date; PR ID; PO #; Supplier Name; Quantity; Unit Price; Extended Price; SKU/Catalog #; Amount/UOM & UOM; Product Description

       

      I tried working from the purchase order templete to make have the relavent fields I need.  My problem is I want there to be one record per PO # with all of the line items of that PO.  I can import each line item as a seperate record correctly, but I'd like them to be in a table on one record with common outer fields.

       

      Here's how the data is set up:

      In one row there are the above fields (+ 14) with the following real world relationship:

      For any date there can be any multiple of (sequential) PR ID's.  For each PR ID there can be multiple PO #'s (one per supplier).  For each PO there can be multilple line items (highest # is 114). 

      There is only one line item per row, however.  The common fields of

      Creation Date; PR ID; PO #; Supplier Name

      Are repeated in each line for each item from the PO.  The remaining fields all vary based on the line item.  

       

      Is there a way to import into both the Record Detail, and Record List tables at the same time (or in matching sequance), breaking up the records by PO?

       

      I hope this question makes since,

      Volos

        • 1. Re: Importing into multiple tables
          philmodjunk
            

          I'd import the data twice: Once into an invoice or PO table and once into a related line items table. You'll need to set up one method or another to prevent duplicate PO entries. One simple method is to set a unique value--validate always validation rule on your PO# field in your PO table. Then subsequent lines of your data with the same PO# should be automatically filtered out during import. Then I'd import the data for the individual line items into a line items table.

           

          To see all the line items on for your PO on the PO layout, add a portal to the line items. This will give you a much better database structure to work with.