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