web
You’re offline. This is a read only version of the page.
close
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 198

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

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

    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?

  • premK6969 Profile Picture
    premK6969 198 on at

    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... 301,109 Super User 2025 Season 2 on at

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

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

    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?

  • premK6969 Profile Picture
    premK6969 198 on at

    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... 301,109 Super User 2025 Season 2 on at

    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?

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

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

#1
Martin Dráb Profile Picture

Martin Dráb 451 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 428 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans