Skip to main content

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.

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

RIGHT(
"000000" & Autonumber, 7
)

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!

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.

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

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

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

 

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

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.

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. 

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)

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? 

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!

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


Terms & Conditions