Excel Add-in: Unable to publish or view data

Recently, I faced an issue when all of sudden our users are not able to upload journal through excel add-in. Following error message was shown in the excel FinOps component while opening any journal through excel.

Error message:

1. An error occurred while retrieving the row count of entity LedgerJournalLine. Error details: User is not authorized to read view LedgerJournalLineEntity. Request denied.
2. An error occurred while retrieving the row count of entity LedgerJournalHeader. Error details: User is not authorized to read view LedgerJournalHeaderEntity. Request denied.
3. A row created in data set LedgerJournalLines was not published. Error message: ‘Could not schedule SetValue for property TransDate: update access to property not granted’

We have not change anything and were surprise with these error, as before EID holiday the excel functionality was working flawlessly.

To resolve the issue, we made a custom role and give permission on given entity which are listed in the error message as shown below,

 
 

We give the permission on the fields of each entity as assign the role to users which were facing the issue. This resolve the issue. However, why this issue appear all of sudden is still under investigation.

Invoice not creating through Vendor Collaboration portal

Recently, during COVID 19 lockdowns, we enable vendor collaboration portal for our vendor so that they can submit the invoice, quotation, etc through portal.

During testing, we come to know that application get stuck when we create an invoice using the following screen

because after filling the new invoice from a purchase order, the application stuck and after some minutes following message was shown

After doing a lot of research, we came to know that it is a missing configuration of the Project in system configuration. Ironically, we are not using the project module at all. Once we enable the project configuration, users able to create invoices using vendor collaboration.

Hope that this will help other people who might face this issue in future

Query to Find specific attribute from DimensionAttributeValueCombination

SELECT * FROM DIMENSIONATTRIBUTEVALUECOMBINATION
WHERE RECID = 5637145399
SELECT DBO.GETVALUEFROMDEFAULTDIMENSION(5637148356, ‘BUSINESSUNIT’)
SELECT DBO.GETVALUEFROMLEDGERDIMENSION(5637145399, ‘MAINACCOUNT’)
GO
ALTER FUNCTION GETVALUEFROMLEDGERDIMENSION(
@DEFAULTDIMENSION BIGINT,
@BACKINGENTITYTYPE VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @DIMENSIONVALUE VARCHAR(50);

SELECT @DIMENSIONVALUE = DISPLAYVALUE
FROM DIMENSIONATTRIBUTE DA
JOIN DIMENSIONATTRIBUTEVALUE DAV ON DA.RECID = DAV.DIMENSIONATTRIBUTE
JOIN DIMENSIONATTRIBUTELEVELVALUE DALV ON DALV.DIMENSIONATTRIBUTEVALUE = DAV.RECID
JOIN DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVG ON DAVG.DIMENSIONATTRIBUTEVALUEGROUP = DALV.DIMENSIONATTRIBUTEVALUEGROUP
WHERE DA.NAME = @BACKINGENTITYTYPE AND DAVG.RECID = @DEFAULTDIMENSION

RETURN(@DIMENSIONVALUE);
END;
GO
ALTER FUNCTION GETVALUEFROMDEFAULTDIMENSION(
@DEFAULTDIMENSION BIGINT,
@BACKINGENTITYTYPE VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @DIMENSIONVALUE VARCHAR(50);

SELECT @DIMENSIONVALUE = DAVSI.DISPLAYVALUE
FROM DIMENSIONATTRIBUTE DA
JOIN DIMENSIONATTRIBUTEVALUE DAV ON DAV.DIMENSIONATTRIBUTE = DA.RECID
JOIN DIMENSIONATTRIBUTEVALUESETITEM DAVSI ON DAVSI.DIMENSIONATTRIBUTEVALUE = DAV.RECID
WHERE DA.NAME = @BACKINGENTITYTYPE AND DAVSI.DIMENSIONATTRIBUTEVALUESET= @DEFAULTDIMENSION

RETURN(@DIMENSIONVALUE);
END;

Error while opening Custom Entity Wizard

Recently, we moved production database on the test server for some data verification. However, after restoration of the database, we face following error when opening Custom Entity wizard in the Data import export module

 

Error Message (03:07:12 pm) Cannot select a record in Model element (DMFModelElement). ModelElementType: 0.
The SQL database has issued an error.
Info Message (03:07:12 pm) SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name ‘XXX.dbo.SYSMODELELEMENT’.
Info Message (03:07:12 pm) SQL statement: SELECT T1.NAME,T1.ELEMENTTYPE,T1.RECID FROM DMFMODELELEMENT T1 WHERE ((ELEMENTTYPE=?) AND NOT ((NAME LIKE ? ESCAPE ‘\’ ))) ORDER BY T1.RECID
Error Message (03:07:12 pm) Cannot create a record in SQL statement trace log (SysTraceTableSQL). Trace type: Error, Aamir.
The record already exists.

The fixed of this issue is to run a DB synchronization. Hope that it also work for you.

For getting Dimension attribute from default dimension

SELECT BU.NAME, PT.PURCHID, PT.DeliveryDate, PT.CREATEDDATETIME, PT.AccountingDate, CASE PT.PURCHSTATUS WHEN 1 THEN ‘OPEN ORDER’ WHEN 2 THEN ‘RECEIVED’ WHEN 3 THEN ‘INVOICED’ WHEN 4 THEN ‘CANCELED’ END AS [PO STATUS]
FROM PURCHTABLE PT
right join (
select DAVSI.DISPLAYVALUE,OMB.NAME, DAVSI.DIMENSIONATTRIBUTEVALUESET from DIMENSIONATTRIBUTE DA
join DimensionAttributeValue DAV on DAV.DIMENSIONATTRIBUTE = DA.RECID
join DimensionAttributeValueSetItem DAVSI on DAVSI.DIMENSIONATTRIBUTEVALUE = DAV.RECID
join DimAttributeOMBusinessUnit OMB on OMB.VALUE = DAVSI.DISPLAYVALUE and DA.BACKINGENTITYTYPE = 11763) Bu on BU.DIMENSIONATTRIBUTEVALUESET = PT.DEFAULTDIMENSION
WHERE PT.CREATEDDATETIME >= ‘2017-07-01’ AND PT.CREATEDDATETIME <= ‘2018-06-30’ and PT.PURCHSTATUS not in (3, 4)
order by BU.DISPLAYVALUE

 

alter FUNCTION GetValueFromDefaultDimension(alter FUNCTION GetValueFromDefaultDimension(@DEFAULTDIMENSION bigint,@BackIngEntityType varchar(50)  )  RETURNS varcharAS  BEGIN       DECLARE @DimensionValue varchar(50);
select @DimensionValue  = DAVSI.DISPLAYVALUE from DIMENSIONATTRIBUTE DA join DimensionAttributeValue DAV on DAV.DIMENSIONATTRIBUTE = DA.RECID join DimensionAttributeValueSetItem DAVSI on DAVSI.DIMENSIONATTRIBUTEVALUE = DAV.RECID where Da.NAME = @BackIngEntityType           RETURN(@DimensionValue);  END;

 

Create FUNCTION GetValueFromLedgerDimension(Create FUNCTION GetValueFromLedgerDimension(@DEFAULTDIMENSION bigint,@BackIngEntityType varchar(50)  )  RETURNS varchar(50)AS  BEGIN       DECLARE @DimensionValue varchar(50);
Select @DimensionValue  = DISPLAYVALUE from DIMENSIONATTRIBUTE DA join DIMENSIONATTRIBUTEVALUE DAV on DA.RECID = DAV.DIMENSIONATTRIBUTE  join DIMENSIONATTRIBUTELEVELVALUE DALV on DALV.DIMENSIONATTRIBUTEVALUE = DAv.RECID join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVG on DAVG.DIMENSIONATTRIBUTEVALUEGROUP = DALV.DIMENSIONATTRIBUTEVALUEGROUP where Da.NAME = @BackIngEntityType           RETURN(@DimensionValue);  END;  GO

Inventory closing or recalculation has reached the maximum number of iterations.

During inventory recalculation, I get this error. After doing a little investigation, the resolution of this issue is to increase the number in the “Maximum Throughput” field as shown below.

 

How to approve workflow by Code

Following code can approve the workflow through code.

 

void Clicked()
{
WorkflowWorkItemTable WorkflowWorkItemTable;

while select WorkflowWorkItemTable
where
WorkflowWorkItemTable.UserId == curuserId()
&& workflowWorkItemTable.Type == WorkflowWorkItemType::WorkItem
&& workflowWorkItemTable.Status == WorkflowWorkItemStatus::Pending
&& WorkflowWorkItemTable.RefTableId == tableNum(TableName of workflow)
{
WorkflowWorkItemActionManager::dispatchWorkItemAction(
WorkflowWorkItemTable,
“Auto Approve by system operation”, // comment
curuserId(), WorkflowWorkItemActionType::Approved,
menuitemActionStr( ABC), // << menu item action approval
false); //is not web Menu Item
}

}

Importing Employee data from CVS file.

Following Job shows you structure which can be helpful in loading data in employee table.

static void uploadWorker(Args _args)
{
CommaTextIo file;
container record;
Description PersonnelNumber,EmploymentType, FirstName, LastName, middleName, Name, Gender, LegalEntity, Address, AddressPhone, CellularPhone, CalendarId, Citizenship, Email, HcmEmployment_ValidFrom, HcmEmployment_ValidTo, Religion, Doctrine, MaritalStatus, Employment_Type, StaffCategory, ProbationEndDate, Employee_status, FirstName_Arabic, LastName_Arabic, Name_Arabic, Designation_Arabic, Grade_Arabic,Department_Arabic, Section_Arabic,Branch_Arabic,Joiningdate_Arabic, EDCemployeeno_Arabic, Nationality_Arabic;
counter c = 0, HcmCompensationLevelCounter = 1;
DirPersonName DirPersonName;
HcmWorkerRecId hcmWorkerRecId;
HcmWorker hcmWorker;
HcmPersonDetails HcmPersonDetails;
HcmPersonPrivateDetails HcmPersonPrivateDetails;
HcmEmployment HcmEmployment;
Date validFrom, ValidTo;
GDI_Status GDI_Status;
utcDateTime _validFrom, _validTo;

;

file = new CommaTextIo(‘D:\\ImportedData\\Copy of Employee Master_Updated.csv’,’r’);
file.inFieldDelimiter(‘,’);

ttsBegin;
while (file.status() == IO_Status::Ok)
{
record = file.read();
PersonnelNumber = conPeek(record, 1);
EmploymentType = conPeek(record, 2);
FirstName = conPeek(record, 3);
LastName = conPeek(record, 4);
middleName = conPeek(record, 5);
Name = conPeek(record, 6);
Gender = conPeek(record, 7);
LegalEntity = conPeek(record, 8);
Address = conPeek(record, 9);
AddressPhone = conPeek(record, 10);
CellularPhone = conPeek(record, 11);
CalendarId = conPeek(record, 12);
Citizenship = conPeek(record, 13);
Email = conPeek(record, 14);
HcmEmployment_ValidFrom = conPeek(record, 15);
HcmEmployment_ValidTo = conPeek(record, 16);
Religion = conPeek(record, 17);
Doctrine = conPeek(record, 18);
MaritalStatus = conPeek(record, 19);
Employment_Type = conPeek(record, 20);
StaffCategory = conPeek(record, 21);
ProbationEndDate = conPeek(record, 22);
Employee_status = conPeek(record, 23);
FirstName_Arabic = conPeek(record, 24);
LastName_Arabic = conPeek(record, 25);
Name_Arabic = conPeek(record, 26);
Designation_Arabic = conPeek(record, 28);
Grade_Arabic = conPeek(record, 29);
Department_Arabic = conPeek(record, 30);
Section_Arabic = conPeek(record, 31);
Branch_Arabic = conPeek(record, 32);
Joiningdate_Arabic = conPeek(record, 33);
EDCemployeeno_Arabic = conPeek(record, 34);
Nationality_Arabic= conPeek(record, 35);

validFrom = str2date(HcmEmployment_ValidFrom, 213);
validTo = str2date(HcmEmployment_ValidTo, 213);

if (validTo < validFrom)
{
validFrom = str2date(HcmEmployment_ValidFrom, 213);
validTo = maxdate();

}

if (PersonnelNumber)
{

DirPersonName.clear();
DirPersonName.initValue();
DirPersonName.FirstName = (FirstName == ” ? Name : FirstName);
DirPersonName.LastName = LastName;

hcmWorker = hcmWorker::findByPersonnelNumber(strRFix(PersonnelNumber,4,’0′));

if (hcmWorker.RecId == 0)
{
hcmWorkerRecId = HcmWorkerTransition::newCreateHcmWorker(DirPersonName, strRFix(PersonnelNumber,4,’0′));
hcmWorker = hcmWorker::find(hcmWorkerRecId);
if (hcmWorkerRecId == 0)
{
continue;
}
}
else
{
DirPersonName = DirPersonName::find(hcmWorker.Person);
hcmWorkerRecId = hcmWorker.RecId;
}

HcmPersonPrivateDetails = HcmPersonPrivateDetails::findByPerson(hcmWorker.Person, true);
if (HcmPersonPrivateDetails.RecId)
{
HcmPersonPrivateDetails.Gender = (Gender == ‘F’? HcmPersonGender::Female:HcmPersonGender::Male);
// HcmPersonPrivateDetails.update();
}
else
{
HcmPersonPrivateDetails.Person = hcmWorker.Person;
HcmPersonPrivateDetails.Gender = (Gender == ‘F’? HcmPersonGender::Female:HcmPersonGender::Male);
// HcmPersonPrivateDetails.insert();

}
_validFrom = DateTimeUtil::minValue();
_validTo = DateTimeUtil::maxValue();
select ValidTimeState(_validFrom, _validTo) hcmEmployment
where HcmEmployment.Worker == hcmWorkerRecId;
// HcmEmployment = HcmEmployment::findByWorkerLegalEntity(hcmWorkerRecId, CompanyInfo::current());
HcmEmployment = HcmEmployment::find(HcmEmployment.RecId, true);
if (HcmEmployment.RecId)
{
HcmEmployment.ValidFrom = DateTimeUtil::newDateTime(validFrom, 0);
HcmEmployment.ValidTo = DateTimeUtil::newDateTime(ValidTo, 0);
HcmEmployment.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
//HcmEmployment.update();
}

HcmPersonDetails = HcmPersonDetails::findByPerson(hcmWorker.Person, DateTimeUtil::minValue(), DateTimeUtil::utcNow(),true);
if (HcmPersonDetails.RecId)
{
HcmPersonDetails.MaritalStatus = (MaritalStatus == ‘Married’ ? HcmPersonMaritalStatus::Married : HcmPersonMaritalStatus::Single);
HcmPersonDetails.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
HcmPersonDetails.GDI_Status = str2enum(GDI_Status, Employee_status);
HcmPersonDetails.FirstName_Arabic = FirstName_Arabic;
HcmPersonDetails.LastName_Arabic = LastName_Arabic;
HcmPersonDetails.Name_Arabic = Name_Arabic;
HcmPersonDetails.Grade_Arabic = Grade_Arabic;
HcmPersonDetails.Department_Arabic = Department_Arabic;
HcmPersonDetails.Section_Arabic = Section_Arabic;
HcmPersonDetails.Branch = Branch_Arabic;
HcmPersonDetails.EDCEmployeeNo = EDCemployeeno_Arabic;
HcmPersonDetails.Nationality = Nationality_Arabic;
HcmPersonDetails.JoiningDate = Joiningdate_Arabic;
//HcmPersonDetails.ValidFrom = DateTimeUtil::newDateTime(validFrom, 0);
// HcmPersonDetails.ValidTo = DateTimeUtil::newDateTime(ValidTo, 0);
HcmPersonDetails.update();
}
else
{
HcmPersonDetails.Person = hcmWorker.Person;
HcmPersonDetails.MaritalStatus = (MaritalStatus == ‘Married’ ? HcmPersonMaritalStatus::Married : HcmPersonMaritalStatus::Single);
HcmPersonDetails.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
HcmPersonDetails.GDI_Status = str2enum(GDI_Status, Employee_status);
HcmPersonDetails.FirstName_Arabic = FirstName_Arabic;
HcmPersonDetails.LastName_Arabic = LastName_Arabic;
HcmPersonDetails.Name_Arabic = Name_Arabic;
HcmPersonDetails.Grade_Arabic = Grade_Arabic;
HcmPersonDetails.Department_Arabic = Department_Arabic;
HcmPersonDetails.Section_Arabic = Section_Arabic;
HcmPersonDetails.Branch = Branch_Arabic;
HcmPersonDetails.EDCEmployeeNo = EDCemployeeno_Arabic;
HcmPersonDetails.Nationality = Nationality_Arabic;
HcmPersonDetails.JoiningDate = Joiningdate_Arabic;
// HcmPersonDetails.insert();

}

}
else
{
info(strFmt(“Line number %1 could not be inserted”,c));
}
}

ttsCommit;
info(“Job has been completed”);
}

Pre-requisite in Sharepoint 2013

During the installation of SP 2013, i find the following error while installing the sharepoint 2013 pre-requisite for Dynamics Ax 2012.

1. Error: Startup task doesn’t exist. This is not a continuation after a restart

2015-02-10 15:58:56 – C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup\SharePointServerPreparationToolStartup_0FF1CE14-0000-0000-0000-000000000000.cmd
2015-02-10 15:58:56 – Error: Startup task doesn’t exist. This is not a continuation after a restart.
2015-02-10 15:58:56 – Locating the following command line arguments file:
2015-02-10 15:58:56 – E:\PrerequisiteInstaller.Arguments.txt

2. Error: [In HRESULT format] (-2147024894)

2015-10-02 15:59:02 – Created thread for installer
2015-10-02 15:59:02 – “C:\Windows\system32\ServerManagerCmd.exe” -inputpath “C:\Users\ADMINI~1\AppData\Local\Temp\PreB8C.tmp.XML”
2015-10-02 15:59:02 – Error: Unable to install (2)
2015-10-02 15:59:02 – Error: [In HRESULT format] (-2147024894)
2015-10-02 15:59:02 – Last return code (2)
2015-10-02 15:59:02 – Reading the following DWORD value/name…

Fix: Just make a copy of ServerManager.Exe in the C:\Windows\system32 folder and rename it to ServerManagerCmd. Then rerun the setup again.

 

Thanks

 

Retail Store are not visible for non-admin user

While doing upgrade from Ax2009 to Ax 2012 R3, I faced this issue. Despite of giving all other retail related roles, the retail back office user are unable to see any retail store in the retail store form. If I give any user system administrator role then same user can see all store and retail store transaction.

After investigation, I find out that due to address book miss match the user are unable to see the retail store. So, I assign the correct address book to each of the user depending their organization structure and issue was resolved.