cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
My_connect
Advocate II
Advocate II

calculated column to integer to sort

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 🙂

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisPiasecki
Super User
Super User

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.

View solution in original post

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

View solution in original post

My_connect
Advocate II
Advocate II

this worked

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

View solution in original post

9 REPLIES 9
ChrisPiasecki
Super User
Super User

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
Advocate II
Advocate II

@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.'.

 

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
Advocate II
Advocate II

 

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

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
Advocate II
Advocate II

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

My_connect_0-1619905114460.png

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 🙂 

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
EricRegnier_0-1619944347565.png

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
Community Support
Community Support

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
Advocate II
Advocate II

this worked

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (1,890)