Syncing tables across bases--help needed

[PLS]

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.

[Alexey_Gusev · Accepted solution]

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)

[PLS]

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? 

[PLS]

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

[Alexey_Gusev]

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.