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 / Finance | Project Operations, Human Resources, ... / How to add the InventB...
Finance | Project Operations, Human Resources, ...
Answered

How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

(0) ShareShare
ReportReport
Posted on by Sophia.r 235

I have a requirement from a client to add the InventBatch.expDate to the grid. I cannot use a display method as it is very important for them to be able to filter and to search on that field.

I have added a new InventBatch datasource to the form with a join to inventsum and added the expDate to the grid.

But I get an error regarding the executeQuery of the new data source.  I know this form is managed by a number of classes example InventDimCtrl_Frm_OnHand.  But I can not find a  method that I can extend to  modify the query because the Data sources are passed as parameters.

I am going about this wrong or is this even possible in Dynamics 365 Finance and operations.

Has anybody done this before.  I will greatly appreciate any assistance.

Kind regards

I have the same question (0)
  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi Sophia.r,

    I think you need to join InventBatch to InventDim table as it contains Batch number id. Which join type have you used? Also, could you, please, share error details?

  • Sophia.r Profile Picture
    Sophia.r 235 on at

    Hi Sergei Thank you for your prompt reply.

    The error I am getting is "Query missing QueryBuildDataSource for FormDataSource PBFInventBatch"

    I named my datasource PBFInventBatch as the InventBatch already exists on that form.  I used an innerjoin.

    I have also tried the join to InventDim and get the same error.

  • Suggested answer
    Rahul Mohta Profile Picture
    Rahul Mohta 21,032 on at

    if the expiry date is going to be used for filter purpose could use advance filter and join batch table and filter on expiry date in it

    hope it helps

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi Sophia.r,

    I think you need to check the method InventDimCtrl_Frm_OnHand.modifyQueryBasedOnDatasourceName and how grouping is done by InventTable.NameAlias. So you can do similar grouping in your data source be ExpDate by CoC method (?).

    Also, I think you need to join it to InventDim, as InventSum doesn't have a field for batch number.

  • Sophia.r Profile Picture
    Sophia.r 235 on at

    Hi Sergei

    I do not find the InventTable.NameAlias in modifyQueryBasedOnDatasourceName that you are referring to.  The modifyQueryBasedOnDatasourceName method is the one that I originally wanted to extend but I dont know how.  I know it should be something like

    FormDataSource _pbfInventBatch = null

    QueryBuildDataSource qbdsPbfInventBatch;

    if(_pbfInventBatch)

    {

    qbdsPbfInventBatch = query.datSourceName(_pbfInventBatch.name());

    qbdsPbfInventBatch.addGroupByField(fieldNum(InventBatch, expDate));

    }

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi Sophia.r,

    Yes, it should be something like that, but at least "if" statement should be replaced with a check if the batch number is selected to be visible in the grid. Also, _pbfInventBatch is always null your case and you need to make it as a global variable in extension class and pass it to class from form.

    InventDimParm inventDimParmGroupBy = InventDimParm::orParmsAll(dimParmLockedRightClick, dimParmVisibleGrid);

if (inventDimParmGroupBy.InventBatchIdFlag && _pbfInventBatch) //_pbfInventBatch should be passed via parm method from form
{
	qbdsPbfInventBatch = query.dataSourceName(_pbfInventBatch.name());

    qbdsPbfInventBatch.addGroupByField(fieldNum(InventBatch, expDate));
}

  • Sophia.r Profile Picture
    Sophia.r 235 on at

    Hi Sergei I have attached my code below.  But am still running into problems.  It works if I remove the Batch Ind from the dimensions and then put it back. But thereafter if I try to filter on or sort on the expDate then the expdate is empty. It goes through my code 2x, each time adding the groupby expDate successfully on the first run and then on the second time even though it goes through the code (line 29-31) it does not add it.  Also even though the executeQuery has no additional code, if I remove this method the form will not even open. Any further advice??

    [ExtensionOf(ClassStr(InventDimCtrl_Frm_OnHand))]
final public class InventDimCtrl_Frm_OnhandPBF_Extension
{

    public void modifyQueryBasedOnDatasourceName(
        Query          _inventSum_DS_Query,
        str            _inventSum_DS_Name,
        FormDataSource _inventDim_DS)
    {
    
        next modifyQueryBasedOnDatasourceName(_inventSum_DS_Query,
                                                _inventSum_DS_Name,
                                                _inventDim_DS);

        InventDimParm inventDimParmGroupBy = InventDimParm::orParmsAll(dimParmLockedRightClick, dimParmVisibleGrid);

        if (inventDimParmGroupBy.InventBatchIdFlag)
        {
            if (!_inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)))
            {
                _inventSum_DS_Query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(InventBatch), 'PBFInventBatch');
                _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).relations(true);
                _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).joinMode(JoinMode::OuterJoin);

                _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).addGroupByField(fieldNum(InventBatch, expDate));
            }
            else
            {
                FormDataSource PBFInventBatch_ds = _inventDim_DS.formRun().dataSource('PBFInventBatch') as FormDataSource;

                PBFInventBatch_ds.queryBuildDataSource().addGroupByField(fieldNum(InventBatch, expDate));
            }
        }
    }

[ExtensionOf(formstr(InventOnHandItem))]
final public class InventOnhandItemPBF_Extension
{
    [DataSource]
    class PBFInventBatch
    {
        void executeQuery()
        {
            super();
        }
    }

}
}
         

  • Verified answer
    Sophia.r Profile Picture
    Sophia.r 235 on at 

    [ExtensionOf(ClassStr(InventDimCtrl_Frm_OnHand))]
final public class InventDimCtrl_Frm_OnhandPBF_Extension
{

    public void modifyQueryBasedOnDatasourceName(
        Query          _inventSum_DS_Query,
        str            _inventSum_DS_Name,
        FormDataSource _inventDim_DS)
    {
    
        next modifyQueryBasedOnDatasourceName(_inventSum_DS_Query,
                                                _inventSum_DS_Name,
                                                _inventDim_DS);

        InventDimParm inventDimParmGroupBy = InventDimParm::orParmsAll(dimParmLockedRightClick, dimParmVisibleGrid);

        if (inventDimParmGroupBy.InventBatchIdFlag)
        {
            if (!_inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)))
            {
                _inventSum_DS_Query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(InventBatch), 'PBFInventBatch');
            }
            
            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).relations(false);
            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).joinMode(JoinMode::OuterJoin);
            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).clearLinks();
            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).addLink(fieldNum(InventDim, inventBatchId), fieldNum(InventBatch, inventBatchId));
            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).addLink(fieldNum(InventSum, ItemId), fieldNum(InventBatch, itemId), tableStr(InventSum));

            _inventSum_DS_Query.dataSourceTable(tableNum(InventBatch)).addGroupByField(fieldNum(InventBatch, expDate));
        }
    }

}

[ExtensionOf(formstr(InventOnHandItem))]
final public class InventOnhandItemPBF_Extension
{
    [DataSource]
    class PBFInventBatch
    {
        void executeQuery()
        {
            super();
        }
    }

}

    SOLUTION!!  Thanks to a number of people assisting here is the solution.  It looks so simple, I know.

  • Suggested answer
    CU05071209-0 Profile Picture
    CU05071209-0 2 on at
    Hi Sophia.r,
     
    are you  able to solve the issue, I have also same requirement , please guide me for this process.
     
     
    Regards,
    Venkat

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!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 529 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 470

#3
Adis Profile Picture

Adis 270 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans