Filter Product Lookup by Order Name

(0) Share

Report

Posted on by Community Member

Dear All,

I am hoping someone can give me advice about potentially filtering a product lookup list to be limited to those selected in a particular order.

Basically I have a custom entity where the user must select an Order, I am however unable to restrict the possible products that can be selected to those contained within the order.

Is there anyway to achieve this? I have seen a few similar use cases using Javascript, but not explicitly relating to Orders and Products

Any advice would be greatly appreciated.

Thanks,

Alex

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Bojan.borisovski@ho... 480 on at

Like (1)

Report
RE: Filter Product Lookup by Order Name

Hi Alex,

There are many examples on this online, one of them being the following:

http://sumedha8.blogspot.com/2012/05/filtered-lookup-in-crm-2011-dynamic.html

In the link there is function called ContactLookupFilter which you need to adjust to your preference and call it in the onload event of the entity.

Steps you need to do, so you customize it to your preference are the following:

1. Make a fetch xml in advanced find that would do the necessary filter for your lookup.
In your case, should be something like this

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="product">
    <attribute name="name" />
    <attribute name="productnumber" />
    <attribute name="subjectid" />
    <attribute name="statecode" />
    <attribute name="productid" />
    <order attribute="productnumber" descending="false" />
    <link-entity name="salesorderdetail" from="productid" to="productid" alias="ag">
      <link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="ah">
        <filter type="and">
          <condition attribute="name" operator="eq" value="{OrderName}" />
        </filter>
      </link-entity>
    </link-entity>
</entity>
</fetch>

2. From the function in the link, edit the _entityName to be product

3. You should add the fetch xml to the example function, with the parameters you wish to filter on (_fetchxml variable from the link).

4. Edit the _layoutXml, so it shows the columns you need in the view.

5. Edit the following line, so the view is set to your field.

var lookupControl = Xrm.Page.ui.controls.get('{your_product_field}');

6. Publish the onload function in crm on the onload

Hope it gives you some direction.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Bojan, thank you so much for your thorough and comprehensive help!

I will look to implement this as soon as possible and feedback to you, but it looks ideal for my situation.

Was this reply helpful? Yes No
Bojan.borisovski@ho... 480 on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Sure np, let me know how it goes.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Dear Bojan,

Thank you so much for your help so far. I feel I am very close to the solution, unfortunately I have reached a stage where I am simply getting an error message saying "undefined error" and as a result I am going round somewhat in circles.

My modified code is below:

Here are a summary of the modifications I have made:

new_subscriptionorderid = Field that contains the order name I wish to have the product list filtered by

new_service = Field that will be my lookup field.

orderid = variable name change rather than accountid

ordername = variable name change rather than account name

product = entity name change

function SubLookupFilter(orderid, ordername)
{
var _viewId = "{10CACCF3-AC63-46FE-920B-DFEF53BCDE33}";
var _entityName = "product";
var _viewDisplayName = "Products of Order : " + ordername;

var _fetchXml = "<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">" +
"<entity name="product">" +
"<attribute name="name" />" +
"<link-entity name="salesorderdetail" from="productid" to="productid" alias="ag">" +
" <link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="ah">" +
"<filter type='and'>" +
" <condition attribute="name" operator="eq" value='"+ordername+"' />" +
"</filter>" +
"</link-entity>" +
"</link-entity>" +
"</entity>" +
"</fetch>";

var _layoutXml = "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +
"<row name='result' id='OrderProducts'>" +
"<cell name='name' width='250' />" +
"</row>" +
"</grid>";

var lookupControl = Xrm.Page.ui.controls.get('new_service');
lookupControl.addCustomView(_viewId, _entityName, _viewDisplayName, _fetchXml, _layoutXml, true);
}

function Subscription_OnLoad()

{
var attrs = Xrm.Page.data.entity.attributes;
if (attrs.get("new_subscriptionorderid").getValue() != null)
{
var _orderid =attrs.get("new_subscriptionorderid").getValue()[0].id;
var _ordername =attrs.get("new_subscriptionorderid").getValue()[0].name;
SubLookupFilter(_orderid, _ordername);
}
}

I have somewhat come to a halt at this point, obviously I will keep persevering with modifications, however I am not too sure what could be causing this.

Any feedback would be much appreciated.

Thanks,

Alex

Was this reply helpful? Yes No
pmdci 1,032 on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Hi,

Dynamics CRM 2011 and onwards provides the ability to produce filtered lookups. Check out this blog post and let us know if this is of any help.

www.powerobjects.com/.../microsoft-dynamics-crm-2011-filtered-lookups

Regards,

P.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Are these possible passing in a value from the form?

That is why I think I need the first solution.

Was this reply helpful? Yes No
Suggested answer

Bojan.borisovski@ho... 480 on at

Like (0)

Report

RE: Filter Product Lookup by Order Name

Hi Alex,

You have errors in the fetchxml string...

You cannot have " in string concatenation, and then in the string it self...

When you use " for concatenation, inside the string you should use ' sign, or the other way around.

So this:

"<entity name="product">" +

"<attribute name="name" />" +

should be

"<entity name='product'>" +

"<attribute name='name' />" + ....

When writing JavaScript I'd suggest you do it in visual studio, or some other ide supports writing javascript, so you can see your typo errors better.

Try this below:

function SubLookupFilter(orderid, ordername) {

var _viewId = "{10CACCF3-AC63-46FE-920B-DFEF53BCDE33}";

var _entityName = "product";

var _viewDisplayName = "Products of Order : " + ordername;

var _fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">' +

'<entity name="product">' +

'<attribute name="name" />' +

'<link-entity name="salesorderdetail" from="productid" to="productid" alias="ag">' +

'<link-entity name="salesorder" from="salesorderid" to="salesorderid" alias="ah">' +

'<filter type="and">' +

'<condition attribute="salesorderid" operator="eq" value="' + orderid + '"' + ' />' +

'</filter>' +

'</link-entity>' +

'</link-entity>' +

'</entity>' +

'</fetch>';

var _layoutXml = "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +

"<row name='result' id='OrderProducts'>" +

"<cell name='name' width='250' />" +

"</row>" +

"</grid>";

var lookupControl = Xrm.Page.getControl('new_service');

lookupControl.addCustomView(_viewId, _entityName, _viewDisplayName, _fetchXml, _layoutXml, true);

}

function Subscription_OnLoad() {

var attrs = Xrm.Page.data.entity.attributes;

if (attrs.get("new_subscriptionorderid").getValue() != null) {

var _orderid = attrs.get("new_subscriptionorderid").getValue()[0].id;

var _ordername = attrs.get("new_subscriptionorderid").getValue()[0].name;

SubLookupFilter(_orderid, _ordername);

}

}

Also try Xrm.Page.getControl function instead of Xrm.Page.ui.controls.get.

I have also change your query to use the orderid and not the ordername. If you use the ordername there might be many orders with the same name, so you should use the Guid orderid so it gets the unique order.

Let me know how it goes

Was this reply helpful? Yes No