web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / SysComputedColumn::com...
Finance | Project Operations, Human Resources, ...
Answered

SysComputedColumn::comparisonField with enums

(0) ShareShare
ReportReport
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)
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 238,281 Most Valuable Professional on at

    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.

  • D365FO user Profile Picture
    D365FO user 465 on at

    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

  • Martin Dráb Profile Picture
    Martin Dráb 238,281 Most Valuable Professional on at

    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

    yes thank you,

    I created another thread

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

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

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Abhilash Warrier Profile Picture

Abhilash Warrier 843 Super User 2025 Season 2

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 428 Super User 2025 Season 2

#3
Martin Dráb Profile Picture

Martin Dráb 357 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans