Scheduled automation to create tickets from assets, but not create duplicates

[Sara Rowe]

We're using assets to track recurring/annual payments. When the renewal date (asset attribute) is within 90 days, I want to create a ticket in our project. Currently, the date range is set for anything in the next 90 days in the AQL. 

What works: 

Scheduled Trigger - Branch on AQL - Create Issue

The Problem: 

Creating duplicate tickets. I only need one reminder for each payment. 

What I've Tried: 

Various versions of the AQL search (branch and look-up objects), then lookup work issues, then compare lookup size. But the scope is not right/too big and I'm getting an "unable to run the search while custom values were being defined" error. Error and current testing iteration (with manual trigger) in screenshots. I get the same error if I schedule the automation or manual trigger. 

The AQL search: objectTypeId = 932 AND "Status" != "Closed" AND "Next Payment Date" > now() AND "Next Payment Date" < now(90d)

[Mikael Sandberg]

I would change the AQL to only search on objects that are 90 days out when the automation is run, that should prevent any duplications. Something like this:

objectTypeId = 932 AND "Status" != "Closed" AND "Next Payment Date" = now(90d)

[Sara Rowe]

Nice and simple. I'm concerned about assets added in the middle of that window though. Or if the automation fails. Since it's looking only at that one point 90 days from now, it creates potential gaps where some tickets may not be created. 

[Mikael Sandberg]

AQL doesn't have the same option as JQL in automation were you can only include work items that have changed since the last time the rule ran. So if you are trying to cover gaps you have to add an additional lookup to see if a work item is linked to a specific asset before creating a new one.

[Marc - Devoteam]

Hi @Sara Rowe 

To get it straight in my head, you need to find assets with a renewal date 90 days from now.

You are getting the right options, I assume.

What are the details of the branch step in your rule, are you processing each found object?

What i seem to miss is the work item lookup, what is the purpose of finding existing issues?

Also this JQL is failing, as it states that the field Renewal should be of type Assets, but this is not possible, you can't JQL on object key.