Skip to main content
Question

I have Un-Normalized data coming into a connected table. I need recommendations on how to best handle this data to import it into my App.

  • September 23, 2018
  • 0 replies
  • 48 views
Forum|alt.badge.img+3
The connected table, contains order, shipment, Payment, and Line Item Data, all of which may not be unique. The data would be better stored as the following 4 separate tables:
  • Orders
  • OrderLineItems
  • Shipments
  • Payments
What method would be best to Auto Separate this incoming Data into these 4 tables?  I have a couple of Ideas but I want some Ideas before I build something that ends up being difficult to maintain.

Example: 
  • An Order with 2 line Items would be held in 2 rows, where the Order Info is the Same and only the Item Detail is different, if the item, has not been shipped nor paid for these Col. would be blank or "N/A"
  • As each item is paid or shipped that specific info is added to the existing Row of information
  • If there is a second Payment, that would create a new Row with a duplicate all of the other information 

NOTES:
  1. The Data we receive contains 90 days of changing order history
  2. The Orders change as they are shipped, Paid for and/or Cancelled.
  3. There are no Order Line #'s defined, so these would need to be generated, (I was thinking of a way to count all the order Line Items, that have with same or smaller Record ID, with some filters to select the single order Line Items.)  So the connected table would need to hold all past orders as to not have these # change.
  4. Same Deal for the Payments and Shipments
    This topic has been closed for replies.

    If I had to have this run automatically would suggest contracting with a QSP to write a script.

    But for a semi automatic update, I would set the Sync for new data to come in daily over night.

    I would then have a subscription email come to me each morning (as a reminder) which would be a Summary report of Unique Order#s.  I would use the More button to copy these records to another table and populate a table of orders where the key field was Order#.

    I would then push a formula URL button which would purge out the data in three tables for the lines and the Shipments and the payments, ands then it would run saved table to table imports to re-populate those tables.

    So the daily manual work would be to update the Orders table using that More button and then click one button to do the rest of the work.

    I have long searched for a native way to auto update an Index table with unique values, such as your order# with a single button click, but as far as I know, that requires script or manually copying a summary report. 
    Terms & ConditionsAccessibility statement