How can I connect a set of mature and existing tables to their respective csv files for daily update?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Everything I have read says that in order to have this process, these type of tables must be created from scratch. With a set of mature tables, recreating them from scratch is onerous with not only the amount of existing data but the many relationships between the tables.
Photo of Mark

Mark

  • 0 Points

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
One why that i know of for a workaround for this, without destroying existing application structure, data, and general function is to create "Parent CSV Import" tables.

For example:
You have an existing table "Jobs" that has a ton of information, fields and relationships.

Create a new table "Job CSV sync" and make this the parent to "Jobs".

Now that each "Import" can have a "job" you can now pass all the individual fields/columns via lookup fields to the original "jobs" table.

(this is where things will be come pretty labor intense).

You will need to not only bring the lookup fields, but make a new formula field for each one of those.

For example:
If you have a native field called [Job Start Date] then you bring a lookup field for the same date, now called [Import - Job Start Date].  You will need to make a formula field that will evaluate the fields and display whichever is the correct. 
(Best practice here is to display the native Jobs field if it is filled in, otherwise use the import value).

the formula would look something like:

If(IsNull([Job Start Date]), [Import - Job Start Date], [Job Start Date])

Keep in mind the csv import will only bring the date fields as text, so you will need to convert those to Dates in your formula.
(Adjusted for Date)
If(IsNull([Job Start Date]), ToDate([Import - Job Start Date]), [Job Start Date])

You would then need to switch out all your reports to use the new formula field.  Not the native one.

Yes, not a very fun option.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
If you have the time, or want to outsource this to make it cleaner.

I'd recommend exporting the app data and staring with a clean app from the start.  Either way, its a messy situation to automate an existing app with this much structure