cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cannot create currency field in CDS from Excel

Trying to bring over data from Excel to CDS. Problem happening in the Create a new record CDS action. A currency field in Excel is not available as a dynamic value. It doesn't seem to matter how the field is formatted in Excel - text, number, currency - it still won't show up.

 

By the way, I have another flow to create a new record in another entity when a record is created in this entity. It needs to pass the Request Amount from the first entity to the Request Amount in the second entity. In that second flow, in the Request Amount field of the Create New Record action, I can pass the Request Amount (base) field of the first entity, but not the plain old Request Amount field. But in the first flow in the first entity, the Request Amount (base) field is not showing up.

 

20200531 Currency field not showing as dynamic value in flow.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Cannot create currency field in CDS from Excel

@KED 

Hi there.  It might work if you drop the output of your Request Amount into a Compose action before the CDS step, and then see if the output from the compose is available to use there.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

3 REPLIES 3
Highlighted
Super User
Super User

Re: Cannot create currency field in CDS from Excel

@KED 

Hi there.  It might work if you drop the output of your Request Amount into a Compose action before the CDS step, and then see if the output from the compose is available to use there.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

Highlighted
Community Support
Community Support

Re: Cannot create currency field in CDS from Excel

Hi @KED,

 

I assume that the Request Amount field format in the CDS is a string, and the Request Amount field in the Excel is integer caused the dynamic content not match. 

You could change the Request Amount field in CDS into an integer, or you could input expression into it:

item()?['Requested Amount']

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Highlighted
Frequent Visitor

Re: Cannot create currency field in CDS from Excel

@edgonzales thanks much - that worked for both currency fields, Request Amount and Annual Net Profit. @v-litu-msft thank you, and I wanted to mention that the Annual Net Profit field in Excel was formatted as text and the Request Amount field in Excel was formatted as number, but both were correctly passed to their respective CDS currency fields with the compose action. 

 

(By the way, I also tried compose with the Excel datetime field "Request Date" for the hell of it but still got the error saying CDS expected a datetime format and I was trying to pass it that kind of 43979.6504889236-looking number that Excel stores dates as. As a workaround I created text fields in Excel for date (=TEXT(AG2,"mm/dd/yyyy"), time (=TEXT(AG2,"h:mm AM/PM"), and datetime (=CONCAT([@[Request Date Text]]," ",[@[Request Time Text]]) and passed the Excel "Request Datetime Text" text field successfully to the "Request Date" datetime field in CDS.)

 

Writing a flow to bring in data from an Excel spreadsheet stored on SharePoint, as it is updated, seems to be such a common use case. It would be really nice if the CDS/Power Automate team could either make super clear documentation about these workarounds or if they could make CDS/Power Automate aware of Excel enough to avoid having to do these workarounds (i.e. if it's formatted as currency/date in Excel, it shows up as a dynamic value in a flow to a CDS currency/date field).... 🙏

 

This 👇 works 

20200601 Currency field showing up because of compose action.png

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (8,367)