hi,
I have this SysComputedColumn method in a view where this view is used inside an entity and it's working fine
return strFmt('ISNULL((select top 1 JournalId from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.Id = %5), %6)' , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id)) , SysComputedColumn::comparisonLiteral('') );
Now i want to add a new enum field, which is JournalType -- does it work like this or do i need to convert it to int?
return strFmt('ISNULL((select top 1 JournalId from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.Id = %5 AND table1.JournalType != %6), %7)' , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalType)) // this is new , SysComputedColumn::comparisonLiteral('') );
yes thank you,
I created another thread
community.dynamics.com/.../invalid-relation-between-entities
Please don't change the topic - create a separate thread for you problem with the relation between entities and explain the situation in detail there.
Let's back to the topic of this thread - comparisonField() and JournalType. Did you get any new information when following my advise about how to see SQL code and the query result?
Hi Martin,
I'm getting this when exporting the composite entity: "Invalid relation between entities" -- is it because of this computed column?
The relation between Entity2 and Entity1 is:
Entity1.Id = Entity2.Id
Entity1.JournalParmId= Entity2.JournalParmId
Entity1.JournalId = Entity2.LinkedJournalId
Entity1 EntityKey: JournalId
Entity2 EntityKey: JournalId
LinkedJouranId and LinkedJournalType are computed columns:
public static server str LinkedJournalId() { return strFmt('ISNULL((select top 1 JournalId from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.Id = %5 AND table1.JournalType != %6), %7)' , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalType)) // this is new , SysComputedColumn::comparisonLiteral('') ); } public static server str LinkedJournalType() { return strFmt('select top 1 JournalType from Table1 as table1 WHERE table1.PARTITION = %1 AND table1.DATAAREAID = %2 AND table1.JournalParmId = %3 AND table1.JournalId != %4 AND table1.RentId = %5 AND table1.JournalType != %6' , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Partition)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, DataAreaId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalParmId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalId)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, Id)) , SysComputedColumn::comparisonField(tableStr(Table1View), identifierStr(Table1), fieldStr(Table1, JournalType)) ); }
You can simply run your method and look at the return value to see what SQL code it generates.
To see the complete view, go to SQL Server Management Studio and look at the definition of your view. There you can also run it and test changes.
If the type of Table1View.JournalType and Table1.JournalType is the same (which it should be), then no conversion is needed.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,836
Most Valuable Professional
nmaenpaa
101,156