Create Dashboard or Search query for cases with no open tasks

(0) Share

Report

Posted on by Mortoncj

Hi,

I want to be able to retrieve cases which are assigned to specific user and have no uncompleted or open tasks?

Ideally this would be a dashboard but having tried there and the advanced find I think im more likely to have success on advanced find but every time i test a search it doesnt work.

Appreciate any help you can provide.

Thanks,

Mortoncj

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Community Member on at

Like (2)

Report
Copy link

Link copied!

Hi Mortoncj,

I have used below screenshot for query case with not open activity, but it still bring up cases with tasks that open, and cases without any tasks can't be returned.

For example, case-qw1 has two tasks, one is open, another is completed, it has been returned with above query.

Maybe you can do two steps to achieve it indirectly.

(1)Query all cases that are assigned to specific user.

(2)Query all cases that are assigned to specific user and related task is open.

So qw3 and qw4 are cases you want.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (2)

Report
Copy link

Link copied!
Hi Mortoncj,

Or you can download Sql 4ds tool of XrmToolBox to use sql to query it.

SQL 4 CDS · XrmToolBox

SELECT DISTINCT e.title, e.owneridname FROM incident AS e WHERE e.ownerid = '73988725-b8a1-eb11-b1ac-002248045db0' AND (NOT EXISTS (SELECT * FROM incident INNER JOIN task AS aa ON incident.incidentid = aa.regardingobjectid WHERE aa.statecode = 0 AND title = e.title)) ORDER BY e.title ASC;

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Partner,

Please click Yes under "Did this answer your question?" to close this thread if the answer is helpful.

Thanks.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No