Flow Designer - Exclude Duplicates and Compare the date/time between 2 fields and get a value

[Santosh37]

Hello,
I am trying to create a Flow Designer to run daily once with the below:

• We have custom table and in that table a record gets created whenever a user is on Record producer and clicks on Knowledge Article that gets displayed based on the service user selects.
• Custom table record consists created time, user details, Service Name and Knowledge article number.
• If same user opens an incident for the same service within 30min from the custom table record created time, then I would like to update an another field which is in the custom table as "Unsuccessful"
  If there is no Incident logged then update the field as "Successful".

Need assistance on below -

1. I would need to compare custom record created time and incident created time and get a value whether the difference between both these fields are less than 30min and use that to update the field.
2. I would also need to exclude the duplicates if multiple records got created in the custom table due to same user viewing the knowledge article multiple time.

Could someone point me the best way to achieve this? Thanks

[DrewW]

To compare dates, you can do something like this.

//r1 and r2 are glide records from diff tables
var t = r1.sys_created_on.getGlideObject();
var n = r2.sys_created_on.getGlideObject();
var diff = t.getNumericValue() - n.getNumericValue();
//Make sure its positive, or adjust the if below to account for it.
if(diff < 0)
   diff = diff * -1
if(diff <= 1800000) { //Number of milliseconds in 30 minutes
   //the date/times where 30 minutes apart.
}

As for removing dups you can use a GlideAggregate and group on the details or when you query for the table with the user and KBA info.
Or you can just query for anything created in the last 30 minutes.  The filter would be

sys_created_onRELATIVEGT@minute@ago@30

this is probably the best way rather than trying to compare a bunch of dates.

[Santosh37]

Thank you for the response, So are you suggesting to create custom action to compare the difference and another custom action using GlideAgregate to exclude the duplicates and then use them in the Flow Designer? 

[DrewW]

I feel like there is some detail missing but I would just suggest using a filter like "sys_created_onRELATIVEGT@minute@ago@30" plus fields like the KBA, user and other fields as needed to find any record created in the last 30 minutes.  Then you can update it if need be or create a new one if not found.  I would do this when the incident is created because I think it will be easier to do at that time.

Just to repeat back what I think you are trying to do.  When a user opens an incident and looks at some KBA's that either they find or are presented to the user you would like to know if that KBA fixed there issue or not.  The way you are determining if it fixed the issue or not is if the user logs another incident with in 30 minutes of the user getting the KBA as a possible solution.

[Santosh37]

Thank you, I will try and update back