Archive
Query using ‘Not In’ filter – Advanced Find – Dynamics 365 – V 9.0
In the previous versions of CRM, there is no option in Advanced Find, to query all ‘Case’ records that do not have a related task, as we don’t have option to execute ‘Outer Join’ (i.e., Not in).
In V9.0 release, a new ‘Does not contain data‘ option added to ‘Advanced Find’ to filter ‘Not In’ records.
Let’s get started to fetch all the ‘Accounts’ which does not have any associated ‘Contacts’
- Open ‘Advanced Find’
- Select ‘Account’ entity and from the Related entities choose OOB ‘Contacts(Company Name)’ relationship.
- Choose ‘Does Not Contain Data’ from filter option.
- Fetch XML will be as below
🙂
SQL Server Error While Exporting Advanced Find Records To Excel
Few Users reported that they were getting “SQL Server Error” while trying to export the Advanced Find records to Excel.
SQL Server Error while exporting records
Reason :
- The filter they used in Advanced Find resulted more than 10000 records.
- CRM has OOB limit of 10000 records to export.
- If your CRM instance is on premise, you can check the limit by querying OrganizationName_MSCRM database.
- Below is the query
- SELECT MaxRecordsForExportToExcel FROM OrganizationBase
Fix :
- You can increase the value of “MaxRecordsForExportToExcel”.
Supported Way
- Updating the MaxRecordsForExportToExcel using CRM SDK call is Supported.
- This will work for both on premise and online instances
Organization organization = new Organization();
organization.Id = {Oragnization GUId}; // Get Org GUID from MSCRM_Config DB’s Organization table.
organization.MaxRecordsForExportToExcel = 300000; // Set desired value
service.Update(organization);
Unsupported Way
- As you aware its strictly not recommended to update CRM database directly and also this approach only works for on premise.
- Run below query by connecting to your OrganizationName_MSCRM Database.
UPDATE OrganizationBase
SET
MaxRecordsForExportToExcel = 100000
🙂
Rajeev Pentyala - Microsoft Power Platform 