Expression to Replace character and require length

[RyanTech_Brian]

Hello all, I have been building in Flow and Logic Apps for a few years now and I've always had a very difficult time with expressions because I have zero background in software/coding.  My current flow takes files stored in SharePoint that cannot be renamed and populates a numerical column with a portion of the filename that can be sorted numerically.  My last step is to require 2 characters after I replace "_" with ".".  Since SharePoint cannot sort numerically on a string, I am stripping the letters and underscore out of the filename to populate a number column.  However, 12345.12 will always come before 12345.2, so I need 12345.2 to be 12345.02.  Does that make sense?  My current output is shown below.  Thanks in advance for any help!

[ScottShearer]

@RyanTech_Brian 

Have you considered making the Number Filed a number rather than a text column?

[RyanTech_Brian]

Hi @ScottShearer, that's exactly what I did sir.  The filename cannot be changed at the source, as it comes from a medical imaging device.  So my approach was to create a new, sortable number column with 2 decimal points as shown.  But filename 12345.12 will always numerically come before 12345.2, so I need to .2 to be .02.  Is my thinking correct here?

[David_MA]

Since it is being produced by a medical device, I assume the naming convention is standardized. Is the format you show what is always used. For example, they all begin with EC following by a control number and then a sequence number? If so, you can use the following expression:

replace(replace(first(split(triggerBody()['text'],'.')),'_','.'),'EC','')

What the expression is doing is splitting your input into two parts at the period before the file extension. It then grabs the first part, replaces the underscore with a period and removes EC.

[wskinnermctc]

I am confused about the issue. If you sort Smaller to Larger it is in order just like your display.

If you want it to be the other direction just sort it Larger to Smaller.

This is an interesting question, and I'd like to help. I have set up a list with values already. But after I filled in the values and looking at it, I'm not sure what the sorting problem is?

Could you show a screenshot of the list in the order you would like to have as your final output? That might help me understand. Or add another column beside you Number Field and show how you would like the values to be displayed.

File Name	Number Field Current Output	Desired Output?
EC1234567_1	1234567.1	1234567.01??
EC1234567_12	1234567.12	1234567.12??
EC1234567_2	1234567.2	1234567.02
EC1234567_23	1234567.23	1234567.023??

[David_MA]

I just noticed your requirement about the formatting of the decimal portion of the name. This gets a bit tricker. This is the revised flow. It still assumes the file naming is standardized by the medical device. 

In the first compose action, expression splits the file name on the period. Note, triggerBody()['text'] would be replaced by your dynamic field.

first(split(triggerBody()['text'],'.'))

 The expression in the second compose action splits the result of the first compose on the underscore:

split(outputs('Compose_Split_Period'),'_')

The final compose takes the two parts and puts them back together in the format you need. The first expression here is:

replace(first(outputs('Compose_Split_Underscore')),'EC','')

The second expression after the period is:

formatNumber(int(last(outputs('Compose_Split_Underscore'))),'000')

In the above expression, you can adjust the number of zeros to how many you will need in order to pad it with leading zeros correctly.

Someone who is really good with expressions can probably combine all of these into one, but this the level I am at for combining expressions 🙂

[RyanTech_Brian]

@David_MA thank you, I'm trying that now!  @wskinnermctc here is the issue.  I need to see 1, 2, 3, 12 in that order. It is currently showing as 1, 12, 2, 3.

[David_MA · Accepted solution]

Actually, combining the last compose wasn't too tough 😀

concat(replace(first(outputs('Compose_Split_Underscore')),'EC',''),'.',formatNumber(int(last(outputs('Compose_Split_Underscore'))),'000'))

[wskinnermctc]

Ahhhhh I see, sorry I was thinking about it the wrong way. The medical file output is labeling them with the underscore then number in order, and you want it to be in the underscore order.

I must have some wires crossed in my head thinking it was numbering them like when a file is copied. Disregard, I understand now.

[RyanTech_Brian]

Thank you so much @David_MA for the solution and everyone else for the input!

[wskinnermctc]

Ok @RyanTech_Brian , I was going to try and make an example. But it seems like you already have everything to format the number. 

Whatever method you are formatting the number after the decimal, divide it by 100. So it would be an expression using division which is div(). 

So you want it to be the last file number divided by 100, or even 1000. Not the full file name number, only the last number that is counting up like 1, 2, 3, 12, 13.

I have an expression below that just gets the last number and divides it by 100. 

float(div(float(last(split(first(split(items('Apply_to_each')?['Title'],'.')),'_'))),100))

You can either add that to the file number or some how concat it. But that should get the decimal number you are looking for. 

I made a full expression that removes the 'EC' text and then adds the first file number to the decimal number, but I don't know if that is what you need.

add(float(replace(first(split(items('Apply_to_each')?['Title'],'_')),'EC','')),float(div(float(last(split(first(split(items('Apply_to_each')?['Title'],'.')),'_'))),100)))

Welp nevermind, looks like it was solved earlier.

[David_MA]

I knew someone would be able to do it with one expression!