Continuously updated data that needs to be linked and transformed on repeat

Forum|Forum|1 year ago
July 17, 2024
4 replies
63 views

+5

cgreaves
Inspiring

I think this question stems from not understanding the core logic behind Airtable. Hopefully this will act as a piece of a puzzle.

Background: I have data being automatically sent as a CSV once per hour. Most of the records will not change often, so I plan to merge the new CSV with the previous one, changing, adding, and removing records as necessary.

This isn't set up yet - I'm first testing the second half, where I process, display, and use this data after it's merged. Actions I need to take range from concatenating two fields to linking to other existing tables to updating junction tables.

I'm stuck with how to start. In Excel, I would set a CSV "landing spot" as a source, so that every new CSV just rewrites the entire source. Then that source would be transformed in Power Query and spit out as a new table. My instinct here was the same - creating a "landing" table, essentially copy and paste that table to a new one where the actions would take place, and use that table for the interfaces. But that doesn't seem possible. And performing actions directly in the table to be overwritten should mean those formulas, etc. get overwritten every time too, right? How does Airtable *want* me to handle this?

+20

Sistema_Aotearo
Inspiring
Forum|Forum|1 year ago
July 18, 2024

Hiya. I think this all depends on what type of data you're storing. Does the data from Airtable need to be exported regularly? What other variables outside of Airtable are forcing a regular mass import? Would an automation that updates a single record be useful?

Justin NgProgramme Coordinator at Sistema Aotearoahttps://www.sistemaaotearoa.org.nz/

Like

C

+5

cgreaves
Author
Inspiring
Forum|Forum|1 year ago
July 18, 2024

Thanks for jumping in! There's a lot going on in the data. I've mapped out an ERD, and it's 15ish tables and dozens of connections. It was my first time, so I hope I didn't overcomplicate it.

I don't want to waste anyone's time, so I'll answer your questions as succinctly as possible. (Just finished. Mission failed.) Below, I'll go into fuller detail to paint a picture.

What type of data: Not sure if this is what you mean, but almost exclusively text. There will be some blank PDF forms and such, but those will be mostly static. If you meant more what is the data supposed to do, see below for details.

Will the data be exported: Airtable will function as a front-end of sorts for another platform, so tables will rarely if ever be exported. I will try to have an "Updates" interface that will let team members know what they need to update on the other platform, sort of our database. (No API or any way to automate).

Why is the data being imported frequently: We're forced for now) to use one platform as a database for PII, and Airtable for workflows, dashboards, and Airtable forms. All changes to the database must be manual, but exporting those changes is semi-automated. To keep Airtable useful throughout the day, frequent updates seem appropriate.

Automations of single records: I have seen that a record changing can trigger an automation, which I assume could also apply to this merging scenario - as in, it doesn't just apply to manual updates from users. I could also trigger it based on the merge happening or something. I think I tenuously grasp the idea that automations live in a separate space from tables, so it could apply over and over to the CSV landing table as it updates. I'm less sure if this would work with formula fields, for example. I guess it all seemed kind of against my intuition about how it should work, and Airtable seems powerful enough that I could get lost for weeks trying to force my solution instead of thinking of it in the "Airtable Way".

Details: we're managing insurance enrollment and credentialing for healthcare providers. The data being loaded in on a schedule is each of those providers' statuses with each of the insurances they're seeking to be enrolled with as well as dates. There's a provider table, an org table, a location table (some orgs have multiple locations, and some providers work at 3-4 locations), and an insurance company table. We're tracking each insurance company's requirements, such as licensure. One goal is to match that against the documents each provider has (a junction table between providers and a doc table). Each org also has specific requirements, so we're trying to link everything so when a team member goes to complete a certain task on behalf of Dr. X, a workflow appears with checklist items tailored to what Dr. X already has on file, and what that insurance or org requires. That's why there's so much linking and processing after the CSV hits the base.

My goal is to capture any manual changes made in Airtable and present a list of changes to be made in the "database", the source of the updating data. (It's a software platform that proved too limited and slow to make improvements for frontend use, but it's safer to leave PII there rather than in Airtable... Not ideal, I know. For now, I have to keep that platform as our source of truth)

When statuses or dates or the provider documents we upload change there, I do want that quickly reflected in Airtable. We're setting up an SFTP, which can be updated up to hourly. Either hourly or daily updates would be okay, but I figured it probably wasn't any harder to do it more frequently. If it adds too much cost or it takes several minutes to process in Airtable, I'd switch to daily.