Creating a relationship between a table and a connected table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I'm using a database built (not by me) using regular tables only. I manually paste in data from Excel sheets every day and I'd like to try the sync function instead.

I've set up the connected table and have got it syncing data from G-Drive but the problem I'm having now is in trying to get this data from the connected table into one of the regular tables (called Shots). The fields in the connected table mirror the ones in Shots, I just need them to effectively copy over into the Shots table.

I've tried creating a relationship between the two tables, as suggested in the sync FAQ, but it's not working. I'm probably not doing it right but I can't find any good documentation on how to do it. I'm very new to Quickbase and didn't build the app I'm using myself so this is turning out to be harder than I'd thought. Any help is much appreciated, thanks.
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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?
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
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.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
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 :)
(Edited)