UXR Base Automation Help

[ChloChlo]

Hi everyone! I'm designing a UXR base and would love advice on how to design an automation that allows me to connect two tables and two bases together. My current base design consists of the following:

Table 1: Intake and Planning

• Primary field: study type and study description
• Potential users: linked to another base (all users table)
• Type of study: interview, focus group, survey, usability test
• Study description
• Goals 
• Hypothesis 
• Study guide
• Notes 
• Study owner 
• UXR Notes by Study: look up from Table 2 

The view is grouped by week 

Table 2: UXR Notes by Study/User 

• Primary field: user name - study type - study description 
• User name:
• Study type 
• study description 
• User status: contacted, not participating, participating 
• Interview date 
• Notes 
• User: look up from table 3, link from "potential users" on table 1 
• Goals 
• Hypothesis 
• Attachments 
• Study Guide 
• Study owner 

Table 3: All Users 

This table is taken from another base that gives a view of all the potential participants we would like to contact 

• Email of the potential participant
• Name 
• Potential Studies: link to Table 1 
• Last contacted: Last modified date of "user status" on table 2 
• Notes: link to Table 2 

How can I create the following automations?

1. When a record is created in table 1 and  a user is added to the field, "potential users," a new record populates on table 2 for that user. I am not too sure how this should work If the same users may be added to multiple studies. 

2. How can I link the table 3 to table 2 so that whatever study a user is involved in, the record on table 2 shows up to the corresponding user record on table 3?

I appreciate any help that can be extended. Thank you!

[TheTimeSavingCo]

> 1. When a record is created in table 1 and a user is added to the field, "potential users," a new record populates on table 2 for that user.

If there are always going to be 10 or fewer potential users, then you can look into Zapier as it handles line item support of record creation for up to 10 records and would be the simplest to set up

If there are going to be more than 10, you'll need to either look into running a script (either from an automation which needs a Pro plan, or a scripting extension with a button click)

And finally, you can force record creation by pasting a unique comma separated list of values into a linked field with an automation.  You'll need to create a formula field that helps you format all the data for your automation to paste it in

> I am not too sure how this should work If the same users may be added to multiple studies. 
You probably want one individual record per user per study, regardless of how many times a user gets added to different studies?  So if User A gets added to Study 1 and Study 2, in Table 2 you should have two records:
1. User A - Study 1
2. User A - Study 2

> 2. How can I link the table 3 to table 2 so that whatever study a user is involved in, the record on table 2 shows up to the corresponding user record on table 3?

Given that Table 3 and Table 2 are in different bases you're never going to have a "real" link I'm afraid.

The simplest way to do this to me would be with synced views.  You'd sync Table 2 to the base with Table 3 in it, and create an automation that would appropriately link the synced view records of Table 2 to their respective records in Table 3

This requires at least a Plus plan for automatic syncs, and would be 5-15 minutes behind

The next option would be some third party service like Zapier, where, on the appropriate update of a record in Table 2, you look for the record in Table 3 and update it

And lastly, there's always scripting