Why "Contains data" could not filter out empty value?

(0) Share

Report

Posted on by Community Member

As we know CRM is using MS SQL Server, in the SQL a blank data could be either NULL or ''(could be called empty). If the user set filter as "contains data" the filter could only filter out NULL data, so that actually empty data remains there. This is not exactly what "contain data" should be!

Is there any way to avoid this issue?

Thanks advance!!!

Kei

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi Kei,

Database changes are not supported, so to remain true to that, you'd need to update through the SDK.

I apologize, I got so focused on fixing the data that I missed out on a solution you suggested in your original post. This could be a little painful to write but it'll do exactly what you're looking for.

There is a way to update the advanced find criteria when you search so empty fields are treated as null. I was part of a team that did something very similar for CRM Intelligent Query (free at: www.CrmIntelligentQuery.com ). For that add-on we intercept the search request on the way to CRM and modify it a little. In this way, we let people you the build in views and query and do left joins, or "doesn't-have" queries.

You would do the same thing, where when there's a condition for "X Does not contain data", you modify the query so it's grouped ("X Does not contain data" OR "X Equals (empty)". This way you'll get back both conditions, but to the user they only pick the normal one.

This is how you'd implement the new plugin:

- Create a new plugin registered on the RetrieveMultiple event, with "none" as the entity. It will run for views, advance finds or even SDK requests for every entity in CRM.

- In the plugin code, make sure the MessageName is RetrieveMultiple, and that the InputParameters contains Query (that will be the query coming that was run)

- The Query property will be either a FetchExpression or QueryExpression depending on where the query was initiated from.

- When you have your query, traverse the nodes to look for the conditions for Contains data and does not contain data, these are the ones you want to update. Modify that particular condition so its grouped as an OR the way we talked about above and you're be good to go.

I really hope this helps, it's a cool thing to implement and would really make searching a lot easier for your users.

Thanks,

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi Aiden,

The reason why I don't to want to fix data directly is that it may cause another potential issue or more issues. And this is unpredictable. I don't want to bring any other risk while fixing this issue.

So I am trying to find a way to avoid this issue.

Thank you for your help and I will mark your answer as verified.

By the way, one more question, if I want to fix data directly I should use SDK programming rather than SQL transaction or stored procedure. The reason is to modify Database is not supported, is this right?

Thank you,

Kei

Was this reply helpful? Yes No
Verified answer

Aiden Kaskela 19,696 on at

Like (0)

Report
RE: Why "Contains data" could not filter out empty value?

Hi Kei,

I was mistaken earlier, an empty text field will be evaluated as having data, but I believe I was correct that CRM will set an empty field to null if its updated to '' or ' '.

To be honest, I would fix the data instead of trying to make CRM work to accommodate the imported errors. I would just bite the bullet and write up a console application to get things straight.

You would need to do it entity by entity, but you could loop through all the properties. Something like this in psuedo-code:

Retrieve all the {entity}

Iterate the attribute on the dynamic entity. If the value of the attribute is String.Empty or whitespace, null the attribute.

If any of the attributes were updated, save the entity

This could take a little while to run but I think it'll be worth it in the long run.

Thanks,

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi Aiden,

What if it is not space, but is '' in the database.('' just means empty string, its size is 0, and could not be equivalent to space (like ' ' or ' ').

Moreover, I don't it's a good way to process data in the database directly.

So, is there any way from CRM system side? like to write JS or plugin filtering out empty string?

I really appreciate your help!

Thank you,

Kei

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi Kai,

This is really a field by field fix. Database updated are unsupported, but if you were to do this through SQL it would need to be one attribute at a time with the format:

update {table} set {column} = ltrim(rtrim({column}))

To do it for contact entity, middlename field, run

update contact set middlename = ltrim(rtrim(middlename))

You could fix the data via the SDK by retrieving all your records, trimming the attributes and saving them back that may take a lot time and impossible for some cases like completed invoices.

Hope this helps. If so, I would really appreciate if you could mark this as the verified answer.

Thanks!

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi

Thank you for your quick reply!

If a record is added by using CRM, everything should be Okay.

But I guess we imported some data from another crm system before.

I used SQL query and found out that SQL Server does have ''(empty data).

So do you know is there any way to deal with this issue in such scenario?

Thank you,

Kei

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

RE: Why "Contains data" could not filter out empty value?

Hi,

This isn't a problem unless your data is wonky in the database. If the contact.middlename field in CRM is blank or NULL, it'll be recognized as not containing data. If you try to set a field as only spaces in CRM, it'll trim the data and be treated as not containing data.

The only way it'll come back as containing data when it doesn't look like it is, is if the field has whitespace characters that we put there directly in SQL (I would guess a data import if I had to). If you create a new record through CRM, I guarantee it will work the way you want it to.

If there is whitespace in the SQL from an import, you need to update each fields with theField = ltrim(rtim(theField)).

Let me know if this doesn't fix it for you. If it is what you're looking for, I'd appreciate it if you could mark this as the verified answer.

Thanks,
Aiden

Was this reply helpful? Yes No

Community site session details

Why "Contains data" could not filter out empty value?

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Why "Contains data" could not filter out empty value?

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates