Skip to main content

Notifications

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, ... / After Importing from E...
Finance | Project Operations, Human Resources, ...
Unanswered

After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

(0) ShareShare
ReportReport
Posted on by premK6969 190

Hi there, I got a requirement to export data from excel to AX 2012 Purchase Components.  Navigate to Accounts Payable > All Purchase Orders > Double click on any Open Order > Expand Components tab.   I had written logic using this link. I can able to export all the data but PurchId and Purchase Line Number was not updating because of that the exported data not showing in the Form. Actually i had created a button on the PurchTable form and added my import from Excel logic in Clicked() method.  Some of the records are updating in the backend table but still PurchId and Linenumber were not updating. I am wondering how to map PurchId to components because through PurchId we get PO Header and by header comes Purchase Lines and using Purchase Line comes Purchase COmponents. Then why the export to excel not taking the data as per the PurchId in PurchLine.

Purchase Header >  Purchase Line > Purchase Components

My requirement is: 

1.  Need to import Purchase Components from Excel to PurchLine Form Purchase Components Tab

2. Some of the data exporting but not PurchId and Line Number

3. PurchLineIngredinetRefRecId was not generating don't know why

4. Do we need to write any logic to connect Line Items with PurchId and Line Number and How it can be achieved?

Please help me with that. I just need to import components based on Purch Line Number which was linked with Purch Id.  Plese find the below screenshot for reference. Thanks in Advance.

pastedimage1596099761382v1.pngpastedimage1596099786619v2.png    

Thanks & Regards,

Prem

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,207 Super User 2025 Season 1 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi premK6969,

    The PurchLineIngredient table is not a standard Dynamics 365 object. If you think that the correct values are stored in the table, then review the form extension to see if there is some additional logic which should be met to have a record visible on the form. Have you checked if the reference fields do have the correct values?

  • premK6969 Profile Picture
    premK6969 190 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi Andre, i can able to see the records in backend table ie PurchLineIngredient but i cant able populate the data in the Form. While creating record manullay i can see it in Form and PurchLineRefRecId was also generating. But my  import from excel code populating in Table but not in Form. Please help me with this.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,207 Super User 2025 Season 1 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi Prem,

    Can you update us on your progress? Did you review if you are retrieving the correct purchase order line and if you are using the correct variable?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,207 Super User 2025 Season 1 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi Prem,

    When you open the option 'Use rich formatting', you can use a code applet to paste your coding. It would be then in a better readable format.

    Do you have the PurchId and LineNumber in the Excel file? You are not retrieving those values. I also don't see where you retrieve the purchLine or pLine variables (you seem to use both in your coding).

  • premK6969 Profile Picture
    premK6969 190 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi Andre, Please find the code attached below. There is no customiation for PO Components just we need to export the Components through Excel. The Components Form consists of Fields from PurchLineIngredient Table and i can see some of the data was imported but Purch Id and Purch Line was not populated. One of my collegue suggested to add initFromPurchLine also still not happening.

    //.......... Modified by Prem _ 28/07/2020

    void clicked()

    {

    Dialog _dialog;

    DialogField _file;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

        //Declaring Table Name

    PurchLineIngredient importTable;

    PurchTable purTable;

    InventDim     dimTab;

    PurchLine  pline;

    int  row = 1;

    ItemId   _itemid;

    UnitIDInventory   _unitid;

    ConfigIdStandard configId;

    InventColorTxt inventColorId;

    InventSizeTxt inventSizeId;

    InventBatchId inventBatchId;

    Qty  _cwQty;

    str _cwUnitId;

    Qty  _qty;

    Qty  pureQty;

    SettingCode  _settingTypeCode;

    real   _rate;

    StoneRate  _stoneSettingRate;

    anytype  _cType;

    real  _lineamount;

    PurchId  purchId;

    LineNumber lineNumber;

    _dialog = new Dialog("Please select the file to load");

    _dialog.addText("Select file:");

    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();

    if (_dialog.closedOK())

    {

    info(_file.value() );

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specifies the file path that you want to read

    filename =_file.value(); //ExcelSheet File Name

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error('File cannot be opened');

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number

    cells = worksheet.cells();

    do

    {

           row++;

           _itemid     = cells.item(row, 1).value().bStr();

           _unitid     = cells.item(row, 2).value().bStr();

           configId = cells.item(row, 3).value().bStr();

           inventColorId = any2str(cells.item(row, 4).value().toString());

           inventSizeId = any2str(cells.item(row, 5).value().toString());

           inventBatchId =  any2str(cells.item(row, 6).value().toString());

           _cwQty         = cells.item(row, 7).value().double();

           _cwUnitId  =  any2str(cells.item(row, 8).value().toString());

           _qty         = cells.item(row, 9).value().double();

           pureQty       = cells.item(row, 10).value().double();

           _settingTypeCode     = cells.item(row, 11).value().bStr();

            _rate         = cells.item(row, 12).value().double();

            _stoneSettingRate  =  cells.item(row, 13).value().double();

             _cType     = cells.item(row, 14).value().bStr();

          _lineamount  = cells.item(row, 15).value().double();

            type = cells.item(row+1, 1).value().variantType();

               PurchLineIngredient.initFromPurchLine(PurchLine);

               importTable.initValue();

               importTable.initFromPurchLine(pline);

           importTable.ItemId       = _itemid;

           importTable.UnitId        =  _unitid;

           dimTab.configId      =   configId;

           dimTab.InventColorId =  inventColorId;

           dimTab.InventSizeId  =  inventSizeId;

           dimTab.inventBatchId =  inventBatchId;

           importTable.CWQTY =  _cwQty;

          // importTable.pdsCWUnitId =  _cwUnitId;

           importTable.Qty          = _qty;

           importTable.PureQty    = pureQty;

           importTable.SettingTypeCode = _settingTypeCode;

           importTable.CRate = _rate;

           importTable.StoneSettingRate = _stoneSettingRate;

           importTable.CalcType = _cType;

           importTable.LineAmount   = _lineamount;

           importTable.insert();

           dimTab.insert();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info("Data is Imported");

    }

    }

    //.......... Modified by Prem _ 28/07/2020

    void clicked()

    {

    Dialog _dialog;

    DialogField _file;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

        //Declaring Table Name

    PurchLineIngredient importTable;

    PurchTable purTable;

    InventDim     dimTab;

    PurchLine  pline;

    int  row = 1;

    ItemId   _itemid;

    UnitIDInventory   _unitid;

    ConfigIdStandard configId;

    InventColorTxt inventColorId;

    InventSizeTxt inventSizeId;

    InventBatchId inventBatchId;

    Qty  _cwQty;

    str _cwUnitId;

    Qty  _qty;

    Qty  pureQty;

    SettingCode  _settingTypeCode;

    real   _rate;

    StoneRate  _stoneSettingRate;

    anytype  _cType;

    real  _lineamount;

    PurchId  purchId;

    LineNumber lineNumber;

    _dialog = new Dialog("Please select the file to load");

    _dialog.addText("Select file:");

    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();

    if (_dialog.closedOK())

    {

    info(_file.value() );

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specifies the file path that you want to read

    filename =_file.value(); //ExcelSheet File Name

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error('File cannot be opened');

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number

    cells = worksheet.cells();

    do

    {

           row++;

           _itemid     = cells.item(row, 1).value().bStr();

           _unitid     = cells.item(row, 2).value().bStr();

           configId = cells.item(row, 3).value().bStr();

           inventColorId = any2str(cells.item(row, 4).value().toString());

           inventSizeId = any2str(cells.item(row, 5).value().toString());

           inventBatchId =  any2str(cells.item(row, 6).value().toString());

           _cwQty         = cells.item(row, 7).value().double();

           _cwUnitId  =  any2str(cells.item(row, 8).value().toString());

           _qty         = cells.item(row, 9).value().double();

           pureQty       = cells.item(row, 10).value().double();

           _settingTypeCode     = cells.item(row, 11).value().bStr();

            _rate         = cells.item(row, 12).value().double();

            _stoneSettingRate  =  cells.item(row, 13).value().double();

             _cType     = cells.item(row, 14).value().bStr();

          _lineamount  = cells.item(row, 15).value().double();

            type = cells.item(row+1, 1).value().variantType();

               PurchLineIngredient.initFromPurchLine(PurchLine);

               importTable.initValue();

               importTable.initFromPurchLine(pline);

           importTable.ItemId       = _itemid;

           importTable.UnitId        =  _unitid;

           dimTab.configId      =   configId;

           dimTab.InventColorId =  inventColorId;

           dimTab.InventSizeId  =  inventSizeId;

           dimTab.inventBatchId =  inventBatchId;

           importTable.CWQTY =  _cwQty;

          // importTable.pdsCWUnitId =  _cwUnitId;

           importTable.Qty          = _qty;

           importTable.PureQty    = pureQty;

           importTable.SettingTypeCode = _settingTypeCode;

           importTable.CRate = _rate;

           importTable.StoneSettingRate = _stoneSettingRate;

           importTable.CalcType = _cType;

           importTable.LineAmount   = _lineamount;

           importTable.insert();

           dimTab.insert();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info("Data is Imported");

    }

    }

    //.......... Modified by Prem _ 28/07/2020

    void clicked()

    {

    Dialog _dialog;

    DialogField _file;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

        //Declaring Table Name

    PurchLineIngredient importTable;

    PurchTable purTable;

    InventDim     dimTab;

    PurchLine  pline;

    int  row = 1;

    ItemId   _itemid;

    UnitIDInventory   _unitid;

    ConfigIdStandard configId;

    InventColorTxt inventColorId;

    InventSizeTxt inventSizeId;

    InventBatchId inventBatchId;

    Qty  _cwQty;

    str _cwUnitId;

    Qty  _qty;

    Qty  pureQty;

    SettingCode  _settingTypeCode;

    real   _rate;

    StoneRate  _stoneSettingRate;

    anytype  _cType;

    real  _lineamount;

    PurchId  purchId;

    LineNumber lineNumber;

    _dialog = new Dialog("Please select the file to load");

    _dialog.addText("Select file:");

    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();

    if (_dialog.closedOK())

    {

    info(_file.value() );

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    //specifies the file path that you want to read

    filename =_file.value(); //ExcelSheet File Name

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error('File cannot be opened');

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number

    cells = worksheet.cells();

    do

    {

           row++;

           _itemid     = cells.item(row, 1).value().bStr();

           _unitid     = cells.item(row, 2).value().bStr();

           configId = cells.item(row, 3).value().bStr();

           inventColorId = any2str(cells.item(row, 4).value().toString());

           inventSizeId = any2str(cells.item(row, 5).value().toString());

           inventBatchId =  any2str(cells.item(row, 6).value().toString());

           _cwQty         = cells.item(row, 7).value().double();

           _cwUnitId  =  any2str(cells.item(row, 8).value().toString());

           _qty         = cells.item(row, 9).value().double();

           pureQty       = cells.item(row, 10).value().double();

           _settingTypeCode     = cells.item(row, 11).value().bStr();

            _rate         = cells.item(row, 12).value().double();

            _stoneSettingRate  =  cells.item(row, 13).value().double();

             _cType     = cells.item(row, 14).value().bStr();

          _lineamount  = cells.item(row, 15).value().double();

            type = cells.item(row+1, 1).value().variantType();

               PurchLineIngredient.initFromPurchLine(PurchLine);

               importTable.initValue();

               importTable.initFromPurchLine(pline);

           importTable.ItemId       = _itemid;

           importTable.UnitId        =  _unitid;

           dimTab.configId      =   configId;

           dimTab.InventColorId =  inventColorId;

           dimTab.InventSizeId  =  inventSizeId;

           dimTab.inventBatchId =  inventBatchId;

           importTable.CWQTY =  _cwQty;

          // importTable.pdsCWUnitId =  _cwUnitId;

           importTable.Qty          = _qty;

           importTable.PureQty    = pureQty;

           importTable.SettingTypeCode = _settingTypeCode;

           importTable.CRate = _rate;

           importTable.StoneSettingRate = _stoneSettingRate;

           importTable.CalcType = _cType;

           importTable.LineAmount   = _lineamount;

           importTable.insert();

           dimTab.insert();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    info("Data is Imported");

    }

    }

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,207 Super User 2025 Season 1 on at
    RE: After Importing from Excel PurchId and PurchLineNumber not updating in AX 2012

    Hi Prem,

    Do you have a customization or ISV solution for the PO components? Can you share the development effort you have done so far?

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,207 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans