calculated column to integer to sort

[My_connect]

Hi All 

We have a calculated column ID_123  = concat(Auto_ID, Year, ID),

I would like to create a duplicate column of ID_123 number format so we can sort as needed.

What is the best approach please advice

Thank you

Connect 🙂

[ChrisPiasecki · Accepted solution]

Hi @My_connect,

The easiest method would be to use a Cloud Flow to update your new column.

1. Add a When a row is added, modified or deleted trigger
2. Add your Auto_ID, Year, and ID columns in the Column Filter (make sure you use the column name and not the display name)
3. Add an action to Update a Row, using the same row ID from you trigger step.
4. Set the new number column via an Expression, using the concat function on those 3 columns, then wrapping it in an int function to convert it to a number. The expression would look something like:
   int(concat(<column1>,<column2>,<column3>))

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[My_connect]

@ChrisPiasecki  Thank you for the suggestion

Im getting following error when i try to apply 

int(concat(triggerOutputs()?['body/_Toons_AutoID_label'], column 2,column 3))

Im getting similar error even with one or 2 columns, i think syntax is wrong ? my duplicate column format is whole number.

duplicate column = whole number 

Auto_ID, = whole number

Year, = Text

ID = Whole number

here is the error

Unable to process template language expressions in action 'Update_a_row' inputs at line '1' and column '18807': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[EricRegnier · Accepted solution]

Hi @My_connect,

Is ID_123 a text or numeric column/field? If text, then you don't need to cast to int after concat. Try simply:

concat(triggerOutputs()?['body/_Toons_AutoID_label'], column 2, column 3)

But note that if it's a text field you wont be able to sort as a proper numeric field. If you do need it as a numeric, then you'll also need to handle empty/null columns and decimal places to ensure there's no  spaces or characters. A simple approach is with substitute and float (for decimal numbers) functions:

float(Substitute(Trim(concat(triggerOutputs()?['body/_Toons_AutoID_label'], column 2, column 3)), " ", ""))

Also note, the max value if int is 2147483647 in case your concatenation goes over.

Hope this helps

[My_connect]

Thanks for the input @EricRegnier 

ID_123 = Text format = contact(Auto_ID,Year,ID)

Auto_ID, = whole number

Year, = Text

ID = Whole number

New column aka Duplicate column = should be number , which should match ID_123

[EricRegnier]

Cheers, suggest to create a new temp column of type single line of text. Run the flow against that column to see the outputs you're getting to ensure it can be converted to a int. Then apply those transformations to the real whole number column and delete the temp column. Will save you a lot of time trying to guess what substitutes to put...

cheers

[My_connect]

I'm pretty sure this is some kindaa syntax error, what im i missing ?

I've made a test solution to figure out this 

Name is Text

Saless_ID is Text and rest of them are whole number

Ive tried following to add numbers to Key column (whole number)

@{triggerOutputs()?['body/toon_saless_id']}

@{triggerOutputs()?['body/_toon_saless_id_label']}

int(@{triggerOutputs()?['body/toon_saless_id']})

int(@{triggerOutputs()?['body/_toon_saless_id_label']})

Thanks in advance 🙂 

[EricRegnier]

When you peek code the syntax should look something like this:

@int(triggerOutputs()?['body/toon_saless_id'])

but in the designer it should look like this

So you don't have to put an "@".

Also, I see in your column that you have a comma. Remove it before casting to int. You can use the substitute function again. 

Hope this helps!

[v-albai-msft]

Hi @My_connect 

How are things going? Have you solved your issue?

If one of above solutions is useful for you, please consider marking his reply as a solution. This will benefit other community members who stuck with the same question.
In addition,If your issue is not resolved, please don't hesitate to let us know.
Best Regards,
Allen

[My_connect · Accepted solution]

this worked

int(concat(triggerOutputs()?['body/toons_id'],triggerOutputs()?['body/toons_column2'],triggerOutputs()?['body/toons_column3']))