Creating a relationship between a table and a connected table

Do you have any unique identifying information in these records that we could use for a mass update/sync?  ie. An automatic connection between the g-drive sync and the original table?

Hmm, I'm not quite sure what you mean. Do you mean records that are shared between the two tables that can be used to link data?

So I'll explain the use case. It's long-winded but thorough :)

I work in a photo studio and we are using a database built in Quickbase to track images through the post-production process. All of the tables used in the database are regular tables, I can't convert them to connected tables (I wish I could, that would make this so much easier). And it would be waaaaaaay too much work to try and rebuild the entire database to accommodate connected tables. It's just not an option.

Each image is represented by its file name, or ShotID. At the end of every day I import an Excel sheet into a Quickbase table called Shots that creates new records for all of the day's new ShotID's.

Every morning I then have upload and download Excel reports that I also have to import into Quickbase. These reports match each ShotID with new rows of data that show things like when a ShotID was sent to the retouchers (To MRC), when it was returned from the retouchers (From MRC), when it was published (To OTMM) etc.

This import of upload/download data is the part I'm trying to automate using a connected table. I import the upload/download data into the connected table and it then needs to see the ShotID's and match them to the ShotID's in the regular table, Shots. This will then update the ShotID rows with the new data from the connected table.

Basically, the connected table is a connected version of the Shots table. So I think, to answer your question, the identifying information is ShotID. I need to create a relationship between the connected table and the Shots table that match ShotID's together. I have tried this, with ShotID being the master record and then telling it which other columns/fields to copy to Shots, but it doesn't work.

I think you are going to want to use some webhooks to make this happen for you.  You will basically tell the webhook, to find the matching ShotID in the static table, and update the fields with the appropriate data.

This will need to run whenever information is added/updated.

You'll want to make sure you account for times when the data is changed manually in the static table, and that the webhook/synced data doesn't override it if not needed.

There might be some other options, but its hard to tell without knowing where all the data is coming from, and how it all gets entered or changed once in the app.

If you get stuck and want some more in-depth guidance, let me know and I can help dive into this more for you.  

Matthew@cirrusops.com

I use "Import into table from clipboard" to paste the data into Quickbase from columns in Excel spreadsheets. I literally just copy and paste.

There are changes I have to make pretty regularly to the static tables. Not just the Shots table, there are a couple of other tables too that pull data from Shots and vice versa. It's only the data that I import directly into Shots though that I want to automate. I don't need a live feed, really all I'm trying to do is be able to import stuff into the static Shots table without having to go in and copy/paste lots of sheets every day. Dropping a single sheet into G-Drive for Quickbase to import automatically is perfect, if I can do that.

I'm interested in the Webhooks solution if that can help avoid synced data overriding modified data. Presumably without Webhooks the synced data would just keep overriding modified data which is definitely not what I want it to do.

Having said all this, I did read somewhere that connected table data can't be modified. Will that still be true if it's synced into a static table? Because I do need to be able to modify data in the Shots table.

When I have time after the weekend I'll read up on how to use Webhooks, unless you think there is an easier to implement solution? Like I said, I'm not a Quickbase noob but I also didn't build the database myself so I'm kind of reverse-engineering a lot of this. For that reason your help is really appreciated :)