
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 23, 2018 11:25 AM
Hi, I’m looking for assistance in creating a formula that can show all our options for Inventory Location - Basically, at an Account, In House, or with a one-time buyer (that I don’t want to create an entire account for.)
If an Account is chosen (through a linked field), return that value. If a value is input in a separate Retail Buyer column (single line text), return that value. If both those fields are empty, return text “In House.”
The simple IF formula works to show Account or “In House”:
IF({Account}>"", {Account}, “In House”)
…but I can’t figure out how to turn this into a nested formula that will provide the third option of a typed in Retail Buyer.
Thanks for any help! I’ve managed to run all my company’s business through airtable, so I really hate when I can’t figure out one little thing :weary: :grimacing:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 23, 2018 11:40 AM
It’s pretty easy :stuck_out_tongue_winking_eye:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 23, 2018 12:46 PM
Oh, I’ve tried to write a nested formula, trust me :upside_down_face: I have some pretty complicated ones elsewhere in my database. I just can’t seem to get this one to work!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 23, 2018 09:34 PM
Something along these lines should work:
IF(
{Account}!='',
{Account},
IF(
{Retail Buyer}!='',
{Retail Buyer},
'In House'
)
)
You may want to use {Account}!=BLANK()
, depending on whether you use a Lookup or Rollup field to access that data. My understanding is that a Lookup returns an array rather than a string, and I have had formulas fail when they attempted to compare an empty string (''
) to a BLANK()
array. (That said, at other times ''
seems to equate to BLANK()
without issue, so perhaps this has been corrected.) This can be difficult to diagnose in cases where a Rollup is later changed to a Lookup.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 06, 2018 08:55 AM
That worked! Thank you so much!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 15, 2018 02:04 PM
This is great! Is there a way to set it up so that the IF formula produces dynamic results that link to other records?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 15, 2018 03:40 PM
Unfortunately, no – formula fields cannot return linked records as their result. They can only return text, numbers, and dates as results, so far as I know.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 17, 2018 05:28 AM
@Jeremy_Oglesby is right: Formula fields can’t return linked records. However, there are two cheats that might meet some needs.
- You can return a URL that links to a specific record in the base. Exactly how that works and what it buys you varies, depending on one’s privileges for that base. I’ve never done anything with this, but it seems it might have some utility…
- You can use values from a single remote field — that is, a field from a linked table — in a formula by using a rollup field with an aggregation formula rather than an aggregation function. There are limitations, but I’m sure I’ve yet to explore the possibilities fully. You can find more about ag formulas in the third item in this reply.
Neither of those gives you what you want, but maybe one or the other will give you something you can use…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 15, 2020 10:06 AM
Thanks. I needed a formula that returned “if not field A then field B”. Here’s what I used:
IF({Field A}!=’’,{Field A}, IF({Field B}!=’’,Field B))
Worked well enough for me.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 10, 2020 03:23 PM
Adding to the conversation as this left me scratching my head a bit as well :slightly_smiling_face:
In my case, I wanted a Formula field to pull in values from adjacent fields (within the same table). Basically: “This Field X needs a value. Get this value from Field A. If Field A doesn’t have a value, then grab the one from Field B.”
The code that worked for me was:
IF(
{Field A}!=BLANK(),
{Field A},
IF(
{Field B}!=BLANK(),
{Field B}))
Hope this helps someone!
-David
