Non-Portable Custom Queries (FetchXML "IN" operator and GUIDs)

(0) Share

Report

Posted on by Community Member

Just noticed that when you use Filter Criteria in a custom entity view and that criteria includes the use of <field> EQ <value, value, value, ...> on a Lookup field, the resulting FetchXML query uses the IN operator.

Sounds reasonable except for the fact that the IN operator includes GUIDs which match those values. That makes it a bit hard for the query to work when the Solution is imported onto another deployment (i.e. those GUIDs don't obviously match anymore).

The workaround I used was to use the "Begins With" operator, which works in my current cases. That query is formed differently and works as expected.

The CRM query editor really shouldn't be using the IN operator for customized Solutions since this will never work and the GUID parameters don't appear to be optional.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Sort by

ScottDurow 23 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

If you want to include the name in the filter rather than the GUID you'll need to use select the name attribute from the related entity in the criteria drop down - this way the fetchxml will join to the related entity and perform the IN on the attribute that you want to use.

It is worth noting as well that you can create records with a specific GUID by importing it - this is good for reference data that you want to be the same across all environments and where you want to use GUIDs in fetchxml queries.

Hope this helps,

Scott

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Scott,

Thank you for the reply. Not quite sure I understand what you are suggesting. I tried this using both a multi-value EQ operator as well as single-valued EQ criteria OR'd together. The resulting FetchXML was similar and included the GUIDs.

Here is the XML for the OR'd example:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="crs_poolmembership">

<attribute name="crs_startdate" />

<attribute name="crs_pool" />

<attribute name="crs_member" />

<attribute name="crs_enddate" />

<order attribute="crs_pool" descending="false" />

<order attribute="crs_member" descending="false" />

<filter type="and">

<filter type="or">

<condition attribute="crs_pool" operator="eq" uiname="LTF" uitype="account" value="{225148CB-4CA5-E311-AAAE-F01FAFD84B49}" />

<condition attribute="crs_pool" operator="eq" uiname="CCCSIF" uitype="account" value="{F9B87AE9-704D-E311-A496-F01FAFD84B49}" />

</filter>

</filter>

<attribute name="crs_poolmembershipid" />

</entity>

</fetch>

In this case, "crs_pool" is a foreign key (lookup), so the query builder is doing the lookup, getting the GUIDs, and storing them in the query.

Dave

Was this reply helpful? Yes No
Linn Zaw Win 3,407 on at

Like
a
(0)

Report
Copy link

Link copied!

Since you're using a reference to the loopup attribute in the condition, it's an unavoidable thing.

As a workaround solution, try to export/import those Master records of custom entity using Solution Extender tool to another deployment so that the records will remains with the same GUID.

solutionextender.codeplex.com

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Linn,

Yes, that would work in the cases where the dataset is actually the same. However, in many situations this is not the case; just consider the difference between a test env and production.

As I mentioned, I do have a workaround, which is to use the "BEGINS WITH" operator to force an actual lookup, but of course this may not work in all cases.

Dave

Was this reply helpful? Yes No
suresh maurya 630 on at

Like
a
(0)

Report
Copy link

Link copied!

You can test FetchXML online http://msxrmtools.com

Was this reply helpful? Yes No