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.

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,751)