Head ---> Wall. Automation Error saying "No valid nested options"

[M_L]

Hello,

I'm looking for help on Airtable Automations. I'm trying to Find a record to update based on Table:Sessions field matching Table:SignUps field. Both fields are "formula" fields that make a text number because the original Sessions field is a linked record, and the original SignUps field is a Primary Key.

I don't know why, but I keep getting the error message below saying, "No valid nested options". It won't let me pick *either* the text field or the original Primary Key for Finding the match, and I have no idea why.

I already went through this post explaining how both of the "Find" fields have to be congruent... but as far as I can tell, mine *are* congruent, but I'm still getting the error. 

How can I get this automation to work?

Additional context below.

TIA,

ML

[Tyler_Thorson]

I can't say for certain why this isn't working for you in this particular case, but I can provide you with a simple - albeit clumsy - workaround that I use all the time:

Create a new formula field and simply insert the name of the lookup field that you are attempting reference - this appears to be {SignUp Form #} - as the formula.

Then reference the formula field instead of the lookup field itself in the automation.

IE:
Formula Field "SignUpForm#Ref"
Formula: {SignUp Form #}

As I said it's not ideal, but it always works for me.

Airtable definitely needs to improve their communication about how these relationships work, because I run into this exact issue all the time and I can never figure out what the difference between when it works and when it doesn't is.

Hope this helps!

[M_L]

Hi @Tyler_Thorson ,

Thanks for the response. I've heard about this work around, and I *think* I'm actually already using it in the automation. That's one reason I'm stumped... For the screenshot with the "Find" section, where it says there are "no valid nested options" --- both fields I'm comparing to "Find" the record are literally formula fields to create text entries of the exact same information from a Linked Record. 

I have no idea why it's not working. The Formula fields help fix automations with this error, but I wonder what the anomaly issue is that prevents this trusty workaround from working? Or if there's user error somewhere... 

If you have other insights on applying this workaround or otherwise I'd be glad to hear.

Thanks,

ML

[M_L]

@Tyler_Thorson I also wonder, is it ever a liability to have too many linked relationships? I have 3 tables that all have two-way linked record fields, and I didn't know if that could cause some sort of conflict as well

[TheTimeSavingCo]

Could you provide screenshots of your data setup?  Or if you could provide an example base that'd be best, as then we can get right into troubleshooting without needing to recreate your data setup manually!

[Alexey_Gusev · Accepted solution]

Hi,
The lookup field value is array (btw the result of Find records is array too but that's not related to your issue)
What formula are you using?
For example. first formula is a simple field name 'lookup'

second is ARRAYJOIN('lookup') to convert array to string

As we can see, (I modified Example Record picker a bit), formula1 value is array (almost the save value except additional lookup stuff), second formula is string.
maybe it can help? 
I think, Compare condition awaits a string from you.

[M_L · Accepted solution]

Hi @Alexey_Gusev ,

Thanks so much for this response - my automation is now WORKING!

I was using a "Formula" field to create my Linked Record field into text, however, the formula I used was simply the Field Name (e.g., {SignUp #}).

In response to your post, I used your solution with a broad-brush approach by updating the text field formula to the "ARRAYJOIN('lookup')" formula you referenced. I went back and tested the automation, and it's now working.

Going forward, when I need to create a text field from a Linked Record, I'll plan to go directly to the ARRAYJOIN formula as a baseline.

Thanks again! I couldn't fathom what the issue was from my knowledge of Airtable, and I'm really grateful for this solution, which is honestly much easier than I was anticipating to get it working.

Thank you, and the Airtable community!

ML