Answered

Update item doesn't work for number formatted columns (but create item does)

(0) Share

Report

Posted on by lyrec81

Hi, I created a simple flow where items in a SharePoint List gets updated using values from a SharePoint Excel if the ID matches, and to create a new row if not.

Everything works, except when I want to update items on the List that's formatted as a number or currency (instead of text). In the screenshot below, Opp and Client are formatted as text while 2023 Q1 is formatted as a currency. I can insert the expression to update the '2023 Q1' on the List with values from the '2023 Q1' on the Excel.

BUT as soon as I click save, it turns into the List values. I even tried to just use the function @items('Apply to each')?['2023 Q1'] but it says the expression is invalid.

What's REALLY weird is that the same exact activity, but just on the "Create item" side of things works.

AND I've had the exact same flow work for a different List and Excel formatted the same way. Please tell me this is a glitch since updating and that it'll be fixed soon. Or please just send help. Thank you!

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (2)

wskinnermctc 6,519 Moderator on at

Like (1)

Report

When you run the flow and update item, is it populated correctly?

I'm asking because sometimes the visual appearance of the dynamic content, (like has the SharePoint logo or has the Excel logo), is not correct, but the value is correct.

I don't know why the image changes, but I have seen that before.

If the SharePoint list value is updated correctly with the Value from Excel, then don't worry about the display difference.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report

What is the InternalName or EntityPropertyName of the SharePoint column 2023_Q1?

If you are unsure what I am referring to about the SharePoint column InternalName, you can check my post here about SharePoint EntityPropertyName.

If you created the SharePoint column named as "2023_Q1" the EntityPropertyName will most likely be something like "OData__x0032_023Q1" since the internal name can't start with a number.

The Excel connector also has problems with columns that are only a number. So if the column title in Excel was just "2023" then it probably wouldn't get the column. Yours might be titled "2023 Q1" which shouldn't really be a problem, but starting with a number and having a space might not help.

Anyways, I would suggest changing the column name in the SharePoint list to be "Q1_2023" and renaming the column in Excel like "Quarter1_2023" and see if that helps.

Was this reply helpful? Yes No
lyrec81 15 on at

Like (0)

Report

Thanks for responding! When I run the flow, it doesn't populate the Q1 column, so it's definitely not just the display that's affected.

Good point on the column name starting with a number. I changed it to be "Q1_23" on both the Excel and the List, but same issue persists. Again, what's weird is that "Create Item" was fine even with the "2023_Q1" name, but maybe the issue is with "Update Item" specifically.

And yeah, the EntityPropertyName is still "OData__x0032_023Q1" after I renamed it to "Q1_23". I even tried just naming both List and Excel columns to something without a number like "Qrtr" and would still run into the same issue. The only way I can fix this is by formatting the List column as text (Single Line of Text) instead of a number or currency.

I think the issue is the internal name. I've already changed the List column to "Qrtr" but it's still showing up as "OData__x0032_023Q1". Can you help with this issue? Really appreciate it!

Was this reply helpful? Yes No
lyrec81 15 on at

Like (0)

Report

So I deleted the columns and created a new one that's called "Q1_2023". The internal name now aligns.

But the issue is still there. The only workaround I can think of is setting the List to be formatted as text, then adding a function to update item as currency, but formatNumber(item()?['Qrtr1'],'$#.00') didn't work.

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (1)

Report
ok @lyrec81 I see the issue now.

First: You can’t change the EntityPropertyName or the InternalName ever. You have changed the title/displayname of the column, but not the InternalName that is used by power automate. You would have to delete the columns and make new ones. I highly recommend creating new column name with better names now so they aren’t a problem in the future.

Second: You should completely delete the Update step. Remove the action from the flow, then Save and back out of the editor. Then go back into the editor and create a new Update Item action. We are trying to refresh the action connector. Sometimes they get like stuck with old format stuff and its good to reset them.

Third: I think this is the main problem. The use of items()?[‘ColumnName’] is confusing the editor because there is a nested apply to each.

You need to identify which Apply to each you want the data from.

So instead include the apply to each to the dynamic content name just like the others appear.

items(‘Apply_to_each’)?[‘ColumnName’]

If you type this into the fx Expression box it will accept it as a full expression. It might be purple/pink color, but thats ok. When you run the flow it should change back to the correct green.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
You will need to type it into the expression.

See how all of the other dynamic content has items('Apply_to_each')? where there is an "s" for items and then "Apply_to_each' is within the parentheses?

Your Example:

formatNumber(item()?['Qrtr1'],'$#.00')

Change it to:

formatNumber(items('Apply_to_each')?['Qrtr1'],'$#.00')

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (0)

Report

@lyrec81 Thank you for changing the column names. That will help in the long run even though it wasn't exactly the issue.

I just made an example that is basically the same as your flow. I'm going to provide pictures below and some instructions. You should be able to see how the dynamic content is added as an expression.

Here is an overview of my flow. It is the same as yours.

Overview of Flow Actions

This is the original table data that I used to create items in a SharePoint List.

Original Excel Table Data

This is the SharePoint List after the original items were created.

SharePoint List - Original Items CreatedSharePoint List Settings Column Reference

Here is the Create Item action. You can see I had to enter the Excel dynamic content manually as an expression.

Create Item Action - manually insert Excel dynamic content as an expression

Here is the Update Item action. This also required the Excel dynamic content to be entered as an expression.

Update Item Expression - dynamic content entered as expression

Update Item Action - the Excel dynamic content has to be manually added as an expression

Here is my updated Excel table that has made changes to the employee's names and salary.

Excel Source with updated data and new rows to add to the SharePoint List

This is my SharePoint List after the flow was run. You can see everything was updated or created.

SharePoint List After Flow Run - Updated Items and New Items

After the flow has run, go back and look at the Update Item and Create Item actions. The expression that was entered is now correctly showing as dynamic content from Excel.

Create Item Action AFTER the flow has run - the expression now displays as Excel dynamic contentUpdate Item Action AFTER the flow has run - the expression now correctly displayed as Excel dynamic content

If you look at this example you should be able to have your flow set up correctly with a Currency type SharePoint column.

Was this reply helpful? Yes No
lyrec81 15 on at

Like (1)

Report

Thanks very much for your detailed reply @wskinnermctc! I got busy with other work but finally had time to come back to this. Writing it out in the fx expression box (and also creating a brand new flow) finally worked! Definitely noting the naming nomenclature from now on.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Glad it works now. After enough times of having to deal with fixing a reference to columns or actions by name in Power Automate, you will get better at initially creating names that are easier. One of those things everyone has to learn the hard way.

It doesn't help that the flow creator doesn't always make references transparent either.

Was this reply helpful? Yes No