Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / How to add the InventB...
Finance forum
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

Categories:
Development / Customization / SDK
  • Suggested answer
    CU05071209-0 Profile Picture
    CU05071209-0 2 on at
    How to add the InventBatch.expDate to InventOnHandItem grid D365F&O
    Hi Sophia.r,
     
    are you  able to solve the issue, I have also same requirement , please guide me for this process.
     
     
    Regards,
    Venkat
  • Verified answer
    Sophia.r Profile Picture
    Sophia.r 235 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    [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.

  • Sophia.r Profile Picture
    Sophia.r 235 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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();
        }
    }

}
}
         

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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,083 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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.

  • Suggested answer
    Rahul Mohta Profile Picture
    Rahul Mohta 21,010 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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

  • Sophia.r Profile Picture
    Sophia.r 235 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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.

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: How to add the InventBatch.expDate to InventOnHandItem grid D365F&O

    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?

Helpful resources

Quick Links

First Dynamics 365 Community Call (CRM Edition)

Don't miss the first D365 Community Call on 7/10!

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 286,384 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,485 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans