SysComputedColumn::comparisonField with enums

(0) Share

Report

Posted on by D365FO user

465

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('')
             );

Also how can i test this computed column method or debug it to see what value this SQL statement actually returns?

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Martin Dráb 238,743 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report
Copy link

Link copied!
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)) ); }

goshoom *I've noticed that if JournalParmId in Entity 1 is not repeated then the export doesn't fail like example below -- however, if more than JournalParmId in entity1, then it fails like the example above

Was this reply helpful? Yes No
Martin Dráb 238,743 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
D365FO user 465 on at

Like (0)

Report
Copy link

Link copied!

yes thank you,

I created another thread

community.dynamics.com/.../invalid-relation-between-entities

Was this reply helpful? Yes No