Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

SysComputedColumn::comparisonField with enums

(0) ShareShare
ReportReport
Posted on by 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?

  • D365FO user Profile Picture
    D365FO user 465 on at
    RE: SysComputedColumn::comparisonField with enums

    yes thank you,

    I created another thread

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,836 Most Valuable Professional on at
    RE: SysComputedColumn::comparisonField with enums

    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?

  • D365FO user Profile Picture
    D365FO user 465 on at
    RE: SysComputedColumn::comparisonField with enums

    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

    pastedimage1675603676666v2.png

    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

    pastedimage1675606544194v1.png

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,836 Most Valuable Professional on at
    RE: SysComputedColumn::comparisonField with enums

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans