Forum Discussion

RicardoSendra__'s avatar
RicardoSendra__
Qrew Trainee
7 years ago

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.

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
Data import and export
Quickbase Sync
Relationships
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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. 
    • RicardoSendra__'s avatar
      RicardoSendra__
      Qrew Trainee
      7 years ago
      Could you give me a bit more detail on the Semi-automatic process....  I did not quite follow you through all of the steps.

      This is what I understood:
      1. Get Data  in Connected Table
      2. Create a Summary Report of the Unique Order ID's ( w/ Email Subs...)
      3. Then copy the List of Order #'s to the Final Order Table(Key: Order_UID ).  Then an Automation will grab the new Order ID's and populate Order Data.
      4. I did not follow you here ???  "... 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."
      Thanks!
       
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      There is no Automation here.

      I'm suggesting that you copy across the the unique orders #s as you suggested, but then there would need to be quite a long formula URL to do all those other steps using the APIs.  It would be a single click that would do about 7 steps.

      If you want help in building that URL button feel free to contact me off line via the gmail emails address on my website  QuickBaseCoach.com