+1I’m trying to create a field reference using a formula field. As an example: if I have a table with FieldA and FieldB and FieldC I want to create a formula field that will link to “FieldX” where X is a variable A, B, or C.
It seems possible to build this if I know all the possible permutations of field names (using SWITCH statements) but what if it’s an open-ended variable?
+21Welcome to the community, @Ben_Einstein! :grinning_face_with_big_eyes: Unfortunately what you want isn’t possible using a formula field. Formulas can only output numbers, dates, strings, or boolean values (true/false). They can’t create links.
On a related subject, links aren’t made to specific fields. The target of a link is always a record, and the displayed value in that link is the target record’s primary field contents.
The SWITCH() function that you refer to allows you to switch the function output based on a single input (usually a field reference, but other options are allowed).
Could you describe what you’re trying to achieve in greater detail? There may be a way to pull it off, but just not using the methods that you currently know about. Then again, it might still be impossible, but it’s hard to say without more detail. :slightly_smiling_face:
+1Welcome to the community, @Ben_Einstein! :grinning_face_with_big_eyes: Unfortunately what you want isn’t possible using a formula field. Formulas can only output numbers, dates, strings, or boolean values (true/false). They can’t create links.
On a related subject, links aren’t made to specific fields. The target of a link is always a record, and the displayed value in that link is the target record’s primary field contents.
The SWITCH() function that you refer to allows you to switch the function output based on a single input (usually a field reference, but other options are allowed).
Could you describe what you’re trying to achieve in greater detail? There may be a way to pull it off, but just not using the methods that you currently know about. Then again, it might still be impossible, but it’s hard to say without more detail. :slightly_smiling_face:
Sure! Thx for the detailed response.
I want to be able to have a single-select field that allows a variable to some how create a reference to the name of that field. In an ideal world, this would allow me to type/select “field_name_a” into one place in Airtable, and see the value/reference to that field elsewhere/loaded dynamically. It can be done with a SWITCH() statement (which is how I’ve done it now) but it’s very brittle if the field name is changed or you want to have, say, a reference to one of 50 different field names (ie I’d have a rediculous SWITCH() with 50+ conditions).
Does that make sense? It seems like a highly desirable feature and something that’s easily done with Sheets/Excel/most relational DBs.
+21Sure! Thx for the detailed response.
I want to be able to have a single-select field that allows a variable to some how create a reference to the name of that field. In an ideal world, this would allow me to type/select “field_name_a” into one place in Airtable, and see the value/reference to that field elsewhere/loaded dynamically. It can be done with a SWITCH() statement (which is how I’ve done it now) but it’s very brittle if the field name is changed or you want to have, say, a reference to one of 50 different field names (ie I’d have a rediculous SWITCH() with 50+ conditions).
Does that make sense? It seems like a highly desirable feature and something that’s easily done with Sheets/Excel/most relational DBs.
It definitely makes sense, and I wish that something like that would work, but unfortunately Airtable doesn’t allow for formula-generated references to other fields. Similar to records, fields are also stored internally with a unique ID, and under the hood it’s this ID that’s used for all field references. That’s why, when you change a field name, any formula referencing that field automatically updates with the new name. The field name in the header is just a “friendly” name that makes sense to you as the user.
All that aside, it might be possible to do what you want using an automation. The idea in my head is that the automation is triggered when the single select field changes. A script action in the automation runs to see what option you’ve chosen, looks for a field with that name, and inserts that field’s value into a target field of your choice. There would be a brief delay between your single select field changing and the desired value appearing in the target field, but it should work.
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.