Filtered Lookups and Normalization

(0) Share

Report

Posted on by Cyclefitness

I need some advice and clarity. In a standard DB model, you strive for normalization.

In my solution I have a Program and the Program has many sub-programs. From what I can tell, in order for the filtered lookup feature to work, I can't normalize those entities; I would have to put a sub-program field on the Program entity. Then the form on which I want to force a dependent lookup from Sub-Program to Program would be a filtered lookup.

Is there any way to filter a lookup based on another lookup and have the entities for those two lookups be separate rather than one?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

Hi Deb,

You can filter what shows up on a lookup based on a form value using javascript. There's a supported method on the lookup control "addCustomFilter" which lets you add a FetchXml criteria to the existing view. You would want to set up the lookup view so it has all the rest of your criteria, and when the form loads or the field changes, add that criteria to the view.

Hope this helps! I'd appreciate if you would mark this as a Verified answer.

Thanks,

Aiden

Was this reply helpful? Yes No
Cyclefitness on at

Like (0)

Report

Hi Aiden:

Do you have a sample of the JavaScript? I'm not a JS developer, but can modify something that exists.

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report
Hi Deb,

I'm so sorry - I had a great link to share and forgot to put it in my post. [View:http://www.concurrency.com/blog/filter-dynamics-crm-lookup-values-javascript/:750:50]

Here's the code sample from that site:

function preFilterLookup() { Xrm.Page.getControl("new_State").addPreSearch(function () { var stateObject = Xrm.Page.getAttribute("new_State").getValue(); // Get the lookup Object, and extract the Value (ID and Text) if(stateObject != null) { var stateTextValue = stateObject[0].name; var stateID = stateObject[0].id; // Filter the cities by State fetchXml = "<filter type='and'><condition attribute='new_state' operator='eq' uiname='" + stateTextValue + "' uitype='new_State' value='" + stateID + "' /></filter>"; // Apply the filter to the field Xrm.Page.getControl("new_City").addCustomFilter(fetchXml); } }); }

There's more information around that code that's running in the link.

Hope this helps!

Thanks,

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Filtering one lookup field based on the value of another lookup field is an OOB feature. You need to configure this, but don't need scripts.

Program and sub-Program could be one entity or two. Either way, it is the sub-Program entity that needs a lookup field to the parent record.

If these are the same entity then you could configure the second lookup to use a view of Programs that have a parent (lookup field "contains data") as well as filtering for parent based on lookup 1.

You could also filter lookup1 based on lookup2, for users who fill in the sub-program first. This will limit users to the one option that is valid as the parent program of the selected sub-program.

Was this reply helpful? Yes No
Cyclefitness on at

Like (0)

Report

I sure was hoping to be able to accomplish via configuration, but it's where the two lookups will be used that poses the problem:

Entity 1 has a 1:N to Entity 2. The Program is on Entity 1.

Entity 2 has an 1:N to Entity 3. The Sub-Program is on Entity 3.

Was this reply helpful? Yes No
Cyclefitness on at

Like (0)

Report

Aiden:

This is really stretching my boundaries, but I'm pushing through with it. At a very foundational level, I see this requires a FetchXML query. And I see lots of instructions that indicate you must "build" a FetchXML query. And I'm assuming this is possible through Advanced Find? I am at a loss here. I'm thinking SQL and need something like:

Show me all records from Sub-Program where the Parent's Program Name = the Program Name from the Case.

Keeping in mind that I'm executing this query from three levels deep ...

Case --1:N ---> Purchase Order

Purchase Order -- 1:N --- Purchase Order Line Item

Purchase Order Line Item is the entity that will have a lookup to be filtered by "Program Name."

What would something like this look like from the Advanced Find Fetch XML UI?

Was this reply helpful? Yes No