cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msimmonds
Regular Visitor

Find the length of a field in a table

I am building a flow to pull data out of invoices we receive from our suppliers so that I can then import that data into our legacy ERP.  The current challenge is that all value fields include '$' but the ERP will only accept numeric data.

 

For the invoice total I put together a series of actions that find the position of the '$' in the string, the overall length of the string and then a substring to pull out just the numbers (with a float thrown in so that I do get a true numeric value).  No problem here, it works great.

 

My problem occurs when working with the table data that AI Builder generates.  The short version is: When creating attempting to create an action that works with a field from the table I can reference that action directly but when I try to create an expression to work on that action Power Automate drops all of the fields from the Dynamic Content.

This group loves detail so here is the longer version.

msimmonds_0-1632156800160.png

We start with an Apply to Each so that we can run a few evaluations on each line to determine if it is appropriate for entry.
The first two conditions are doing some formatting and a first evaluation of the line.

The problem lies within the Scope group.
Find text position...: no problems.  I can select the field needed from the table.
Compose start position....: This simply adds 1 to the result of the Find Text... action.
When I try to create a Compose for : Length(table field) none of the fields from the table are available for selection in the Dynamic Content.  

I doubt I am the first person to run into this so hoping someone smarter than I am can show me the way.

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @msimmonds 

 

If you know the name of the key, you can use the expression length(item()?['nameofkey'])

 

If you don't know the name of the key, you can add the dynamic block elsewhere in your flow, hover your mouse of the block and see the expression.  Another method is to use the ellipses on the action where you have inserted the dynamic value and choose - peek code.  A final method but I don't think is going to work (as you say you cannot see the dynamic values), is to type length() into the expression builder and then switch tabs to the dynamic values and if the block is there, you could select it.

 

If arrays are new to you, I have a video here https://youtu.be/6nJSUNh579w

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

View solution in original post

2 REPLIES 2
DamoBird365
Super User
Super User

Hi @msimmonds 

 

If you know the name of the key, you can use the expression length(item()?['nameofkey'])

 

If you don't know the name of the key, you can add the dynamic block elsewhere in your flow, hover your mouse of the block and see the expression.  Another method is to use the ellipses on the action where you have inserted the dynamic value and choose - peek code.  A final method but I don't think is going to work (as you say you cannot see the dynamic values), is to type length() into the expression builder and then switch tabs to the dynamic values and if the block is there, you could select it.

 

If arrays are new to you, I have a video here https://youtu.be/6nJSUNh579w

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

View solution in original post

Thank you for supplying the missing piece!  And I will definitely be watching more of your videos - that one really worked for me.

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.

Users online (2,106)