Data/Fetch error

(0) Share

Report

Posted on by JFulfordMS

185

I have a salesorder with a lookup to a lead. When viewing the sales order, I can clearly see and click to follow the lookup to the lead. On that lead, I can clearly see and click to follow the lookup to the source campaign.

However, the following fetch XML returns that sames Sales Order and will not recognize that the Source Campaign is actually present. I've also tried using a link entity in FetchXML builder and the link fails as well, even though the lookup is definitely populated.

If you notice, the references to the first and last name of the lead works.

And the lead with the source campaign set.

*This post is locked for comments

All responses (10)

Answers (0)

Martin Donnelly 1,016 on at

Like (0)

Report

RE: Data/Fetch error

I ask this because the latest depiction of the fetch doesn't match the initial question, but is the lead link still an inner join? The depiction tool doesn't seem to reveal that. If it were outer, that would explain how the filter condition was ignored -- but not how the lead1.leadid column got a value.

Also interesting that you would specify that there is no campaign but then ask for its name.

PS- the assertion that you have filtered to the "record in question" is questionable. I would think the record in question is a salesorder (salesorderid) not a customer. This hides any other examples of the same phenomena.
Suggested answer

Kokulan 18,052 on at

Like (0)

Report

RE: Data/Fetch error

Hi

Could you please try the following?

Find these records using Advanced Find - the ones with Source Campaign Null.

Open the record and see if the form has the Source Campaign set. If it does, either that is a custom lookup or lookup to something else or only populated on form load.

You could open the Form Editor from the record and check the field name.

Like I mentioned above, one other possible reason for this is that the lookup is auto populated by some scripts on load of the form? But then if that's the case, it should show unsaved changes at bottom right corner.
Guido Preite 54,073 Moderator on at

Like (0)

Report

RE: Data/Fetch error

very strange indeed. any chance (worth to check) that the Source Campaign field you see in the form is not the standard campaignid field of lead entity?

the sql db you queried is a data export copy of that instance, correct?
JFulfordMS 185 on at

Like (0)

Report

RE: Data/Fetch error

The FetchXML I built is the intended fetch XML. I was intentionally looking for Sales Orders where the Related Lead's Source Campaign is NULL. I was doing this to attempt to identify an attribution issue further down our reporting pipeline.

However, when I started researching this, is when I noticed that the FetchXML is pulling a record that it SHOULD not be pulling.

Here is the fetch XML filtered down to the record in question. As you can see, the link to the lead is successful. That previous snip showed the SQL query for that leadid, pulling the field that shows as populated on the CRM, but null in the database.

This is that lead, with the Source Campaign (campaignid) field populated.
Martin Donnelly 1,016 on at

Like (0)

Report

RE: Data/Fetch error

but you have a condition on the join that the campaignid is null. I would guess you get a shorter list than you are expecting.

did you type this up yourself or get it from advanced find? My guess is that you should remove the null condition to get what you seek.
Guido Preite 54,073 Moderator on at

Like (0)

Report

RE: Data/Fetch error

sorry but that screenshot proves nothing, I can trust you that the record opened in the background is the same specified in your sql query, but at least doing a test with FetchXML Builder I can check (for example) the hierarchy of the xml and check if I missed something.
JFulfordMS 185 on at

Like (0)

Report

RE: Data/Fetch error

To clarify, visually looking at the record in the CRM shows that the Source Campaign field has a value and the lookup is valid. However, the actual data shows that field is being null.
Guido Preite 54,073 Moderator on at

Like (0)

Report

RE: Data/Fetch error

can you post a screenshot of FetchXML Builder with at left the query with the campaignid null and the results on the right showing that the campaignid field of the lead entity has value?
JFulfordMS 185 on at

Like (0)

Report

RE: Data/Fetch error

Correct, and that query is returning a lead that HAS the campaignid that isn't NULL. The fetch is correct, the results are not.
Suggested answer

Guido Preite 54,073 Moderator on at

Like (0)

Report

RE: Data/Fetch error

this condition maybe is wrong

<condition attribute="campaignid" operator="null" />

you are asking for leads that has an empty campaignid, you want lead with this value set

use again FetchXML Builder to create your query and pay attention to the condition