Unanswered

Extract Ticket Numbers from an Email Subject

(0) Share

Report

Posted on by PlantBasedCajun

So I am circumventing my current Ticket System for the main organization with a Power Automate ticket system for myself. We use Cherwell.

Basically when I get the Cherwell Email from a new Incident, I want to grab that ticket number as well as my personal ticket number which is the ID field of my excel sheet.

Standard Created Ticket:

Incident ID 115740 My Department Cherwell - Test Request - DL#216 1/29/2021 11:49 AM has been created

So the Cherwell Ticket Number is 115740. I want to place that into my excel spreadsheet. The ID field of my excel spreadsheet is 216 (You can see if after 'DL#').

So I have two COMPOSE Actions:

One to Grab the 115740 so something to look for 'ID ' and grab the 6 digits afterwards

One to look for 'DL#' and I need to get the number between 'DL#' and ' ' so I can get the whole number (It will increase in characters at one point.

I am stumped and have not been able to figure it out. Any suggestions?

Finally step is "Update a Row" based on the 216 number ID and drop the 115740 into another field on that row.

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (2)

v-yiwenxie-msft Microsoft Employee on at

Like (0)

Report

Hi @PlantBasedCajun

It seems that you want to extract some numbers from an email subject and append them into an excel row.

The whole flow is as below:

The detailed steps for ‘Apply_to_each’ are:
Compose: get the 6 digits after ‘Incident ID ’
Compose 2: return the index of ‘DL#’ in email subject
Compose 3: return the substring which starts with ‘DL#’
Compose 4: return the substring between ‘DL#’ and ‘ ’

The functions used are:
substring(items('Apply_to_each')?['subject'],12,6)
indexOf(items('Apply_to_each')?['subject'],'DL#')
substring(items('Apply_to_each')?['subject'],outputs('Compose_2'))
substring(string(split(outputs('Compose_3'),' ')[0]),3)

After compose, here comes the ‘update the row’:

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
PlantBasedCajun 58 on at

Like (0)

Report

So for the GET EMAILS Action, I am using "When a New Email Arrives V3" and they dont have a Value field?

Was this reply helpful? Yes No
PlantBasedCajun 58 on at

Like (0)

Report

So I decided to rebuild it your way... Got an error of Compose_3: Unable to process template language expressions in action 'Compose_3' inputs at line '1' and column '21099': 'The template language function 'substring' parameter is out of range: 'start index' must be non-negative integer and should be less than the length of the string. Please see https://aka.ms/logicexpressions#substring for usage details.'.

Was this reply helpful? Yes No
Verified answer

v-yiwenxie-msft Microsoft Employee on at

Like (0)

Report

Hi @PlantBasedCajun

If you use 'When a new email arrives V3' as the trigger, then there is no need to use 'apply to each', thus not needing value field.

The newly-arrived email which is used in this case:

The whole flow is:

And the functions should be changed a little:

Compose: get the 6 digits after ‘Incident ID ’
Compose 2: return the index of ‘DL#’ in email subject
Compose 3: return the substring which starts with ‘DL#’
Compose 4: return the substring between ‘DL#’ and ‘ ’

The functions used are:
substring(triggerOutputs()?['body/subject'],12,6)
indexOf(triggerOutputs()?['body/subject'],'DL#')
substring(triggerOutputs()?['body/subject'],outputs('Compose_2'))
substring(string(split(outputs('Compose_3'),' ')[0]),3)]

The detailed step of 'update a row' is:
The outputs of all 4 'compose':

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
PlantBasedCajun 58 on at

Like (0)

Report

Thank you for being so detailed. This worked and i can grab these ticket numbers now. Great Job.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Although this is already solved, Thought I would show you another approach, as this seems quite complex to me.

ticketNumber Expression:

split(triggerOutputs()?['body/subject'], ' ')[2]

ID Expression:

substring(split(triggerOutputs()?['body/subject'], ' ')[10], 3)

It can be even more simplified than this.

Was this reply helpful? Yes No
PlantBasedCajun 58 on at

Like (0)

Report

interesting... Consolidated to two compose actions. I will check it out.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (1)

Report

I actually reduced it to one on my side, but kept it as two for simplicity.

Was this reply helpful? Yes No
PlantBasedCajun 58 on at

Like (0)

Report

So Oddly enough, my "Compose_4" broke and I cannot figure out why. Nothing has changed but I thought I would add the Screenshot

Was this reply helpful? Yes No
Verified answer

Paulie78 8,422 Moderator on at

Like (0)

Report

You need to look in the run history at the failure, the format of the subject line must have changed.

Was this reply helpful? Yes No