How to link / reference data without a key??

[davehocking]

Hi all, I'm trying to solve a problem and I don't know the best way of going about this, I hope someone might be able to set me on the right track here.

We have a table of contacts and a table of invites, we can track who we have invited to what events (see below) This is working fine, however... I have a 3rd party source of data in Google Sheets that I want to cross reference against my list of invites, so we can automate the reporting of the real status.

What I can't figure out is how to process the data after the registrations report is pulled (daily). I don't want to have to go into each record and get it to look-up and store any changes one-by-one, there will be hundreds of invites/registrations to process. 

Is this something I need to do in a bot?

I'm struggling to see past the obvious "there's no reference linking these tables" issue, any ideas would be greatly appreciated!

[Marc_Dillon · Accepted solution]

I'm not really sure what your question is here, what are you trying to do? I see there is the email address in both table, you can associate them with that.

[Marc_Dillon · Accepted solution]

I'm not really sure what your question is here, what are you trying to do? I see there is the email address in both table, you can associate them with that.

[davehocking]

Sorry I wasn't clear Marc, I think I was just having a "brain fart" - I just used Select() to look up the matching rows using the email and event name to get the registration status. I got myself down a rabbit-hole by thinking I needed to do something more complex.

[AleksiAlkio · Accepted solution]

The best way is to use virtual column for this purpose. It will update the data every time when the app is synced.

[davehocking]

Thanks, that's exactly what I needed! - the downside being the impact on sync performance, but I've got a separate project I need to look at to improve things there.