cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Villanous81
Helper I
Helper I

Prepend name to created value

I'm hoping someone can help me with creating values via Power Automate with a prepended name and then incrementing numbers.

 

So essentially I would take a value from a parsed column value in Excel and use that as my prepended value and then create subsequent items with incrementing numbers. So for example RS001(this is an example of the value pulled from Excel)-1, RS001-2, RS001-3, etc.

 

The end result is that I'm hoping to create unique items in Business Central based on this Excel column value.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Villanous81 

 

This makes sense. Object numbering starts from 0. So I suspect row 2 is being returned in the first response and then null is in the second. If you want to start from 1, use range 0,length for the input but add 1 to item() within the loop before prepending.

 

Damien

View solution in original post

12 REPLIES 12
DamoBird365
Microsoft
Microsoft

Hi @Villanous81 

 

Not sure how many of these you are looking for but you can use range(0,x) and concat as follows:

 

DamoBird365_1-1643656344821.png

 

Output for each loop is as follows:

DamoBird365_2-1643656375663.png

 

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Thank you for the reply Damien, this is great and I can certainly use in other situations. The issue I have (see attached picture), is your proposed solution would require me inserting an 'Apply to Each' inside another 'Apply to Each' and that's never pretty. In my Excel worksheet I have a column value titled 'Oppty ID' and I'm already doing a compose to capture the 'first' row, so this can serve as the base of my newly created Item Name. Then like I stated before I would like append a -1, -2, -3, etc. to these when creating them in my Apply to Each loop.

 

Workflow Example.png

Hi @Villanous81 

 

You can do the same with a select.  If you use range(0, length(excel value array)) as input to your apply to each, you can create your unique reference per row and call each row of the excel list rows by integer ?[item()]. 

Does that make sense?

 

Damien

Villanous81
Helper I
Helper I

So I would change my apply to each from @{outputs('List_rows_present_in_a_table')?['body/value']} to something like  range(0,10(outputs('List_rows_present_in_a_table')?['body/value']))?

 

Sorry I might not be seeing it.

@Villanous81 

 

Let's start from the beginning.

 

1. You have an excel sheet with rows... and access the data with list rows

2a are you getting multiple rows - or just one row for the value of the prepend?

2b if multiple rows, are you using the apply to each to create multiple workflow items?

3. how do you determine the number of items to create?  Is it by the number of rows (2b) or by some other number?

 

DamoBird365_0-1643747042065.png

Sample output:

 

DamoBird365_1-1643747093009.png

 

I have retrieved yellow from my excel table using integer index, which is the value item() from the range().

 

Does this help?

 

Damien

 

Villanous81
Helper I
Helper I

Thanks so much for all the help, see my answers below.

 

1. You have an excel sheet with rows... and access the data with list rows This is correct

2a are you getting multiple rows - or just one row for the value of the prepend? I am getting multiple rows, I am only capturing the first value (using a first expression in a compose step for the Oppty ID) for the purpose of using for the value of the prepend.

2b if multiple rows, are you using the apply to each to create multiple workflow items? Yes I am creating a new workflow item for each row present in the Excel worksheet.

3. how do you determine the number of items to create?  Is it by the number of rows (2b) or by some other number? 2b

 

With the method you are showing, I would need to create a Compose of each item I need out of the Excel worksheet when creating a Workflow item and then call the Compose Output instead of the Excel worksheet value in the Apply to Each step? Would this create an item for each row present in the Excel worksheet?

Hi @Villanous81 

 

You could use a compose but equally, you could just construct the expression using the expression builder for each of the columns.  

 

You’ve already mentioned using first, you can call the first object using integers ?[0]. This is exactly the same concept but using range. The integer for the object, ie row, is populated dynamically and will create an item for each row.

 

Damien

Villanous81
Helper I
Helper I

How would I construct the expression so I could pull the values I need from the Excel worksheet and then subsequentially use those values to create Workflow items with the specific value.

 

Villanous81_0-1644265925700.png

 

When I tried to use the Range method and Compose actions for the values I needed, the first run captures the compose step and then the 2nd does not.

 

Villanous81_1-1644265965136.png

Villanous81_2-1644265973327.png

 

Villanous81_3-1644265984736.png

 

Hi @Villanous81 

 

You need to share a screenshot of the range expression you have used for an apply to each, as well as the expression for BOM.  Remember that BOM with have to use an integer index based on current item.

 

Damien

Villanous81
Helper I
Helper I

My range expression was just like your example, except I wanted my range to start at 1 is all.

 

range(1,length(outputs('List_rows_present_in_a_table')?['body/value'])) 
Villanous81_0-1644366186644.png

 

 This is what the BOM Compose looks like.
Villanous81_1-1644366229059.png

First run of Apply to Each is fine.

Villanous81_2-1644366244097.png

Second Apply to Each there is nothing (there is something in the second row for this column)

Villanous81_3-1644366282252.png

 

 

Hi @Villanous81 

 

This makes sense. Object numbering starts from 0. So I suspect row 2 is being returned in the first response and then null is in the second. If you want to start from 1, use range 0,length for the input but add 1 to item() within the loop before prepending.

 

Damien

Villanous81
Helper I
Helper I

Thanks so much Damien, this was staring me smack dab in the face :). I've learned a bunch of cool new tricks building this flow. I think this finally solves all the problems I was having with all the moving pieces of this flow. Appreciate all your help.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (2,538)