You’re offline. This is a read only version of the page.
Hi All,
I am trying to add new computed field to standard data entity, what I want to return to this field NO or Yes based on if current record has data in related table (I want to do select statement to find if current record has related data and return yes or no based on it . I am truing this but synchronization issue appear
return SysComputedColumn::if(
SysComputedColumn::isNotNullExpression(expression),
SysComputedColumn::returnLiteral(NoYes::Yes),
SysComputedColumn::returnLiteral(NoYes::No));
expression contain sql statement to check if it has data in the related table.
Any hint?
I think it doesn't generate correct SQL code - you need to put the subquery to parentheses. For example:
Share
Report
All responses (
Answers (
str roleTypeValue = SysComputedColumn::returnLiteral(DirPartyRoleType::Customer);
str expression = strFmt(@"SELECT DirPartyView.RecId FROM DirPartyView
JOIN DIRPARTYTABLE on DirPartyView.PARTY = DIRPARTYTABLE.RECID
WHERE DIRPARTYTABLE.PARTYNUMBER = %1
and DirPartyView.RoleType = %2",
SysComputedColumn::returnField(tableStr(DirPartyV2Entity),
dataEntityDataSourceStr(DirPartyV2Entity, DirPartyBaseEntity), fieldStr(DirPartyV2Entity, PartyNumber)),
roleTypeValue);
return SysComputedColumn::if(
SysComputedColumn::isNotNullExpression(strFmt('(%1)', expression)),
SysComputedColumn::returnLiteral(NoYes::Yes),
SysComputedColumn::returnLiteral(NoYes::No));
By they way, please use Insert > Code to paste code to this forum (as I did).
Hi Mariano,
Yes I tried it on SQL and it is working from SQL
Hi Martin,
this the code that I call on Computed field:
str expression;
str roleTypeValue = SysComputedColumn::returnLiteral(DirPartyRoleType::Customer);
expression = strFmt(@"SELECT DirPartyView.RecId FROM DirPartyView
JOIN DIRPARTYTABLE on DirPartyView.PARTY = DIRPARTYTABLE.RECID
WHERE DIRPARTYTABLE.PARTYNUMBER = %1
and DirPartyView.RoleType = %2",
SysComputedColumn::returnField(tableStr(DirPartyV2Entity),
dataEntityDataSourceStr(DirPartyV2Entity, DirPartyBaseEntity), fieldStr(DirPartyV2Entity,
PartyNumber)), roleTypeValue);
return SysComputedColumn::if(
SysComputedColumn::isNotNullExpression(expression),
SysComputedColumn::returnLiteral(NoYes::Yes),
SysComputedColumn::returnLiteral(NoYes::No));
Write a simple runnable class, call the method and print the result in a screen, in the end is a string with the SQL syntax that will be added into the entity, which is a view in SQL, I do it each time I have to write a computed column, execute the query in SQL management studio (take notice that you'll have to add the query to the underneath sql code from the view), this way you'll see where is the error, fix it and then you'll know what to do in x++
It would help if you shared all your code and the result of the method. The problem is likely caused by what you have in the expression, which you told nothing about.
But from what I know, your code looks logically wrong to me. The expression DirPartyView.RoleType = 1 will never return NULL, will it?
Hi Judy,
Can I debug it? I tried to export data and put break points, but the debugger did not hit. Any hints?
Hi Andre,
this is the error and appear when I am trying to build and sync code
Hi,
As mentioned, please provide us with the more details.
Please check SysComputedColumn class in MS document:
learn.microsoft.com/.../gg945162(v=ax.60)
And the method isNotNullExpression() is not mentioned in the class.
Nevertheless, it is recommended that you split the 3 expressions in if condition, and then debug to see the root cause of the error.
Hi Waed,
What is the exact error and when does this appear?
#1
André Arnaud de Cal...
291,965
Super User 2025 Season 1
#2
Martin Dráb
230,817
Most Valuable Professional
#3
nmaenpaa
101,156
