web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SQL query - exclude de...
Microsoft Dynamics CRM (Archived)

SQL query - exclude deleted records?

(0) ShareShare
ReportReport
Posted on by xZara 100

I am new to microsoft crm and I created a query in SQL (that I will use to link to an external database for a report) but deleted records are showing up, at least the records are not visible in CRM but are in the SQL tables - how do I exclude these records? I am querying appointments.

Thanks for your help

 

*This post is locked for comments

I have the same question (0)
  • Grzegorz Kalek Profile Picture
    Grzegorz Kalek 4,210 on at
    Hi, check if DeletionStateCode column is not equal to 2; by the way better way is to use sql filtered views ex. FilteredAccount, FilteredContact, etc.
  • xZara Profile Picture
    xZara 100 on at

    Thanks for the quick reply - I ran the query again but from FilteredAppointment but strangely the records are still showing up so I ran the query from Appointment and included the field DeletionStateCode and its showing up as 0 for the records that should be showing up as deleted.

    Any ideas?

    Thanks for the help!

  • Grzegorz Kalek Profile Picture
    Grzegorz Kalek 4,210 on at
    How do you know these records are deleted ?
  • xZara Profile Picture
    xZara 100 on at

    I know what it is!!

    I had included a field from appointment called new_category and this links to StringMap - what I hadn't done was include stringmap.ObjectTypeCode in my filter critieria and set the objecttypecode = '4201' (there was another list for attributename 'new_category' in stringmap with a different ObjectTypeCode). So the result was matching a new_category value of 2 to two records in stringmap hence why I was seeing 2 records in my query result.

    So it was my query that was the problem not the showing of deleted records. I just thought it was deleted records because they were not coming up in the front end.

    Thanks for your help - at least I now know to use Filtered views also.

    :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans