- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 25, 2024 05:14 AM
Hi all - I'm having a very strange issue with a Formula field. I've got a Status field (single select) that informs a Formula field. i.e. if the Status field says ABC, then make the Formula field say XYZ. It's working perfectly except for two outputs. Now here's where it gets weird: after extensive testing, the issue seems to be with the output string, not the input.
- If the Status is Booking Received, the Formula field should return "Booking received, waiting for pickup confirmation" but it returns a blank field. I also can't get it to return any results when I change the formula to return my name, the word Test, or gibberish lettering.
- But when I type one of the other results that is working for another status, it works! Meaning if I type "Decline" in to the formula in place of "Booking received, waiting for pickup" it returns Declined. So weird, right?? Why will it only return certain other existing outputs?
- The same thing is happening for the "On hold" result. It will not return the words "On hold" or any random string I test it with, but it will return one of the other existing statuses.
IF(
OR(
{Latest CSF Status} = "Submitted - container cleared",
{Latest CSF Status} = "Closed - lost possession of container"
),
"Shipment & CSF complete",
IF(
OR(
Status = "(SH) Post-load sent",
Status = "(SHI) Invoice pulled",
Status = "(SHIPPED) Docs sent"
),
"Container loaded - waiting for CSF",
IF(
Status = "Tentative",
"Waiting for booking & pickup confirmation",
IF(
Status = "Booking received",
"Booking received, waiting for pickup confirmation",
IF(
Status = "Pickup confirmed",
"Booking received & pickup confirmed",
IF(
OR(
Status = "Declined",
Status = "Released",
Status = "Cancelled"
),
"Declined",
IF(
Status = "Hold",
"On Hold",
""
)
)
)
)
)
)
)
Thanks for any help!
Kelsey
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 30, 2024 07:56 AM
Thanks for the link!
Yeap, the formula is set to output: Booking received, waiting for pickup confirmation
And the formatting option is set to output: Booking received - waiting for pickup confirmation
Because the formula output doesn't match the formatting option, it just defaults to blank. Once you update all your formatting options to make sure they match your formula outputs it should work fine!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 25, 2024 07:22 AM - edited ‎Sep 25, 2024 07:23 AM
Perhaps you could try using "&" to concatenate the IFs instead of nesting them, e.g.
IF(
OR(
{Latest CSF Status} = "Submitted - container cleared",
{Latest CSF Status} = "Closed - lost possession of container"
),
"Shipment & CSF complete"
) &
IF(
OR(
Status = "(SH) Post-load sent",
Status = "(SHI) Invoice pulled",
Status = "(SHIPPED) Docs sent"
),
"Container loaded - waiting for CSF"
) etc etc
That would simplify the formula and would surface any logic issues quickly
If that doesn’t resolve the issue, could you share a copy of your base without any records in it? This would allow others to troubleshoot more effectively, as the fields and options from scratch would be quite challenging

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 25, 2024 09:41 AM
Have you checked the configuration of the single-select field? Could there be a hidden space at the beginning or end of the choice name?
I also suggest using a SWITCH() function.
SWITCH( {Latest CSF Status},
"Submitted - container cleared", "Shipment & CSF complete",
"Closed - lost possession of container", "Shipment & CSF complete",
SWITCH( {Status},
"(SH) Post-load sent", "Container loaded - waiting for CSF",
"(SHI) Invoice pulled", "Container loaded - waiting for CSF",
"(SHIPPED) Docs sent", "Container loaded - waiting for CSF"
"Tentative", "Waiting for booking & pickup confirmation",
"Booking received", "Booking received, waiting for pickup confirmation",
"Pickup confirmed", "Booking received & pickup confirmed",
"Declined", "Declined",
"Released", "Declined",
"Cancelled", "Declined",
"Hold", "On Hold"
))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 25, 2024 07:41 PM - edited ‎Sep 25, 2024 07:42 PM
Oooh, I never thought of putting another SWITCH inside the default like that, that's neat! Thanks for that!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 25, 2024 08:06 PM
@TheTimeSavingCo wrote:Oooh, I never thought of putting another SWITCH inside the default like that, that's neat! Thanks for that!
You're welcome. I format the nested SWITCH() the same way I format nested IF() functions. In these cases, I find less indenting to be easier to read.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 26, 2024 11:00 AM
Thank you for your input! I tried both alternative formulas and unfortunately neither one returns different results than the original formula (and I've also checked the Status field for extra spaces, etc). Here's a simplified version of the problem using the SWITCH formula:
- This formula returns no results:
- SWITCH( {Status},
"Booking received", "Booking received, waiting for pickup confirmation"
)
- SWITCH( {Status},
- While this formula works perfectly, but it's useless because it's successfully returning the wrong result:
- SWITCH( {Status},
"Booking received", "Booking received & pickup confirmed"
)
- SWITCH( {Status},
It's as if there's a list of acceptable outputs somewhere, and two of my outputs ("Booking received, waiting for pickup confirmation" and "On hold") do not meet the requirements for some reason. But I cannot figure out why.
Is there a way I can share a copy of the base without having to make you editors?
Thanks for your attention on this! I'm stumped.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 26, 2024 02:31 PM - edited ‎Sep 26, 2024 02:32 PM
Do you have your formula field set up to format the result as a single-select? If so, you must manually add the different possible values that the formula can output to the configuration of the field on the formatting tab.
It is possible to provide a read-only copy of the base, but that would not provide access to the configuration of the field.
A better alternative is to post screenshots of the field configurations and the sample data.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 26, 2024 07:35 PM - edited ‎Sep 26, 2024 07:36 PM
re: Is there a way I can share a copy of the base without having to make you editors?
@Kelsey- Yeap check out this link: https://support.airtable.com/docs/creating-airtable-base-share-links#creating-configuring-base-share...
And you would need to toggle on the "Allow viewers to copy data out of the base" option
Please only do this with a duplicated copy of your base with no production data in it though!
---
re: It is possible to provide a read-only copy of the base, but that would not provide access to the configuration of the field.
@kuovonne Hmm I think this works for field configurations, but not for automations. Here's a public shared base link, and once we copy that base to our own workspace we have access to the field configs: https://airtable.com/appOYfSvD3zaCSjy2/shrfKDwbePrq3wMVT
For automations we need to actually be collaborators though I believe

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 27, 2024 04:21 PM
Ah yes, if the external user can make a copy of the base with read-only access, the user would have access to the formulas in the copy.
If Kelsey goes this route, I recommend having enough sample data to reproduce/demonstrate the issue, even if actual production data is removed. However, I'd like to hear if the formula field is formatted to output the result as a single-select first. I think that is the most likely case given what has been posted so far.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 30, 2024 06:33 AM
Okay does this link work? I scrubbed the data. The issue is in the Shipments table - you'll see the relevant fields in the Sandbox view. The issue is occurring in the Next Status Action field. I've also created a TEST Formula Field to play around in - that field will get deleted from my base once I figure this problem out. And yes @kuovonne, the formula field is formatted to return as a single-select field. Thanks for taking a look!
