Prepend name to created value

[Villanous81]

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.

[DamoBird365]

Hi @Villanous81 

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

Output for each loop is as follows:

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

[Villanous81]

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.

[DamoBird365]

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]

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.

[DamoBird365]

@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?

Sample output:

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

Does this help?

Damien

[Villanous81]

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?

[DamoBird365]

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]

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.

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.

[DamoBird365]

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]

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'])) 

 This is what the BOM Compose looks like.

First run of Apply to Each is fine.

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

[DamoBird365 · Accepted solution]

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]

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.