Getting invalid ID for automation

[auekk2787]

I have a table called "Entities" which lists schools and school districts. I have a link record field which allows me to link records to the same table, to create a "parent" district to multiple "child" schools. 

AT has the limitation of not creating cross-references within the same table. I'm trying to create an automation to auto fill this.

I have one column called "District" which lets me link a parent district to a school record. I have another column called "Schools in District" where I can manually link the child schools to that district record. 

My automation is giving me "received invalid inputs" even though the record IDs look correct in the test runs.

2 steps: when the "District" field is updated (I've entered the school's parent district), then find the District record and update the "Schools in District" with that school record. This test all seems correct:

 I manually updated the district for Crawford to Atlanta Public Schools. It then found the Atlanta PS record and entered "rec3EFJSipy2cZjIX" for Schools in District, which is Crawford's ID. 

Can anyone help?

[AirOps]

Hi @auekk2787, 

Is there a particular reason you aren't using a lookup field to display the "Schools in District" rather than linking them directly? It sounds as if your automation is trying to achieve what this Airtable field can display. 

By using a lookup field based on your "District" linked field, it would automatically show a list of all of the child schools linked directly on the district table upon updating the "District" field with on the entities table. 

- Chantal 

[auekk2787]

The districts and schools are in the same table. There is no school that appears automatically in a district's record. In my screenshot, I had to add the 2 schools manually. So there is nothing to lookup.

This is the same issue as discussed in this thread:

https://community.airtable.com/t5/base-design/cross-reference-linked-records-in-same-table/td-p/7839...

[AirOps]

I see! My apologies, I misinterpreted your question. 

In this case even if they result was valid in your second step, it would not function in the future, as that linked field would always get overwritten by the most recent school manually linked to a district. Scott's recommendations in the thread above follows the advice that I would give you to solve this issue. They are the most scalable solutions. 

The simplest solution presented in that tread to track these relationships was to create a new table to track the district and school ties, as mentioned by the original poster. 

However, if you did want to focus on just one table, you can use some clever helper fields to help make your automation work and get around Airtable's limitation.

To get around the Airtable automation not accepted the ID from the first step you can set up a helper field to give you the name of the district linked in text. I called it "District Text" as below.

With that set up you can set up an automation as follows: 

The find record step is so that we can get a list of all of the schools already linked to this district to you in our update step. 

In step 3 the Airtable Record ID is the record ID from the trigger, and the list is from the find records step, can get this element like so: 

1. 

2. 

3. 

-Chantal

[auekk2787]

Ah, thanks for noting the overwrite. In that case, I should go through the linking table.

I'm not clear how they set up the second table to track school ties. Do you have advice on doing this?

[AirOps]

If you are planning to use multiple tables I would recommend splitting your tables into one table to host the District records, and one table for School records. In this case your district is a parent record for many potential schools. 

You could then create a direct link between the two tables. 

District Table: 

Schools Table: 

Note:  if two tables is undesired the solution suggested above will not have the overwrite issue, as we make sure to add the find record step to get a list of all of the schools previously linked and we link them again. 

[auekk2787]

I see. I thought there was a more clever solution of some dummy secondary table that just serves as a School-District link. 

[auekk2787]

I tired setting up this automation, and I'm still getting my original error of Received invalid inputs. 

I updated the district for Hubbard Media Academy to Alum Rock:

 It ran the find records:

But it's refused to enter the ID for Hubbard - reck8RTtl3EdQTjzP

 I'd like to use this method but I just can't get it to accept the record ID.