Preventing duplicate items in heldpesk ticket suystem

[Piet-72]

Hi, I'm new here and new to building in PA/Flow..

I have managed to create a SP site with a Tickets list and built the trigger to check a shared mailbox for new items and then Create item in the Ticket list. Additionally it will export the mail and add it as an attachment.

This works great, but when a reply to to the existing issue is received in the shared mailbox, it creates a new ticket. What I have tried to do is create a flow to check for existing tickets with the same Email Subject  using Trim to remove RE: and FW: from the new reply so that it will compare. ( I know I need to add the sender mail as an additional check, but no idea how to yet)

The check for duplicate/existing tickets looks like this:

Initialize variable has the values as follows and is meant to strip the RE and FW from the replies or forwards of the email

Name: title

Type: String

Value: trim(replace(replace(replace(replace(triggerOutputs()?['body/Title'],'FW:',''),'RE:',''), 'Re:', ''), 'Fw:', ''))

The error message as follows: 

There are other items after this but it does not get past this section at the moment.

Any pointers?

[v-bofeng-msft]

Hi @Piet-72 ,

These formulas are not allowed in the Filter query, please add an additional filter array action to do the filtering

trim(replace(replace(replace(replace(triggerOutputs()?['body/Title'],'FW:',''),'RE:',''), 'Re:', ''), 'Fw:', ''))

Best Regards,

Bof

[Piet-72]

Thank you for the response Bof. Much appreciated.

If I understand correctly the formula is inserted into the filter directly from the Initialize Variable.

Would the filter array then sit between the Initialize variable and the Get-Items section? Would you be able to indicate how the formula should look? I would then need to remove the title section from the existing Get-Items section

[NicholasNg]

You might be overcomplicating your flow. In your first flow, do a condition to check if the email is a reply or forward and not create a ticket within that flow.

[Piet-72]

Thank you Nicholas,

I would prefer if it is a single flow instead of the two I created. At the time I did not consider replies coming in but to rather use the "Comments" option in the List to communicate ticket updates etc, but I know I will receive replies and forwards at some point.

If I do a check in the first flow where I create the tickets, and it is a reply or forward, I would still need to run the trim command to check for a match to an existing ticket, correct? This would be required to update an existing ticket with the Update Item action?

First flow is like this:

The filter to check for replies and forwards would then need to sit prior to the Create Item action?

[grantjenkins]

In most ticketing systems they don't include the ticket number within the Subject (or at least not only there). It's probably better to include a unique reference with a predefined prefix (same for all tickets) that you can search on, within the body of the email. If the body of the email doesn't include the prefix then it's a new ticket. If it does include the prefix, then it's a reply to an existing ticket - in which case you would extract out the unique reference (prefix and ticket number) and search for it in your list to be updated.

[NicholasNg]

Agreed with @grantjenkins that approach can work.

On your point of trimming the email, I don't think its that necessary. You could probably have a condition within the trigger to check for RE or FWD in the title. If not you use the condition to end the flow if the subject contains re, fwd etc. 

Edit: Solved: "Does not contain" in email subject - Power Platform Community (microsoft.com) might help too

[Piet-72]

Hi Grant,

Thank you for the feedback and valuable advice.  I'll have to look into that and will try to implement. At the moment we are using the shared mailbox to reply to issues instead of replying via the test ticketing system, which is probably part of our issue. 

[Piet-72]

Hi Nicholas,

Thank you for the valuable feedback. I think your previous comment about overcomplicating it is correct, but with my limited knowledge of PA I think the development for this will take a bit longer and I'll need to spend more time getting my workflows sorted out first.

[NicholasNg]

Hi Piet,

Just use the condition step and terminate step that i showed in the earlier reply immediately after your first flow's trigger. It will check if the email in the shared inbox is a reply/fwd and end the flow before getting to the next steps. You may want to add more rows in the condition to account for Re, RE, FWD, Fwd, etc.