Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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(
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.
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!
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
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"
))
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!
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.
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:
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.
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.
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
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.
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!