Skip to main content
Solved

Help! SWITCH formula not working

P
  • PLS
  • Participating Frequently
  • 6 replies

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.

Best answer by Alexey_Gusev

PLS wrote:

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


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)
View original
Did this topic help you find an answer to your question?

13 replies

pressGO_design

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

RIGHT(
"000000" & Autonumber, 7
)

P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 25, 2024
pressGO_design wrote:

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!

P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 25, 2024
pressGO_design wrote:

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

RIGHT(
"000000" & Autonumber, 7
)


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.

TheTimeSavingCo
Forum|alt.badge.img+18
PLS wrote:

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

Adam, Consultant @ The Time Saving Co
pressGO_design

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
Forum|alt.badge.img+12

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} & ''

 

P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 26, 2024

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

P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 26, 2024
pressGO_design wrote:

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


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

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. 

Alexey_Gusev
Forum|alt.badge.img+12
  • Alexey_Gusev
  • Brainy
  • 1113 replies
  • Answer
  • March 27, 2024
PLS wrote:

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


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)
pressGO_design
1 person likes this
  • pressGO_design
    pressGO_design
P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 28, 2024
Alexey_Gusev wrote:

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)

It worked!!! Thank you!  I assumed I needed a field reference for Autonumber just like the others. Any idea why it isn't required when the others are? 

P
  • PLS
  • Author
  • Participating Frequently
  • 6 replies
  • March 28, 2024
pressGO_design wrote:

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. 


Thanks for this info. I'll definitely use it for future questions. I appreciate everyone's help!

Alexey_Gusev
Forum|alt.badge.img+12
PLS wrote:

It worked!!! Thank you!  I assumed I needed a field reference for Autonumber just like the others. Any idea why it isn't required when the others are? 


The Autonumber word is a field reference.

 

RIGHT("000000" & {Autonumber}, 7)

 

is the same as

 

RIGHT("000000" & Autonumber, 7)

 

When the field name consists of a single word, curly braces are not required.

 
 
 
 

Reply


Most helpful members this week

Terms & Conditions