Limiting number to certain amount of digits

[Nick_Neumann]

I deal with SKUs that are 7 digits long. the first 6 digits represent the Master SKU and the 7th digit represents a variation of the Master SKU. In my table, sometimes the SKU comes in as 6 digits and sometimes at 7. I would like to create an expression that will delete the last digit of the SKU, ONLY if the SKU is 7 digits long so that way all SKUs in the column are always 6 digits. How would I go about this?

[Marc_Dillon]

LEFT( [SKU] , 6 ) should work.

[Nick_Neumann]

Marc_Dillon:

LEFT( [SKU] , 6 )

Hey @Marc_Dillon, thank you but didn’t seem to work. I should also mention that the SKU column is Ref type which I images is the reason it didn’t work

[Marc_Dillon]

What was the result if it didn’t work?
Where did you enter the expression?
What is your data structure?
Does the referenced table have different key and label columns?

[Nick_Neumann]

I put it in as an app formula and the result was that the SKUs remained the same number of digits, either 6 or 7. The referenced table has the same Key column (SKU). The label columns are different from each other whoever and neither are the SKU. The table which I am trying to apply the formula deals with our image database. The SKU metadata for the images are either 6 or 7 digits long. The master SKU table which the image table always has 6 digit SKUS.

[Marc_Dillon · Accepted solution]

Are you trying to input the expression in the SKU column? That won’t work. You’ll have to create a new column to hold the modified value.

[Nick_Neumann]

Okay got ya. Just made a virtual column with the formula and turned that into the ref and it worked. I’m wondering why a virtual ref type column works with the formula but a non virtual ref type column wouldn’t?

[Marc_Dillon]

It has nothing to do with real vs virtual.

You can’t modify the value in a column via specifying an app formula, especially one that references itself.
Presumably the SKU column is input by the user. Specifying an app formula is for creation of a value, and doing so prohibits user-entered data in that field.

[Nick_Neumann]

Okay, this makes sense. Thank you for the help @Marc_Dillon!

[Aleksi]

Just an idea… you can change digits with an Event action when the form is saved. Then you can run the formula LEFT([SKU],6) with that action.

[Nick_Neumann]

This makes sense, but the SKUs aren’t provided by user input, they’re generated by an SQL query that overwrites the pre existing sheet on a daily basis.

[Aleksi]

Okay… then it won’t work in your case.