Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

inserting record in hcmPersonDetails table through x++

Posted on by 491

Hi All,

i am trying to insert record in hcmPersonDetails by getting data from excel. 

 HcmWorker = hcmWorker::findByPersonnelNumber(personalNumber,true);
                if(hcmworker::findByPersonnelNumber(personalNumber))

                {
                   if(hcmWorker.Person)
                    {
                    dirPartyTable = dirPartyTable::find(dirPerson::find(HcmWorker.Person).PartyNumber);
                    if(!dirPartyTable)

                    {
                        dirPartyTable = DirPartyTable::createNew(DirPartyType::Person,firstName);
                    }
                    else
                    {

                        dirPersonName = DirPersonName::find(hcmWorker.Person,true);
                        dirPersonName.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

                        dirPersonName.FirstName = firstName;

                        dirPersonName.LastName  = lastName;
                        dirPersonName.write();
                    }
                    }
ttsBegin;
                    dirPerson = dirPerson::find(DirPartyTable.RecId, true);
                    //dirPerson.MaritalStatus = maritalStatus;
                    dirPerson.write();
                    ttsCommit;
                    
                    ttsBegin;

                    HcmPersonDetails.initValue();
                    HcmPersonDetails.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
                    HcmPersonDetails.ValidFrom = DateTimeUtil::utcNow();
                    HcmPersonDetails.ValidTo = DateTimeUtil::maxValue();
                    HcmPersonDetails.Person            = dirPerson.RecId;
                    hcmPersonDetails.MaritalStatus     = maritalStatus;
                    hcmPersonDetails.NumberOfDependents= noOfDependent;
                    hcmPersonDetails.write();
                    ttsCommit;


it is working fine one i only insert 1 record. but when i try to insert multiple record, it is only inserting the last column of excel.

*This post is locked for comments

  • Verified answer
    xain Profile Picture
    xain 491 on at
    RE: inserting record in hcmPersonDetails table through x++

    It is solved now. i have tried hcmPersonDetails.inser(); instead of hcmPersonDetails.write();

  • xain Profile Picture
    xain 491 on at
    RE: inserting record in hcmPersonDetails table through x++
    Static boolean ImportVendorsDetails()
    {
        #AviFiles
        DirPartyTable               dirPartyTable;
        DirPerson                   dirPerson;
        DirPersonName               dirPersonName;
        VendGroup                   vendGroup;
        LogisticsLocation           logisticsLocation,vendLocation;
        DirOrganization             dirOrganization;
        VendTable                   vendTable, _vendTable, vendorExist;
        DirPartyPostalAddressView   dirPartyPostalAddressView;
        DirPartyContactInfoView     dirPartyContactInfoView;
    
        SysExcelApplication         application;
        SysExcelWorkbooks           workbooks;
        SysExcelWorkbook            workbook;
        SysExcelWorksheets          worksheets;
        SysExcelWorksheet           worksheet;
        SysExcelCells               cells;
        COMVariantType              type;
        COM                         excelCOM;
        int                         row ,progressbarloop = 1,
                                    lastRow, firstRow, totalRows;
    
        Dialog                      dialog;
        FilenameOpen                filename;
        dialogField                 dialogFilename;
    
        SysOperationProgress        progressbar;
    
        Description                 locDescription,contactDiscription;
        LogisticsLocationId         locationId;
        VendAccount                 accountNum;
        Name                        vendorName,firstName,lastName;
        real                         maritalStatus;
        VendGroupId                 vendGroupId;
        LogisticsAddressCity        cityId;
        LogisticsAddressCountyId    CountryId;
        InventLocationId            warehouseId;
        InventSiteId                siteId;
        LogisticsAddressStreet      address;
        CurrencyCode                currencyCode;
        DirParty                    dirPartyClass;
        DirPartyRecId               PartyRecId;
    
        TaxWithholdGroup            taxWithholdGroup;
        PoolId                      poolId;
        HcmWorker                   hcmWorker;
    
        HcmPersonDetails            hcmPersonDetails;
        HcmPersonNumberOfDependents noOfDependent;
        DirPartyRecId               person;
        int                         taxWithHold;
        boolean                     completed, taxWithH;
        str                         locationName;//,personalNumber;
        HcmPersonnelNumberId        personalNumber;
        ValidFromDate               validFrom;
        LogisticsAddressCountryRegionId      countryRegionId;
        LogisticsElectronicAddressMethodType contactType;
        LogisticsElectronicAddressLocator    contactPhone, contactEmail, contactFax, contactTelex, contactURL;
    
        str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
        {
            switch (_cv.variantType())
            {
                case (COMVariantType::VT_BSTR):
                return _cv.bStr();
    
                case (COMVariantType::VT_R4):
                return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
    
                case (COMVariantType::VT_R8):
                return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
    
                case (COMVariantType::VT_DECIMAL):
                return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
    
                case (COMVariantType::VT_DATE):
                return date2str(_cv.date(),123,2,1,2,1,4);
    
                case (COMVariantType::VT_EMPTY):
                return "";
    
                default: throw error(strfmt("@SYS26908", _cv.variantType()));
             }
        }
    
        dialog = new Dialog('Import Vendors');
        dialogFilename = dialog.addField(ExtendedTypeStr("FilenameOpen"));
        dialog.filenameLookupTitle('Import from excel.');
        dialog.caption('Import From Excel');
        dialog.filenameLookupFilter(["@SYS28576","*.xlsx"]);
        dialog.filenameLookupTitle("Select from EXCEL");
        dialogFilename.value(filename);
    
        if(dialog.run())
        {
            filename = dialogFilename.value();
            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();
            excelCOM = cells.comObject();
            excelCOM = excelCOM.SpecialCells(2);
            firstRow = excelCOM.Row();
            excelCOM = excelCOM.SpecialCells(11);
            lastRow = excelCOM.Row();
            totalRows = lastRow - firstRow;
            row = firstRow;
            type = cells.item(row, 1).value().variantType();
    
            progressbar = new SysOperationProgress();
            progressbar.setCaption("Processing.....");
            progressbar.setAnimation(#AviUpdate);
            progressbar.setTotal(totalRows);
    
            try
            {
                //ttsBegin;
                do
                {
                    row++;
                    progressbar.setText(strFmt("Processing line no. %1 of %2", progressbarloop , totalRows));
                    type = cells.item(row+1, 1).value().variantType();
    
                    personalNumber      = COMVariant2Str(cells.item(row, 1).value());
                    firstName      = cells.item(row, 2).value().bStr();
                    lastName   = cells.item(row, 3).value().bStr();
                    maritalStatus = cells.item(row, 4).value().double();
                    noOfDependent   = cells.item(row, 5).value().double();
                  
    
                    HcmWorker = hcmWorker::findByPersonnelNumber(personalNumber,true);
                    if(hcmworker::findByPersonnelNumber(personalNumber))
    
                    {
                       if(hcmWorker.Person)
                        {
                        dirPartyTable = dirPartyTable::find(dirPerson::find(HcmWorker.Person).PartyNumber);
                        if(!dirPartyTable)
    
                        {
                            dirPartyTable = DirPartyTable::createNew(DirPartyType::Person,firstName);
                        }
                        else
                        {
    
                            dirPersonName = DirPersonName::find(hcmWorker.Person,true);
                            dirPersonName.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
    
                            dirPersonName.FirstName = firstName;
    
                            dirPersonName.LastName  = lastName;
                            dirPersonName.write();
                        }
                        }
                        
                        ttsBegin;
                        dirPerson = dirPerson::find(DirPartyTable.RecId, true);
                        //dirPerson.MaritalStatus = maritalStatus;
                        dirPerson.write();
                        ttsCommit;
                        
                        ttsBegin;
    
                        HcmPersonDetails.initValue();
                        HcmPersonDetails.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
                        HcmPersonDetails.ValidFrom = DateTimeUtil::utcNow();
                        HcmPersonDetails.ValidTo = DateTimeUtil::maxValue();
                        HcmPersonDetails.Person            = dirPerson.RecId;
                        hcmPersonDetails.MaritalStatus     = maritalStatus;
                        hcmPersonDetails.NumberOfDependents= noOfDependent;
                        hcmPersonDetails.write();
                        ttsCommit;
    
                      
                    }
                   
                type = cells.item(row + 1, 1).value().variantType();
                progressbar.setCount(progressbarloop,1);
                progressbarloop++;
                }
                while (type != COMVariantType::VT_EMPTY);
                completed = true;
                //ttsCommit;
            }
            catch
            {
                ttsAbort;
                application.quit();
                throw error('Error in import.');
            }
            workbook.close(false, fileName);
            workbooks.close();
            application.quit();
       }
       else
       {
           completed = false;
       }
        info(strFmt("%1 vendors have been imported successfully.", row));
       return completed;
    }

    Hi, 

    it is in the loop, and this is complete code. Thanks

  • veejay Profile Picture
    veejay 55 on at
    RE: inserting record in hcmPersonDetails table through x++

    check with while loop and if possible paste whole X++ code in this discussion I could you help you on that.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans