Skip to main content

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 😩 😬

It’s pretty easy 😜



It’s pretty easy 😜






Oh, I’ve tried to write a nested formula, trust me 🙃 I have some pretty complicated ones elsewhere in my database. I just can’t seem to get this one to work!

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.

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.


That worked! Thank you so much!



This is great! Is there a way to set it up so that the IF formula produces dynamic results that link to other records?



This is great! Is there a way to set it up so that the IF formula produces dynamic results that link to other records?


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.



This is great! Is there a way to set it up so that the IF formula produces dynamic results that link to other records?


@Jeremy_Oglesby is right: Formula fields can’t return linked records. However, there are two cheats that might meet some needs.





  1. 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…


  2. 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…

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.

Adding to the conversation as this left me scratching my head a bit as well 🙂



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

Reply


Terms & Conditions