Skip to main content

Hello! 

I am working to create a style number. If a style has two different colors, it will need two records, but the same style number.

I want the number to start with a certain number unique to the division, then another number unique to  the brand, then the third would be a number in sequential order. I have fields for Division, Brand, and Autonumber.   I used the below formula. 

 

SWITCH(Division, "DIV1", "2", "DIV2","3", "DIV3", "4", "DIV54,"5",0)

&

SWITCH(Brand,"Brand1","2","Brand2","3","Brand3","4","Brand","5",0)

&

Autonumber

 

The problem is I can’t use Autonumber because two rows may be the same style but different color.  As they are the same style, they need to have the same number. What could I use instead of Autonumber? Possibly the answer isn’t a formula, open to any suggestions.


Thanks!

Hey ​@msavvidesfownes,

I’m finding it difficult to follow, would you mind some more information? E.g. use case.

You could probably:
1. Create a new field called Style, and select out of different styles; OR
2. Create a new table called Styles, link Styles to Items (I guess that is what it is), where each style would get its own numbered code. With a lookup you can bring this number back to your Items table and have it on your formula. At the end of the day, autonumber will not be referencing style, but just a very unique id.

I might be missing a lot of things as the use case is not super clear to me. But I hope this kicks off the discussion :D

Hope I can better help soon.

Mike, Consultant @ Automatic Nation

Hi Mike,

Thanks for your suggestion. I'll try to clarify I’d like a formula to create a number for all of the same styles.

For example, there may be three records for one style, each with its own color.  I’d like the formula to recognize the style is the same and give it the same number.  But on the forth record that’s a different style, the formula would assign another number.

The below I did manually, but see how the baseball hats all have the same style no and it counts up when there is a different style. 

Let me know if I can add more info to help.

 

 

This may not be exactly the solution you’re looking for, but that base snapshot gave me an idea…

Instead of multiple rows/entries for each style and color, why not link multiple colors to the same style in a single record, and then use the list view to display the hierarchy? 

So from the example above, you’d just have 4 rows (one for each style which you could autonumber), with Baseball Hat linked to both BLACK and WHITE records. Then in a list view (either on the data side or in an interface) you can arrange it so beneath each style is a line for the colors in which it’s available. 

Thanks for the suggestion! Sadly, we need each color to have its own record. We are adding images for each color to generate a grid view that will be used as a line sheet for selling to customers. If that wasn’t the case, this would’ve solved it.

What if you converted the ‘Style Name’ text field into a linked field to a new table called ‘Styles’ instead?  Then you could put an autonumber field there and then pull it over via lookup like so:

 

Hi Adam,

Thanks for the suggestion.

I think that could work.  We may have more that one baseball hat but I bet this could still follow your suggestion with whatever the style.

 

Thanks!

Terms & Conditions