This is for Alliance and not IFS Cloud or other IFS products?

I think your question is whether when creating a new service order, you can have a popup appear indicating there were multiple occurrences in the last 30 days on this serial number for emergency calls (presumably using priority or some other field).

Alliance does have the possibility to use Process Flows to notify alerts upon certain actions such as this using SQL queries as the source validation or not.  Please take a look at this functionality.

Or you could have a scheduled job for the Process flow which will send out emails, etc. scheduled daily.

Please review the document Process Flow User Guide.pdf for more information on creating and using Process Flows.

Hi Phil,

Many thanks. I was thinking to Process Flow as well.

My difficulty would be to count calls still in activity (service_order) and ones already closed (c_service_order) for same equipment.

Not sure I can create a procedure instead of a single SQL query in “Process Flow Data Query” in order to build a complex query.

Do you have any experience on that ?

Thanks a lot.

Regards

Pascal

Hi Pascal,

While I am not going to do all the development for you, perhaps the below can be used as a guideline for what you want to do:

select item_id, serial_no, callt_id, sum(list1)
from (

select item_id, serial_no, callt_id, count(item_id) list1
from service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id


union all

select item_id, serial_no, callt_id, count(item_id) list1
from c_service_call with(nolock)
where callt_id = 'Support'
group by item_id, serial_no, callt_id


) FinalList

group by item_id, serial_no, callt_id
having sum(list1) > 5

The trick is to do a union all join between two queries; one for active and one for closed tickets and then sum the respective counts to qualify if over the threshold.

I did not do anything to check specific date ranges or other criteria. Please note, I have not tried this within a process flow itself but just pure SQL and it returned the expected results I wanted for my criteria.

There may be and probably are more elegant ways to do this.  If this is not sufficient, I recommend you ask for consulting services to help you develop exactly what you need.

Hi Phil,

Many thanks for your help.

You turned me the light !

I know now what to do.

Your quesry was very helpful.

Regards

pascal

You are welcome, Pascal.

Please note, the change to the original query as I removed the check for qty of 5 for the 'sub-queries’ relying only on the sum used in the main query but I am sure you get the idea.

Good luck!

Phil

Thanks Phil,

I noticed the point and did same from my side.
Thanks again.

Pascal