Trying to auto-link records between tables

1

I have an external SQLite data source with two tables: Contacts and Donations. The Donations table has a DonorID field for each donor. I want to have a corresponding (unique) DonorID field on the Contacts table. Naturally, I want to link the donations to the donors (ie. contacts). Now, I can make a “Has Many” Link field on the Contacts table, no problem, but then I have to manually link every donation to the correct contact. As I understand it there is no way to do this automatically, which seems like a major shortcoming of NocoDB, but anyway.

I thought I came up with a clever workaround by making a trigger on the Donations table (outside of NocoDB), so that it would automatically fill in the appropriate link whenever new records were inserted. It would do this by comparing the DonorID between the two tables. So clever, until I realized that the Link fields are not part of the external DB. They exist somewhere in the NocoDB internal database. :unamused_face: So there is no way for the trigger to see and edit the Link field.

So that’s it? Back to doing things manually? That’s not acceptable. So, any idea how to achieve what I want? When I insert (usually pasting in multiple) new donation records, how can I automatically link them to the matching contacts?

2

Create a script to link records. Related example here. Link Records by Field

Use webhook to “Run script” on new record create / update. Details here. Create webhook

3

I see. So I need to use the cloud version for scripts. I have been self-hosting and didn’t know about scripts. I see that the Link Records script is a pre-made option you can drop in in the cloud environment. I wonder why this sort of behaviour isn’t built into NocoDB, since it is such a common need. NocoDB needs something like this script to create a persistent link between tables.

Is scripting ability ever going to come to the self-hosting version?

4

Scripting ability is available in self hosted enterprise solution (along with many other enterprise grade features).