Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

insert_recordset x++

Posted on by 290

I'v this regular table that I wanted to have static data from other table(s). I'm using run batch framework classes to insert this data. Below are my code/insert method. When I run this... it doesn't insert any data. Please advise on what I might be missing here in terms of coding.  Thanks in advance!

//bulk insertion into mgbShipmentVIEWTable
static void InsertMGBShipmentViewTable2(Args args)
{
    //declare variables
    MGBShipmentTableViewCreate                  mgbShipmentTableViewCreate;
    SalesLine                                   salesLine;
    SalesTable                                  salesTable;
    InventTable                                 inventTable;
    MGBShipmentVIEWTable                        mgbShipmentVIEWTable;
    CustTable                                   custTable;
    DirPartyTable                               dirPartyTable;
    MGB_InventTableProductFamilySubFamReUse     productFamilyView, productFamilyView2, productFamilyView3;
    InventDim                                   inventDim;
    LogisticsPostalAddress                      logisticsPostalAddress;
    HcmWorker                                   hcmWorkerProjectManager;
    DirPersonName                               dirPersonNameProjectManager;
    HcmWorker                                   hcmWorkerRegionalManager;
    DirPersonName                               dirPersonNameRegionalManager;
    SqlDictionary                               sqlDictionary;
    CustInvoiceJour                             custInvoiceJour;
    PurchTable                                  purchTable;
    mgbFirmMaster                               mgbFirmMaster;
    mgbFirmContactMaster                        mgbFirmContactMaster, mgbContactFirmMaster2;
    DimensionAttributeLevelValueView            dimensionAttributeLevelValueView;
    DimensionFinancialTag                       dimensionFinancialTag;
    IntrastatItemCode                           intrastatItemCode;

    delete_from   mgbShipmentVIEWTable;

    insert_recordset   mgbShipmentVIEWTable
    (
       CustAccount,
       mgbOrderCreationDate,
       PURCHORDERFORMNUM,
       SalesId,
       ShippingDateConfirmed,
       ShippingDateRequested,
       SalesQty,
       SalesStatus,
       LineAmount,
       SalesPrice,
       ItemId,
       mgbLineNum,
       mgbProjectedShipDate,
       ShipCarrierAccount,
       ShipCarrierAccountCode,
       DeliveryName,
       mgbLegFinishId,
       mgbPolyId,
       mgbAcrylic,
       mgbArmBoltCapId,
       mgbCasterId,
       mgbContrastA,
       mgbContrastB,
       mgbContrastCover,
       mgbContrastCushion,
       mgbContrastPillow,
       mgbContrastWelts,
       mgbFerrules,
       mgbFringe,
       mgbGrommetId,
       mgbHinge,
       mgbKickplate,
       mgbNails,
       mgbLegFinishId,
       mgbOutsideContrast,
       mgbMetalBaseFinishId,
       mgbLinkedProductionOrder,
       DeliveryPostalAddress,
       ReqGroupId,
       IntraCode,
       ConfigId,
       City,
       State,
       Zipcode,
       CountryRegionId,
       mgbInstallDate,
       mgbProjectName,
       mgbProjectManager,
       mgbSalesAssociate,
       mgbRetailStore,
       mgbRegionalAccountManager,
       mgbDesignFirmId,
       mgbDesignFirmContactId,
       SegmentId,
       SubsegmentId,
       Name,
       mgbProjectManager,
       mgbFirmId,
       mgbFirmDescription,
       mgbFirmContactId,
       ProductFamily,
       SubProductFamily,
       InvoiceAccount,
       InvoiceDate,
       Qty

 )
 select
       CustAccount,
       mgbOrderCreationDate,
       PURCHORDERFORMNUM,
       SalesId,
       ShippingDateConfirmed,
       ShippingDateRequested,
       SalesQty,
       SalesStatus,
       LineAmount,
       SalesPrice,
       ItemId,
       mgbLineNum,
       mgbProjectedShipDate,
       ShipCarrierAccount,
       ShipCarrierAccountCode,
       DeliveryName,
       mgbLegFinishId,
       mgbPolyId,
       mgbAcrylic,
       mgbArmBoltCapId,
       mgbCasterId,
       mgbContrastA,
       mgbContrastB,
       mgbContrastCover,
       mgbContrastCushion,
       mgbContrastPillow,
       mgbContrastWelts,
       mgbFerrules,
       mgbFringe,
       mgbGrommetId,
       mgbHinge,
       mgbKickplate,
       mgbNails,
       mgbLegFinishId,
       mgbOutsideContrast,
       mgbMetalBaseFinishId,
       mgbLinkedProductionOrder,
       DeliveryPostalAddress
    from SalesLine
    join salesLine where salesLine.SalesId == salesTable.SalesId
    outer join firstonly ReqGroupId from inventTable where inventTable.ItemId == salesLine.ItemId
         outer join firstOnly IntraCode from inventTable where inventTable.IntraCode == intrastatItemCode.ItemCodeId
         outer join firstonly ConfigId from inventDim where inventDim.inventDimId == salesLine.InventDimId
         outer join firstonly City, State, ZipCode, CountryRegionId from logisticsPostaladdress where logisticsPostaladdress.RecId == salesLine.DeliveryPostalAddress
         //outer join firstonly itemId from productFamilyView where productFamilyView.ItemId == salesLine.ItemId
         //outer join firstOnly purchTable where purchTable.purchId == salesLine.InventRefid

    join mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from salesTable where salesTable.SalesId == salesLine.SalesId
    join firstonly SegmentId, SubsegmentId from custTable where custTable.AccountNum == salesTable.CustAccount
        outer join firstOnly Name from dirPartyTable where dirPartyTable.RecId == custTable.Party
        outer join firstOnly person from hcmWorkerProjectManager where hcmWorkerProjectManager.RecId == salesTable.mgbProjectManager
        outer join firstonly dirPersonNameProjectManager where dirPersonNameProjectManager.Person == hcmWorkerProjectManager.Person
        //outer join firstOnly person from hcmWorkerRegionalManager where hcmWorkerRegionalManager.RecId == salesTable.mgbProjectManager
        outer join firstonly dirPersonNameRegionalManager where dirPersonNameRegionalManager.Person == hcmWorkerRegionalManager.Person
        outer join firstOnly mgbFirmId, mgbFirmDescription from mgbFirmMaster where mgbFirmMaster.mgbFirmId == salesTable.mgbPurchaseFirmId
       
        outer join firstOnly mgbFirmContactId from mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbPurchaseFirmContactId
        outer join firstOnly mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbDesignFirmContactId
        outer join firstOnly mgbContactFirmMaster2 where mgbContactFirmMaster2.mgbFirmId == salesTable.mgbDesignFirmId

    join ProductFamily, SubProductFamily  from productFamilyView where productFamilyView.ProductFamily == mgbShipmentVIEWTable.ProductFamily
        join firstOnly productFamilyView where productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc
        //join firstOnly productFamilyView2 where productFamilyView2.SubProductFamily == mgbShipmentVIEWTable.SubProductFamily
        join firstOnly productFamilyView3 where productFamilyView3.SubProductFamilyDescription == mgbShipmentVIEWTable.SubProductFamilyDesc

    join InvoiceAccount, InvoiceDate, Qty from CustInvoiceJour where custInvoiceJour.SalesId == salesTable.SalesId;
        


}


  • Lhamo Dolma Profile Picture
    Lhamo Dolma 290 on at
    RE: insert_recordset x++

    Hi all....

    Instead of doing an outer join for all. I had to change them joins to update_recordset and it inserted data into the table. Thanks for all the help.

  • Lhamo Dolma Profile Picture
    Lhamo Dolma 290 on at
    RE: insert_recordset x++

    Hi Sukrut,

    Data is returning when I run it using SQL query but I've turned this SQL query into X++ code and this is where I'm having issues with data not inserting to the table in AOT. I think there might be some issues on the joins that I've in X++.

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: insert_recordset x++

    Hi,

    I corrected your query by commenting the mgb* prefix objects and I am able to get the data. Please use the following query in your insert statement(by adjusting the fieldlist) and let me know if anything is missing.

     select
           CustAccount,
           mgbOrderCreationDate,
           PURCHORDERFORMNUM,
           SalesId,
           ShippingDateConfirmed,
           ShippingDateRequested,
           SalesQty,
           SalesStatus,
           LineAmount,
           SalesPrice,
           ItemId,
           mgbLineNum,
           mgbProjectedShipDate,
           ShipCarrierAccount,
           ShipCarrierAccountCode,
           DeliveryName,
           mgbLegFinishId,
           mgbPolyId,
           mgbAcrylic,
           mgbArmBoltCapId,
           mgbCasterId,
           mgbContrastA,
           mgbContrastB,
           mgbContrastCover,
           mgbContrastCushion,
           mgbContrastPillow,
           mgbContrastWelts,
           mgbFerrules,
           mgbFringe,
           mgbGrommetId,
           mgbHinge,
           mgbKickplate,
           mgbNails,
           mgbLegFinishId,
           mgbOutsideContrast,
           mgbMetalBaseFinishId,
           mgbLinkedProductionOrder,
           DeliveryPostalAddress
        from SalesLine
        join 
        mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from
        salesTable where salesLine.SalesId == salesTable.SalesId // Corrected here
        outer join firstonly IntraCode, ReqGroupId from inventTable where inventTable.ItemId == salesLine.ItemId // Corrected here
            // outer join firstOnly IntraCode from intrastatItemCode where inventTable.IntraCode == intrastatItemCode.ItemCodeId // Removed this
             outer join firstonly ConfigId from inventDim where inventDim.inventDimId == salesLine.InventDimId
             outer join firstonly City, State, ZipCode, CountryRegionId from logisticsPostaladdress where logisticsPostaladdress.RecId == salesLine.DeliveryPostalAddress
             //outer join firstonly itemId, ProductFamily, SubProductFamily from productFamilyView where productFamilyView.ItemId == salesLine.ItemId
             outer join firstOnly purchTable where purchTable.purchId == salesLine.InventRefid
    
        //join mgbInstallDate, mgbProjectName, mgbProjectManager, mgbSalesAssociate, mgbRetailStore, mgbRegionalAccountManager, mgbDesignFirmId, mgbDesignFirmContactId from salesTable where salesTable.SalesId == salesLine.SalesId // Removed this
        join firstonly SegmentId, SubsegmentId from custTable where custTable.AccountNum == salesTable.CustAccount
            outer join firstOnly Name from dirPartyTable where dirPartyTable.RecId == custTable.Party
            outer join firstOnly person from hcmWorkerProjectManager where hcmWorkerProjectManager.RecId == salesTable.mgbProjectManager
            outer join firstonly dirPersonNameProjectManager where dirPersonNameProjectManager.Person == hcmWorkerProjectManager.Person
            outer join firstOnly person from hcmWorkerRegionalManager where hcmWorkerRegionalManager.RecId == salesTable.mgbProjectManager
            outer join firstonly dirPersonNameRegionalManager where dirPersonNameRegionalManager.Person == hcmWorkerRegionalManager.Person
            outer join firstOnly mgbFirmId, mgbFirmDescription from mgbFirmMaster where mgbFirmMaster.mgbFirmId == salesTable.mgbPurchaseFirmId
            outer join firstOnly mgbFirmContactId from mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbPurchaseFirmContactId
            outer join firstOnly mgbFirmContactMaster where mgbFirmContactMaster.mgbFirmContactId == salesTable.mgbDesignFirmContactId
            outer join firstOnly mgbContactFirmMaster2 where mgbContactFirmMaster2.mgbFirmId == salesTable.mgbDesignFirmId
        join mgbShipmentVIEWTable where productFamilyView.ProductFamily == mgbShipmentVIEWTable.ProductFamily // Corrected here
         && productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc
       //     join firstOnly productFamilyView where productFamilyView.ProductFamilyDescription == mgbShipmentVIEWTable.ProductFamilyDesc // Removed this
            join firstOnly productFamilyView2 where productFamilyView2.SubProductFamily == mgbShipmentVIEWTable.SubProductFamily
            join firstOnly productFamilyView3 where productFamilyView3.SubProductFamilyDescription == mgbShipmentVIEWTable.SubProductFamilyDesc
            join InvoiceAccount, InvoiceDate, Qty from CustInvoiceJour where custInvoiceJour.SalesId == salesTable.SalesId;


Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held 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 . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans