Forum Discussion

zboland's avatar
zboland
Copper Contributor
Aug 03, 2023

Microsoft Lists - Creating a unique SKU from a calculated column

Microsoft Lists - Creating a unique SKU from a calculated column, fetching the [Type de produit] column and adding unique numbers at the end

 

I am trying to figure out the best way to add a calculated column that would generate a SKU value for products we have at our company. These products are for employees (either welcome gifts or items being borrowed). 

Some items such as water bottles with our branding, we have over 800, but would be counted as 1 row.

I need a unique SKU because, let's say we have 3 types of water bottles, the SKU would look like:

Bouteille-001

Bouteille-002

Bouteille-003

 

This would be to identify different water bottle types. If I could have it where the first digit of the 000 would be the material for example (1 = plastic, 2 = metal, 3 = etc), that would be even better, and the second digit would be the color (1=white, 2= black, 3=red)

 

So, a Plastic, Red bottle would be

Bouteille-131

and if there were more than 1 type of red plastic bottle, the next one generated in the calculated column would be 

Bouteille-132

 

First off, is it possible to do this in Microsoft Lists?

If so, how would I go about it?

 

I currently was going to start with this, but this formula seems to be incompatible or not working with lists

=CONCATENATE([Type de Produit]], "-", TEXT(NEWID(), "D"))

 

Here is a picture of what the list looks like:

 
 
 
 
 

 

 

 

I would be grateful for any help I can get with this.

Thanks,

Zach

Lists
Microsoft Lists
  • Fares_Alabsi's avatar
    Fares_Alabsi
    Copper Contributor
    Aug 04, 2023
    You can use the following formula to create a unique SKU value for products in your list:

    ```
    =CONCATENATE("Bouteille-", TEXT([Type de Produit], "D"), "-", TEXT(ROW(), "000"))
    ```

    This formula will first concatenate the string "Bouteille-" with the value of the [Type de Produit] column. Then, it will convert the row number of the current item to a string and pad it with zeros to the length of 3. Finally, it will concatenate the two strings together to create the SKU value.

    For example, if the value of the [Type de Produit] column is "Plastic" and the row number of the current item is 1, the formula will return the value "Bouteille-100". If the row number of the current item is 2, the formula will return the value "Bouteille-101".

    Here are the steps on how to create a calculated column with this formula:

    1. Go to the list that you want to add the calculated column to.
    2. Click the **Columns** tab.
    3. Click the **Add column** button.
    4. In the **Column type** section, select **Calculated**.
    5. In the **Formula** field, enter the following formula:

    ```
    =CONCATENATE("Bouteille-", TEXT([Type de Produit], "D"), "-", TEXT(ROW(), "000"))
    ```

    6. Click the **Save** button.

    The calculated column will now be added to your list. You can use the column to generate unique SKU values for your products.
    • ganeshsanap's avatar
      ganeshsanap
      MVP
      Aug 04, 2023

      Fares_Alabsi ROW() function is not supported in SharePoint calculated formulas.

       

      zboland I don't think it is possible using the SharePoint calculated column formulas as You cannot reference a value in a row other than the current row. You cannot check if bottle with same type or color is already available in the list (in another list item) and so you cannot add next sequence number like 1, 2, 3...etc. to unique ID based on other list items.

       

      Source: Introduction to SharePoint formulas and functions 

      Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources