Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Help! SWITCH formula not working

Topic Labels: Formulas
Solved
Jump to Solution
3702 13
cancel
Showing results for 
Search instead for 
Did you mean: 
PLS
4 - Data Explorer
4 - Data Explorer

I'm new, so apologies for any blatant errors. I'm trying to create a SKU in the table primary field using the SWITCH function based on other fields in the table. My initial try included a lengthy list of colors (all unique) and the 5-letter results (not unique) for output, and the formula failed to even load. Removing the color section, I was able to generate the appropriate results based on the other columns. However, after struggling with the color field and failing, I reset the formula to the original (no color), and now get #ERROR!. I cleaned up and sorted the color entries to perfectly match the fields, and now get #ERROR!, which is progress, I guess, as at least the formula loaded.

Next, I added a lookup field for the color code I want in this table and changed the color SWITCH section to reference the lookup field, but I still get #ERROR!. Here's my new formula:

SWITCH(
{Product Type},
"Apparel", "APP",
"Accessories", "ACC",
"Blankets", "BKT",
"Brock", "BRK",
"Other", "OTH"
) & "-" &
LEFT(
{Blank}, 17
) & "-" &
LEFT(
{Color Code (from Color)}, 5
) & "-" &
LEFT(
{Size}, 3
) & "-" &
RIGHT(
{Autonumber},
"000000" & Autonumber, 7
)

It seems that this should be a simple formula. The referenced fields are all either fields in this table, primary key fields in other linked tables or a lookup field in a linked table (Color). I'm sure I'm missing something very basic, but it eludes me. At this point in a resident program, I would close out the program and restart the computer, but what is the equivalent for an online program?

Thanks for any insights.

1 Solution

Accepted Solutions

I tried your formula and get ERROR too
After I corrected mistake with Autonumber and inserted 'add empty string' for lookup, it works. Please check.

SWITCH({Product Type},
"Apparel", "APP",
"Accessories", "ACC",
"Blankets", "BKT",
"Brock", "BRK",
"Other", "OTH") & "-" &
LEFT({Blank}, 17) & "-" &
LEFT({Color Code (from Color)}&'', 5) & "-" &
LEFT({Size}, 3) & "-" &
RIGHT("000000" & Autonumber, 7)

See Solution in Thread

13 Replies 13
pressGO_design
10 - Mercury
10 - Mercury

Hmmm... Try changing the RIGHT formula at the end to

RIGHT(
"000000" & Autonumber, 7
)

Sadly, no change. I still get the error message.

Thanks for trying!

I should have also said, the formula originally worked without the Color reference, so I think that must be the problem area, although I now get an error even when I delete the Color section. Hence, my complete confusion.

Is `Color Code (from Color)` a lookup field?  If so, perhaps try modifying that line to `{Color Code (from Color)} & "", 5`

If you could provide an example base that'd be great; troubleshooting it via the base itself is a lot smoother

pressGO_design
10 - Mercury
10 - Mercury

Yes - a view into the base would be easier. I’m also super-curious about the Blank column where you’re pulling 17 characters. 

Alexey_Gusev
13 - Mars
13 - Mars

Hi,

@PLS wrote:

and changed the color SWITCH section to reference the lookup field, but I still get #ERROR!.



Does it mean {Product type} is lookup?  If Yes, add empty string to convert array to string:

 {Product type} & ''

 

PLS
4 - Data Explorer
4 - Data Explorer

Thank you for the responses. I tried the Color Code lookup suggestion, no joy. No, Product type isn't a lookup, it's a link to the primary key field of another table, as are several of the other references. Only Color Code is a lookup field (with the primary key field linked, but it's the full text color, so too long for my SKU). 

How do I attach a copy of the table? Since it's online, there's no file to attach...?

It does sound strange, I admit. It's actually a table for blank apparel and items we sublimate designs onto for sale (mostly t-shirts), and the 17 characters are brand (4 letters) and model number (5 letters/numbers), type of apparel or item (3 letters), type of neckline (apparel, 1 letter) and size group (youth, men's, women's, 1 letter), plus dashes between. It makes up the bulk of the SKU.

pressGO_design
10 - Mercury
10 - Mercury

Make a copy of your base don’t duplicate the records, add some dummy data - just 3 or 4 records, and then share a read-only public link to that base.