web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Item lookup in New pro...
Microsoft Dynamics AX (Archived)

Item lookup in New production Order

(0) ShareShare
ReportReport
Posted on by AX 2012 r3 2,426

Good Morning,

I am trying to have a lookup on new production order.

8880.prod1.png

In lookup i am getting all item numbers.

As the need is we need to get lookup of particular items which have bomids (bom created itemids).

So,i found out in BomVersion table itemid field is there.we can use that as lookup.

But i found duplicate values.

So,how can i achieve this.

Please suggest and show some light on this.

Regards.

Have a great day.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Please use the following query in your lookup and field Active set to Yes as a range is required.

    bomVersion bomVersion ;
InventTable inventTable;
    
select firstonly inventTable
join bomVersion
where bomVersion.ItemId == inventTable.itemid
&& bomVersion.Active == true
&& bomVersion.FromDate <= systemdateget ()
&& (!bomVersion.ToDate || bomVersion.ToDate >= systemdateget());
  • AX 2012 r3 Profile Picture
    AX 2012 r3 2,426 on at

    Good Morning Chaitanya,

    Thanks for your reply.

    Where should i write this.

    Please explain.

    Regards.

    have a great day.

  • AX 2012 r3 Profile Picture
    AX 2012 r3 2,426 on at

    Hi sukrut,

    what i am getting in lookup all are inventtable itemids

    What i required is those items which have bom ids attached only those items should come in lookup.

    Regards.

    Have a great day.

  • AX 2012 r3 Profile Picture
    AX 2012 r3 2,426 on at

    I have done this.

    I added group by in my query to itemid.

    i will place complete code.once i complete.

    Regards.

    have a great day

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Form InventItemIdLookupPurchase happens to be the ItemId lookup on form ProdTableCreate. So, in the run method of this form include the following code.I don't have the proper setup to test this, so let me know if anything is missing.

    public void run()
{
    QueryBuildDataSource qbds;
    QueryBuildRange      qbr;
    
    FormStringControl   callerControl   = SysTableLookup::getCallerStringControl(element.args());
    boolean             filterLookup    = false;

    filterLookup = SysTableLookup::filterLookupPreRun(callerControl,
                                                     inventTable_ItemId,
                                                     inventTable_ds);
    
    qbds = inventTable_ds.query().dataSourceTable(tableNum(InventTable)).addDataSource(tableNum(bomVersion));
    
    qbds.addLink(fieldNum(InventTable, Itemid), fieldNum(BomVersion, ItemId));
    qbds.joinMode(JoinMode::InnerJoin);
    //qbds.relations(true);
    
    qbds.addRange(fieldNum(BomVersion, Active)).value(queryValue(NoYes::Yes));
    
    qbds.addRange(fieldnum(BomVersion, FromDate)).value(queryRange(dateNull(), today()));
    qbds.addRange(fieldnum(BomVersion, ToDate)).value(queryRange(today(), dateMax()));
    
    super();

    SysTableLookup::filterLookupPostRun(filterLookup,
                                        callerControl.text(),
                                        inventTable_ItemId,
                                        inventTable_ds);
}
  • AX 2012 r3 Profile Picture
    AX 2012 r3 2,426 on at

    Hi Chaitanya,

    Lookup is coming from the form "InventItemIdLookupByDefaultOrderSetting"

    what i have done is i duplicated that form and added bomversion datasource and changed link type to Exist join.

    So i achieved this.

    I will place this complete with screen shots.

    Thankyou for your reply.

    No one replied.Atleast you replied.i really appreciate.Keep going bro.

    Regards.

    Have a great day.

  • B K Sharma Profile Picture
    B K Sharma 737 on at

    Dear,

    Why are you going to develop a lookup using a duplicate copy of some exiting lookup form, just create a custom lookup by overriding lookup method in your form and also if you know the relation between bom version and item master and just get the proper relation and use group by in code.

    Correct me if i am wrong or understanding you problem in wrong way.

    Thanks

  • AX 2012 r3 Profile Picture
    AX 2012 r3 2,426 on at

    Hi BK Sharma,

    the form which i have mentioned("InventItemIdLookupByDefaultOrderSetting") is the actual form used in lookup given by microsoft.

    The Main Form is ProdTableCreate --> Datasource --> Prodtable --> Fields --> ItemId --> Methods --> Lookup

    (Microsoft has given this method already).

    No need to override again.

    So in this method you can see it is calling class InventLookupItemIdByDefaultOrder.

    In this class they are calling form(InventItemIdLookupByDefaultOrderSetting) as lookup .

    if we do any customization to this form which is used as lookup.then it might effect wherever this form is used as lookup.

    So,i think better create duplicate this form and we can use it for Bom-Inventory item lookup.

    So that is the reason i duplicated that form and added datasource.

    If i am wrong please rectify it.

    Regards.

    Have a great day.

  • B K Sharma Profile Picture
    B K Sharma 737 on at

    Absolutely you are right, I just checked the form and found as you said.

    Regards

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Joris dG Profile Picture

Joris dG 5

#2
Andrew Jones a1x Profile Picture

Andrew Jones a1x 2

#3
Basit Profile Picture

Basit 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans