Active price upload from excel

Hi,

I’m trying to update active price for items through excel, I added one button in inventitemprice form… when I try to update for 1 item its getting updated but for more than 1 item its not updating…

My code is

void clicked()
{
    //super();
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;

    //Table Declarations Starts
    InventItemPrice                 inventprice;
    //Table Declartions Ends
    //fields
    ItemId                          _itemid;
    CostingVersionId                _versionid;
    CostingVersionPriceType         _type;
    Price                           _price;
    CostingVersionId                _version;
    InventDimId                     _invent;
    int                             row;
    ;
    dialog              =   new Dialog("Excel Upoad");
    dialogFilename      =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Sales Price Excel Upload");
    dialogFilename.value(filename);
    if(!dialog.run())
    return;
    filename            =   dialogFilename.value();
    info(filename);
    application         =   SysExcelApplication::construct();
    workbooks           =   application.workbooks();

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }

    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();

    try
    {
        ttsbegin;
        do
        {
            row++;
            _itemid                 =   cells.item(row, 1).value().bStr();
            _type                   =   new DictEnum(enumNum(CostingVersionPriceType)).name2Value(cells.item(row,2).value().bStr()) ;
            _price                  =   cells.item(row, 3).value().double();
            _version                =   cells.item(row, 4).value().bStr();
            //_invent                 =   cells.item(row, 5).value().bStr();
            if(row > 1)
            {
            //update into inventprice table
            while select forupdate Price from inventprice where inventprice.ItemId==_itemid && inventprice.VersionId==_version && inventprice.PriceType==_type
            {
                if(inventprice.RecId)
                {
                     ttsbegin;
                
                     inventprice.Price=_price;
                     inventprice.update();
                     info(strfmt("Price updated successfully",_itemid));
                     ttscommit;
                      ItemPriceActivated_ds.research();
                }

                else
                {
                     warning(strfmt("Price can't be update for (%1)",_itemid));
                }
             }

            }
         type = cells.item(row+1, 1).value().variantType();
         }
             while (type != COMVariantType::VT_EMPTY);
             application.quit();
             ttscommit;
         }
            catch
            {
            Error("Upload Failed");
            }
}

My excel file is

pastedimage1524822509179v1.png

may I know how to update?

It might be a problem with the format of the Item number column. Your code is unable to identify the type of the column.

Try by formatting the column to Text

problem is with Excel format it is unable to read the integer type item number from your excel try this conversion from any type to str

switch(cells.item(row,1).value().variantType())
{
case COMVariantType::VT_BSTR:
accnum = strFmt("%1", cells.item(row,1).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
accnum = strFmt("%1", any2int(cells.item(row,1).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
accnum = strFmt("%1", cells.item(row,1).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
accnum = strFmt("%1", cells.item(row,1).value().uLong());
break;
case COMVariantType::VT_EMPTY:
accnum = ‘’;
break;
default:
throw error(strfmt(‘Unhandled variant type (%1).’, cells.item(row,1).value().variantType()));
}

use your row number and write a string field(accnum) to store the result. hope this will help

Yes, I added and it is now updating all values…

But I’ve one more doubt does price will update without inventdim value or it is necessary to specify inventdimid?

  while select forupdate Price from inventprice where inventprice.ItemId==_itemid && inventprice.VersionId==_version && inventprice.PriceType==_type
            {
                if(inventprice.RecId)
                {
                     ttsbegin;
                     
                     inventprice.Price=_price;
                     inventprice.update();
                     info(strfmt("Price updated successfully",_itemid));
                     ttscommit;
                      ItemPriceActivated_ds.research();
                }

                else
                {
                     warning(strfmt("Price can't be update for (%1)",_itemid));
                }
             }

is this correct way to update or am I missing anything?

You need InventDimId. The prices are specific to site and product dimensions (size, color…)\

Try creating a record manually and see what fields are updated in that table.

Yes, kranthi.

Its not letting me to create a record when dimension isn’t specified…

So how should I update if my excel is having site value?

Use InventDim::findOrCreate() method.

Example:

InventDim inventDim;

inventDim.InventSiteId = site; // this is from excel;

inventPrice.InventDimId = InventDim::findOrCreate(inventDim).InventDimId;

if(row > 1)
            {
            breakpoint;
            select _inventdim;
            _inventdim.InventSiteId=_site;
            inventprice.InventDimId=InventDim::findOrCreate(_inventdim).inventDimId;
            _invent= inventprice.InventDimId;
            //update into inventprice table
            while select forupdate inventprice where inventprice.ItemId==_itemid && inventprice.VersionId==_version && inventprice.InventDimId==_invent && inventprice.PriceType==_type
            {
                if(inventprice.RecId)
                { 
                     ttsbegin;
                     inventprice.PriceUnit=_unit;
                     inventprice.Price=_price;
                     inventprice.update();
                     info(strfmt("Price updated successfully",_itemid));
                     ttscommit;
                     ItemPriceActivated_ds.research();
                }

                else
                {
                     warning(strfmt("Price can't be update for (%1)",_itemid));
                }
             }

            }

I’m getting an error when I upload my excel file as

pastedimage1525078566179v1.png

At what line of code you are getting this error?

Some comments on your code.

select _inventdim; -> **why is this line of code? This may give you a wrong inventDimId.**

if(inventprice.RecId) 
 {
**You don't need this check in a while select.**

ItemPriceActivated_ds.research();
**You don't have to call research after updating each record. You can call it end of your code.**

info(strfmt("Price updated successfully",_itemid));
**You are not using %. See this, [msdn.microsoft.com/.../aa589727.aspx](https://msdn.microsoft.com/en-us/library/aa589727.aspx)**
 try
    {
        ttsbegin;
        do
        {
            row++;
            _itemid           =   COMVariant2Str(cells.item(row, 1).value());
            _type             =   new DictEnum(enumNum(CostingVersionPriceType)).name2Value(cells.item(row,2).value().bStr()) ;
            _price            =   cells.item(row, 3).value().double();
            _unit             =   cells.item(row, 4).value().double();
           _version          =   COMVariant2Str(cells.item(row, 6).value());
            _site             =   COMVariant2Str(cells.item(row, 5).value());
            if(row > 1)
            {
            breakpoint;
            select _inventdim;
            _inventdim.InventSiteId=_site;
            inventprice.InventDimId=InventDim::findOrCreate(_inventdim).inventDimId;
            _invent= inventprice.InventDimId;
            current=InventItemPrice::findCurrent(_itemid,_type,_invent,_activationDate,_site,_costingType,false);
            //update into inventprice table

            while select forupdate inventprice where inventprice.ItemId==_itemid && inventprice.VersionId==_version && inventprice.InventDimId==_invent
            {
                if(inventprice.RecId && inventprice.PriceType==CostingVersionPriceType::Cost)
                {
                     ttsbegin;
                     inventprice.PriceUnit=_unit;
                     inventprice.Price=_price;
                     inventprice.update();
                         select forupdate inm where inm.ItemId==inventprice.ItemId && inm.ModuleType==ModuleInventPurchSales::Invent;
                         ttsbegin;
                         inm.Price=_price;
                         inm.PriceUnit=_unit;
                         inm.update(true);
                         ttscommit;
                     info(strfmt("Price updated successfully for %1",inventprice.ItemId));
                     ttscommit;
                    
                }
                 
                else
                {
                     warning(strfmt("Price can't be update for (%1)",inventprice.ItemId));
                }
             }

            }
         type = cells

I’ve modified some lines and its updating price now kranthi…

I declared inventdim table and

select _inventdim; is to select tat table.

I've one more doubt how to find the current active version, so that I can update the price for that version alone when we upload the excel file

You don’t need that select.

Look at \Data Dictionary\Tables\InventItemPrice\Methods\findCurrent

I’m trying to insert items in active price form through excel, here is my code

do
        {
            row++;
            _itemid           =   COMVariant2Str(cells.item(row, 1).value());
            _price            =   cells.item(row, 2).value().double();
            _unit             =   cells.item(row, 3).value().double();
            _site             =   COMVariant2Str(cells.item(row, 4).value());
            if(row > 1)
            {
            breakpoint;
            select Costingversionid from inp;
            _versionid=inp.Costingversionid;
            
                inventprice.ItemId=_itemid;
                inventprice.VersionId=_versionid;
                inventprice.PriceType=CostingVersionPriceType::Sales;
                _inventdim.InventSiteId=_site;
                inventprice.InventDimId=InventDim::findOrCreate(_inventdim).inventDimId;
                inventprice.Price=_price;
                inventprice.PriceUnit=_unit;
                inventprice.insert();
             
                info(strfmt("Items inserted successfully for %1",inventprice.ItemId));

            ItemPriceActivated_ds.research();
            }
         type = cells.item(row+1, 1).value().variantType();
         }
             while (type != COMVariantType::VT_EMPTY);
             application.quit();
             ttscommit;
         }

Here is my excel file

pastedimage1525252538966v1.png

When I debug itemid value is showing as 102, may i know the issue?

yes, I’ve done that it is some formating mistake in my excel file.

Now when I import my excel file items got inserted in inventitemprice form but the price isn’t getting updated in item form…Is there any method need to be called?

do
    {
        row++;
        _itemid           =   COMVariant2Str(cells.item(row, 1).value());
        _price            =   cells.item(row, 2).value().double();
        _unit             =   cells.item(row, 3).value().double();
        _site             =   COMVariant2Str(cells.item(row, 4).value());

        select Costingversionid from inp;
        _versionid=inp.Costingversionid;

        if(row > 1)
        {
         breakpoint;
         select firstonly inventprice where inventprice.ItemId==_itemid && inventprice.VersionId==_versionid;
        
            inventprice.ItemId=_itemid;
            inventprice.VersionId=_versionid;
            inventprice.PriceType=CostingVersionPriceType::Sales;
            _inventdim.InventSiteId=_site;
            inventprice.InventDimId=InventDim::findOrCreate(_inventdim).inventDimId;
            inventprice.Price=_price;
            inventprice.PriceUnit=_unit;
            inventprice.insert();
            info(strfmt("Items inserted successfully for %1",inventprice.ItemId));
        
        }
        type = cells.item(row+1, 1).value().variantType();
        }while (type != COMVariantType::VT_EMPTY);
        application.quit();
        ttscommit;
        }

this is my insert code, how can I update the sales price in item master form for the item?

You have to update the price on InventTableModule table for record with module type sales.

Should I do manually, kranthi?

Write the code to update the InventTableModule record after inserting into Price table.

 select forupdate inm where inm.ItemId==inventprice.ItemId && inm.ModuleType==ModuleInventPurchSales::Sales;
            ttsbegin;
            inm.Price=_price;
            inm.PriceUnit=_unit;
            inm.update(true);
            ttscommit;

Is this right?

Looks OK.