Filter an array to a string

[vobius]

Hello all,

given I have an array like this:

[
  "[TERMINAL_0600]",
  "Description=here could be any text",
  "[TERMINAL_0601]",
  "Description=here could be any text",
  "[TERMINAL_0602]",
  "Description=here could be any text",
  "[TERMINAL_0603]",
  "Description=here could be any text",
  "[TERMINAL_0604]",
  "Description=here could be any text",
  "[TERMINAL_0605]",
  "Description=here could be any text",
  "[TERMINAL_0606]",
  "Description=here could be any text",
  "[TERMINAL_0607]",
  "Description=here could be any text",
  "[TERMINAL_0608]",
  "Description=here could be any text",
  "[TERMINAL_0609]",
  "Description=here could be any text",
  "[TERMINAL_0610]",
  "Description=here could be any text",
  "[TERMINAL_0611]",
  "Description=here could be any text",
  "[TERMINAL_0612]",
  "Description=here could be any text",
  "[TERMINAL_0613]",
  "Description=here could be any text",
  "[TERMINAL_0614]",
  "Description=here could be any text",
  "[TERMINAL_0615]",
  "Description=here could be any text",
  "[TERMINAL_0616]",
  "Description=here could be any text",
  "[TERMINAL_0620]",
  "Description=here could be any text",
  "[TERMINAL_0621]",
  "Description=here could be any text",
  "[TERMINAL_0622]",
  "Description=here could be any text",
  "[TERMINAL_0623]",
  "Description=here could be any text",
  "[TERMINAL_0624]",
  "Description=here could be any text",
  "[TERMINAL_0625]",
  "Description=here could be any text",
  "[TERMINAL_0626]",
  "Description=here could be any text",
  "[TERMINAL_0627]",
  "Description=here could be any text",
  "[TERMINAL_0628]",
  "Description=here could be any text",
  "[TERMINAL_0629]",
  "Description=here could be any text",
  "[TERMINAL_0630]",
  "Description=here could be any text",
  "[TERMINAL_0631]",
  "Description=here could be any text",
  "[TERMINAL_0632]",
  "Description=here could be any text",
  "[TERMINAL_0633]",
  "Description=here could be any text",
  "[TERMINAL_0634]",
  "Description=here could be any text",
  "[TERMINAL_0635]",
  "Description=here could be any text",
  "[TERMINAL_0636]",
  "Description=here could be any text",
  "[TERMINAL_0640]",
  "Description=here could be any text",
  "[TERMINAL_0641]",
  "Description=here could be any text",
  "[TERMINAL_0642]",
  "Description=here could be any text",
  "[TERMINAL_0643]",
  "Description=here could be any text",
  "[TERMINAL_0644]",
   "Description=here could be any text",
  "[TERMINAL_0645]",
  "Description=here could be any text",
  "[TERMINAL_0646]",
  "Description=here could be any text",
  "[TERMINAL_0647]",
  "Description=here could be any text",
  "[TERMINAL_0648]",
  "Description=here could be any text",
  "[TERMINAL_0649]",
  "Description=here could be any text",
  "[TERMINAL_0650]",
  "Description=here could be any text",
  "[TERMINAL_0651]",
  "Description=here could be any text",
  "[TERMINAL_0652]",
  "Description=here could be any text",
  "[TERMINAL_0653]",
  "Description=here could be any text",
  "[TERMINAL_0654]",
  "Description=here could be any text",
  "[TERMINAL_0655]",
  "Description=here could be any text",
  "[TERMINAL_0656]",
  "Description=here could be any text",
]

I now want to get specific values from that array, which seems very complex to do within power automate. 

In my flow I have two variables which are called DEVID and GRPID and are input by the user as a string(!) 

For this example the DEVID is 06, the GRPID could be 00, 20, 40, 60, ...

Each device contains up to 16 terminals. First terminal of DEVID 06, GRPID 00 is 0600, second is 0601, third is 0602, ....

I now need the 16 Terminals + each description from the above array which contains ALL terminals from all GRPIDs in the following format as a string, so that I can put it into a multiline Sharepoint text field.

For example for GRPID 06 DEVID 00 i would need this as a result: 

Terminal 0600 - descriptiontext

Terminal 0601 - descriptiontext

Terminal 0602 - descriptiontext

....

Terminal 0616 - descriptiontext

For GRPID 06 DEVID 20 I would need this:

Terminal 0620 - descriptiontext

Terminal 0621 - descriptiontext

Terminal 0622 - descriptiontext

....

Terminal 0636 - descriptiontext

Does anyone have any idea how I could achieve this? Any help would be very much appreciated!

[DamoBird365]

Hi @vobius 

I put your sample data into a compose and removed the last , after the final line so that it was an array.  Then i came up with the following:

Which you can call from an apply to each and update your columns with current item as follows:

The output of which is:

How is it done?  There are 17 terminals 0 - 16 inclusive.

Select String:

FROM: range(0, div(length(outputs('ComposeArray')), 2))

MAP: concat(replace(replace(outputs('ComposeArray')?[mul(item(), 2)],'[',''),']',''),'-',split(outputs('ComposeArray')?[add(mul(item(), 2), 1)],'=')?[1])

From: is an range of numbers from 0 to the size of your array divided by 2 i.e. the number of pairs

Map: is concatenating three strings. 

1. the terminal id minus the [ and ], using replace and selecting the object by integer. item() * 2.  Item is 0,1,2,3 etc from the range, the even objects 0,2,4,6 etc.

2. a - hyphen

3. the description minus the description. Using split on a similar expression for the terminal id but adding 1 to the multiplication.  That means we get 0*2 + 1 = 1, 1*2 + 1 =3, then 5,7,9 the odd objects.

Compose Return line is a compose with a single return, hit the enter/return key.

Select Array of terminals:

FROM: range(0,div(length(body('SelectString')),17))

MAP: join(take(skip(body('SelectString'),mul(17,item())),17),outputs('ComposeReturnLine'))

From is a range from 0, to the length of the new array divided by 17, i.e. the number of terminals.

Map is skipping the array starting point based on a multiplication, 17*0=0, 17*1=17 etc, taking the next 17 objects and then joining the newly created array to form a string using the return line.

The output is a new array of 17 lines per object which can be fed to your apply to each and written to your individual items in a list.

Hope that makes sense.  Complex but efficient.

Cheers
Damien

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

[vobius]

Thank you very much. Just tried it, renamed my compose etc. to the same as yours but I now get the error:

 "mul" expects an integer or a decimal number as the first parameter. The specified value is of type "null".

PS: already subscribed to your channel 😉 

EDIT: Another thing I forgot to tell: it's not always 17 terminals in a device. For example I have one device where there are only 5 terminals. 0340,0341,0342,0343,0344 or for example 0320,0324,0326,0327,0328

Any chance there is a possibility to filter this by the GRPID and DEVID the user has to input in PowerApps?

I know, it's a really complex scenario, but the devices we want to monitor only have some text files where all of this data is stored 😞

[DamoBird365]

HI @vobius 

Can you share a screen shot of the error?

Will you know how many terminals will be in a device?  You could make every instance of 17 dynamic.  Have a compose, insert a number (from Power Apps as input) and then pass that to the expressions as outputs('compose').

For example:

range(0,div(length(body('SelectString')),outputs('compose')))

Damien

[vobius]

Hey,

thanks for your fast answer. No, i will never know how many terminals there are per group, unfortunately. This number can be dynamic as there can be new terminals when i refresh the data. I have no counter of connected Terminals in the textfile 😞

Here is the screenshot. Sorry - my language is German, hope that isn't a big problem.

[Paulie78 · Accepted solution]

Hi @vobius 

I took a similar, but slightly different approach to Damien, here is how my flow looks:

https://ibb.co/jGVN9wJ

So the array is first passed into a Select action, this is the code:

From:
range(0,div(length(outputs('Compose')),2))

Terminal:
replace(replace(outputs('Compose')[mul(item(),2)], '[', ''), ']', '')

Description:
replace(outputs('Compose')[add(mul(item(),2),1)], 'Description=', '')

Prefix:
substring(split(outputs('Compose')[mul(item(),2)], '_')[1],0,2)

This produces a new array which looks like this:

[
  {
    "Terminal": "TERMINAL_0600",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0601",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0602",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0603",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0604",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0605",
    "Description": "here could be any text",
    "Prefix": "06"
  },
  {
    "Terminal": "TERMINAL_0606",
    "Description": "here could be any text",
    "Prefix": "06"
  }
]

This new array makes it easy to apply a filter on the Terminal type such as 06 in the next Filter Array step. The code for which is:

@equals(item()['Prefix'], string('06'))

You can enter it in the format above if you switch the filter to advanced mode.

In the final section you simply go into an apply to each loop and combine the value of Terminal and description with the code:

concat(item()['Terminal'], ' - ', item()['Description'])

The filter will ensure you only get terminals with a 06 (or whatever you pass in) prefix.

See how you get on.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[DamoBird365]

Morning @vobius 

The error relates to the range? Which should be from 0 to length of the array divided by 2. Mul is used on item() and 2? It’s hard to see where a null would appear in this. Can you share more screenshots of your setup? 

When it comes to dynamic number of terminals in a group, how do you determine that number? Is it based on 0-19, 20-39, 40-59 etc? Is there a maximum of 20? Do they always run sequentially? 0,1,2,3,4 or could there be 0,4,7,9,16? GRPID is obviously the prefix and Paul shows how you could filter that easily enough, just need to understand the logic of DEVID if it’s 40, would that relate to any ids between 40 & 59?

Finally, you mention that this is for a PowerApp, do you want it to return the result based on a single terminal? 06 20? Or all terminals in the file?

Damien

[vobius]

Hey @DamoBird365 Based on the solution of @Paulie78 I now have the following array.

Regarding the Terminals:
There are managers that each contain up to 16 terminals. Each manager has the DEVID of 00, 20, 40, 60 or 80.

Each terminal than has the DEVID of 01,02, ... 16 or 21,22, ... 36 and so on. They dont necessarily run sequentially, it's possible that one manager only has 5 Terminals which are on non sequential DEVIDs.

The DEVID is put in via a textbox in PowerApps by a User who wants to update all the informations of the specific manager.

In another branch of the flow I update all base data of the manager. In this branch I try to put in all Terminal data, so that I can connect the two Sharepoint Lists in a nice Dashboard via PowerApps where users can also upload photos, attachments, notes and so on.

Thank you very much.

[eliotcole]

OK, you should definitely use either @Paulie78's or @DamoBird365's answers, @vobius  ... but here it's done in with zero Apply to each loops.

Possibly not the record for longest expressions just to avoid looping ... but gimme points for trying, eh, folks?

Two Selects One cCNST

This is technically just a two step method, for that in full, see the last spoiler. However I (like @DamoBird365) chose to make a carriage return constant with cCNST. Plus, I hadn't read Damo's suggestion, but this is very similar ... it just removes the Apply to each. 👍

Select Almost Everything

This Select is the same range as Damo's, I think, I didn't check too closely.

range(
    0, 
    div(
        length(
            outputs('initialArrayCNST')
        ), 
        2
    )
)

However, in the map section in the below spoiler, I've gone a bit more in-depth, with it. That range used by multiplying each number it spits out to pick the next 2 items:

replace(
    replace(
	    join(
			take(
				skip(
					outputs('initialArrayCNST'), 
					mul(
						item(), 
						2
					)
				), 
				2
			), 
			'demonBeGONE'
		), 
	    ']demonBeGONEDescription=',
        ' - '
    ),
    '[TERMINAL_',
    'Terminal '
)

As you can see, it takes a journey with the original array, this spoiler contains a breakdown:

Spoiler

1. skip() - This first skip multiplies the current item() number by two, which provides the place in the original array that you want to be at. So iterations 0,1,2,3 become the numbers 0,2,4,6.
2. take() - This takes two items from the position given in the array from that first skip(). So items 0+1, 2+3, 4+5, 6+7.
3. join() - This joins each two-strong array back up with the (hopefully unique!) text "demonBeGONE" to make a single string.
4. replace() - The first replace then removes the change that demon brought about.
5. replace() - The second replace makes the beginning look nice.

Select yo mama

This beast is a lot, thankfully the From range is relatively easily handled. It simply divides the amount of entries in the previous select by the amount of possible terminals, that being 17 (0-16):

range(
    0, 
    div(
        length(
            body('Select_Almost_Everything')
        ), 
        17
    )
)

And the the devil spawn is in the next spoiler:

Spoiler

concat(
    outputs('cCNST'),
    outputs('cCNST'),
    'Here are all the Terminals for DIVID ',
    slice(
		take(skip(body('Select_Almost_Everything'), mul(item(), 17)), 17)[0], 
		9, 
		11
	),
    ' and GRPID ',
    slice(
		take(skip(body('Select_Almost_Everything'), mul(item(), 17)), 17)[0], 
		11, 
		13
	),
    ':',
    outputs('cCNST'),
	join(
		take(
			skip(
				body('Select_Almost_Everything'), 
				mul(
					item(), 
					17
				)
			), 
			17
		),
		outputs('cCNST')
	)
)

If I break that down (like my mind 😅) over sort-of each concat() step. Note that it uses the same skip/multiply method as before, I won't re-explain that:

Spoiler

1. strings & carriage returns - The first three items are just text, to set up each array item with a descriptor line.
2. slice() - This first slice returns the DIVID number by taking the characters at the point defined.
3. stringy - This puts in a bit more word bases nonsense.
4. slice() - The second slice returns the GRPID number by taking the characters at the point defined.
5. colon & carriage return - A colon then a new line ... for a new world.
6. join() - This final part joins each item inside the take() with a carriage return, placing them on new lines.

You'll also note that I didn't indent the take() functions in the slice() functions, that's because they're the same as the one in the join() at the end. They just use a [0] to take the first item.

Taking It Onward

To call the text, just use:

join(
    body('Select_yo_mama'), 
    outputs('cCNST')
)

Since it begins with two carriage returns, you'll want to either place it next to any other text, or wrap a trim() around it.

Pure Selects On A Stick

Here's that version without the cCNST, so you can remove another precious flow action. 😉

As you can see, I've used the shorter decodeUriComponent(), but uriComponentToString() should also work:

Spoiler

Select Yo Mama's New Map

concat(
    decodeUriComponent('%0D'),
    decodeUriComponent('%0D'),
    'Here are all the Terminals for DIVID ',
    slice(
		take(skip(body('Select_Almost_Everything'), mul(item(), 17)), 17)[0], 
		9, 
		11
	),
    ' and GRPID ',
    slice(
		take(skip(body('Select_Almost_Everything'), mul(item(), 17)), 17)[0], 
		11, 
		13
	),
    ':',
    decodeUriComponent('%0D'),
	join(
		take(
			skip(
				body('Select_Almost_Everything'), 
				mul(
					item(), 
					17
				)
			), 
			17
		),
		decodeUriComponent('%0D')
	)
)

Calling The Text

join(
    body('Select_yo_mama'), 
    decodeUriComponent('%0D')
)

[vobius]

@DamoBird365 

Just to close this topic:
I just created a filter array action, where I filter my DEVID based on this query and it works perfectly how I wanted.

@and(lessOrEquals(int(item()['DeviceID']), add(int(triggerBody()['text_1']), 16)),greaterOrEquals(int(item()['DeviceID']), int(triggerBody()['text_1'])))