Accounts without a complete appointment in 90 days

(0) Share

Report

Posted on by Community Member

Is there a way to create a view for all accounts that have not had a completed appointment in the last 90 days? I've looked at the Neglected Accounts report but it doesn't give me what I need. I'm able to create a view of all accounts that HAVE had a completed appointment, but I need to find all that haven't. Any help is appreciated!

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (2)

Verified answer

ThomasN 3,190 on at

Like (0)

Report

Hi GretchenCRM, Thanks for reaching out.

This is an outer join really looking for all the records that do not match the filter. I have done this before, and it may take some tweaking and studying up more on Fetchxml, but it is possible.

Try this link to help.

msdn.microsoft.com/.../dn531006.aspx

Please verify if this answered your question.

Have a wonderful day!

-Tom

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

Hi Gretchen,

That's not possible out of the box in CRM. There are add-ins like CRM Intelligent Query where you can get back accounts that dont have ANY appointments, but you can't put a condition on it for technical reasons (you would essentially need to do two queries to know what to exclude).

If you're on premise you could do this with a SSRS report easily because SQL gives you the tools to query for that, but I'm afraid that's the only way I know if to get that information.

If this helps I'd appreciate if you would mark this as Answering your question.

Thanks,

Aiden

Was this reply helpful? Yes No
Aiden Kaskela 19,696 on at

Like (0)

Report

Hi Tom,

You can do a left-join, but it's not possible to produce a left-join with conditions on the right-side entity.

Thanks,

Aiden

Was this reply helpful? Yes No
Aiden Kaskela 19,696 on at

Like (0)

Report

Tom,

I owe you an apology, after messing around with the Fetch you absolutely can do that. I'll give the Fetch XML in a few minutes, I'm still cleaning it up.

Thanks,

Aiden

Was this reply helpful? Yes No
Verified answer

Aiden Kaskela 19,696 on at

Like (0)

Report
Hi Grethen,

Here is FetchXml which will give you all active accounts which don't have an appointment in the last 90 days (using the Scheduled Start date as the date condition):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="account"> <attribute name="name" /> <attribute name="accountid" /> <order attribute="name" descending="false" /> <filter type="and"> <condition attribute="statecode" operator="eq" value="0" /> <condition entityname='appointment' attribute='activityid' operator='null'/> </filter> <link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer"> <filter type="and"> <condition attribute="scheduledstart" operator="last-x-days" value="90" /> </filter> </link-entity> </entity> </fetch>

If you could mark both Tom and my answers as having Answered your question, we would both appreciate it :)

Thanks,

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Well, well, well....Hi Tom! It's Gretchen @ DDS. Thanks for the help!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Perfect, thanks Aiden!

Was this reply helpful? Yes No
ThomasN 3,190 on at

Like (1)

Report

Hi Gretchen! I knew this request sounded familiar! :)

This is what I call team work. Great Job Aiden putting that Fetch together to save Gretchen time.

Have a great day everyone!

Was this reply helpful? Yes No