Hi,
I have two methods. One will update the dimension that has value(in excel) and another method for updating the dimension as empty if the cell in excel sheet has empty values.
BELOW METHOD FOR REMOVING EXISTING DIMENSION VALUE:
public void updateEmptyDimension(ProjTable projTableLocal, Name attributeName)
{
ProjTable projTable = projTableLocal; //Project Record containing Financial Dimension
DimensionSHA1Hash hash; //To store the calculated hash for DimensionAttributeValueSet
DimensionAttribute dimAttr; // Contains the financial dimensions records
DimensionAttributeValue dimAttrValue; // Contains used financial dimension values
DimensionAttributeValueSet dimAttrValueSet; //Contains default dimension records
DimensionAttributeValueSetStorage davss;
DimensionAttributeValueSetItem dimAttrValueSetItem; //Contains individual records for default dimensions
DimensionAttributeSetItem dimAttrSetItem; // Contains the number of dimensions active for a account structure ledger
HashKey valueKeyHashArray[]; //To store the hash key of dimension in question
Map dimAttrRecId, dimAttrStr; //To store the dimension attribute recid and dimension attribute value display value
Set dimAttrValueRecId;
SetEnumerator setEnum;
int dimAttrCount, i;
//Initialize the map to store the backing entity types
dimAttrRecId = new Map(Types::Int64, Types::Integer);
dimAttrValueRecId = new Set(Types::Int64);
dimAttrStr = new Map(Types::Int64, Types::String);
davss = DimensionAttributeValueSetStorage::find(ProjTable::find(projTableLocal.ProjId).DefaultDimension);
//Find all the active dimensions for current ledger except main account and store there
//backing entity type in the map
while select RecId from dimAttr order by Name
where dimAttr.Type != DimensionAttributeType::MainAccount
join RecId from dimAttrSetItem
where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == DimensionCache::getDimensionAttributeSetForLedger()
{
dimAttrCount++;
dimAttrRecId.insert(dimAttr.RecId, dimAttrCount);
}
//initialize hash key array to null
for (i = 1; i<= dimAttrCount; i++)
valueKeyHashArray[i] = emptyGuid();
//Get individual dimension attribute records and display values except worker dimension
//Store them in sets
while select DisplayValue, DimensionAttributeValue from dimAttrValueSetItem
where dimAttrValueSetItem.DimensionAttributeValueSet == projTable.DefaultDimension
join DimensionAttribute, HashKey, RecId from dimAttrValue
where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue
join RecId from dimAttr
where dimAttr.RecId == dimAttrValue.DimensionAttribute
&& dimAttr.Name != attributeName//As we ignore dimension, its hash key remains blank
{
dimAttrValueRecId.add(dimAttrValueSetItem.DimensionAttributeValue);
dimAttrStr.insert(dimAttrValueSetItem.DimensionAttributeValue, dimAttrValueSetItem.DisplayValue);
valueKeyHashArray[dimAttrRecId.lookup(dimAttrValue.DimensionAttribute)] = dimAttrValue.HashKey;
}
//Calculate the hash for the current values
hash = DimensionAttributeValueSetStorage::getHashFromArray(valueKeyHashArray, dimAttrCount);
//Null hash indicates no values exist, which may occur if the user entered an invalid value for one dimension attribute
if (hash == conNull())
{
throw error('Wrong value for Dimensions');
}
// Search for existing value set
dimAttrValueSet = DimensionAttributeValueSet::findByHash(hash);
// This value set does not exist, so it must be persisted
if (!dimAttrValueSet)
{
ttsbegin;
// Insert the value set with appropriate hash
dimAttrValueSet.Hash = hash;
dimAttrValueSet.insert();
// Insert only specified set items use this
setEnum = dimAttrValueRecId.getEnumerator();
while (setEnum.moveNext())
{
dimAttrValueSetItem.clear();
dimAttrValueSetItem.DimensionAttributeValueSet = dimAttrValueSet.RecId;
dimAttrValueSetItem.DimensionAttributeValue = setEnum.current();
dimAttrValueSetItem.DisplayValue = dimAttrStr.lookup(setEnum.current());
dimAttrValueSetItem.insert();
}
ttscommit;
}
ttsBegin;
projTable.selectForUpdate(true);
projTable.DefaultDimension = dimAttrValueSet.RecId;
projTable.update();
ttsCommit;
}
BELOW METHOD TO UPDATE DIMENSION VALUES:
public void startImport(FormButtonControl _formButtonControl,FileName _filename)
{
SysOperationProgress sysprogress = new SysOperationProgress();
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
str file;
int row=8, insert;
DimensionAttributeValue dimAttrDeptValue,dimAtrrDivisionValue,dimAtrrProgramValue,dimAttrProjectValue,dimAtrrVerticalValue;
DimensionAttribute dimAttrDept,dimAtrrDivision,dimAtrrProgram,dimAttrProject,dimAtrrVertical;
DimensionAttributeValueSetStorage davss;
//RecId defaultDimension;
ProjTable projTable;
ProjId projectId;
DimensionAttribute dimAttr;
DimensionAttributeSetItem dimAttrSetItem;
DimensionEnumeration dimensionSetId;
int excludedType = DimensionAttributeType::MainAccount;
FormButtonControl test;
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_EMPTY:
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
file = dialogFilename.value();
workbooks.open(file);
}
catch (Exception::Error)
{
throw error('File cannot be opened.');
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
if(!ValidateFile)
{
ValidateFile = this.validateFile(test,filename);// Validate the file before starting the import process
}
if(ValidateFile) // Import only when validation is successful
{
do
{
row++;
if(row > 1)
{
sysprogress.setText(strFmt('Processing row %1',row));
projectId= COMVariant2Str( cells.item(row, 1).value());
davss = DimensionAttributeValueSetStorage::find(ProjTable::find(projectId).DefaultDimension);
dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
while select Name from dimAttr order by Name where dimAttr.Type != excludedType
join RecId from dimAttrSetItem where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
dimAttrSetItem.DimensionAttributeSet == dimensionSetId
{// Iterate through the map to get the attribute names
dimAttrDept = DimensionAttribute::findByName(dimAttr.Name);
projTable = ProjTable::find(projectId, true);
if(COMVariant2Str( cells.item(row, (headerMap.lookup(dimAttr.Name))).value()) == '')
{
this.updateEmptyDimension(projTable,dimAttr.Name);
}
else
{
dimAttrDeptValue =
DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttrDept, COMVariant2Str( cells.item(row, (headerMap.lookup(dimAttr.Name))).value()), false, true);
davss.addItem(dimAttrDeptValue);
projTable.DefaultDimension = dimAttrSetItem.RecId;
ttsBegin;
projTable.update();
ttsCommit;
}
}
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
info(strFmt("@TEL1604" ,insert));
}
else
{
checkFailed("Validation of the file failed");
}
}
Even after updating i.e removing dimension values if the excel file has empty value. The updated field still shows the previous value instead of empty.
