I have a table called REQUEST that gets filled using a form
One of the fields is STATE
Based on the content of STATE a function field assigns the request to a PERSON
Result: One PERSON is responsible for one STATE. They may be responsible for any number of REQUESTS in the REQUEST table
I have another table called PEOPLE with information about each PERSON, including what STATE they are responsible for.
I know how to link the tables and use look up fields, but it seems I have to manually assign each record in the REQUESTS table.
Is there a way that the PEOPLE table can be linked so that when the REQUEST table receives data and calculates the PERSON based on STATE, that that link is created over on the PEOPLE table.
I saw this thread … Dynamic (calculated) link to a record in a linked table … and wondered whether that is what I am talking about but I don’t quite understand enough to know for sure. I have Zaps on Zapier for other things and could do that if I quite understood what it is I am trying to do.
My aim is to create a view for each PERSON, copy that link into their PERSON entry on the PEOPLE table and send that to each of them. But I can’t work out how to link the tables by STATE that isn’t manual.
At the moment I have a nested IF formula field in the REQUEST table that returns email address based on the PERSON field in that table. Manageable at this stage but unnecessarily complex as numbers of PEOPLE increase. And I would like to do other things with this.
If one person is responsible for each state, you could add another table for [States], and link [People] to it. Then, add a look-up field to your [Requests] table that looks up the person responsible for the state. This way, when someone fills out the form and they choose a state, the person responsible for that state automatically populates on the [Requests] table.
You can then use lookups to pull the data, like email address, for each person across from the [Person] table, to the [States] table, then the [Requests] table, and eliminate your big IF formula. :slightly_smiling_face: