cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KED
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

@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

@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

v-litu-msft
Community Support
Community Support

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.

 

KED
Frequent Visitor

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,432)