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
It is solved now. i have tried hcmPersonDetails.inser(); instead of hcmPersonDetails.write();
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
check with while loop and if possible paste whole X++ code in this discussion I could you help you on that.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,134 Super User 2024 Season 2
Martin Dráb 229,928 Most Valuable Professional
nmaenpaa 101,156