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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Data entity ranges
Finance | Project Operations, Human Resources, ...
Answered

Data entity ranges

(0) ShareShare
ReportReport
Posted on by junior AX 1,552

Hi,

1. what's the difference between putting a range on the dataSource of the entity. Or in the range node of the entity itself?

2. I have an Enum field called status in one of the dataSources that have the following names: A B C D E F

i want to export the entity by putting a range that meets the following  if (status = A or status C or Status = D or Status = F)

i think this one is easy, i should do this:
pastedimage1604581583041v1.png

comma means OR right????

3. However there is special condition for Status = F

if another field called field1 is filled then don't take Status = F. However if field1 was empty then i should take it. How can i do this?

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,099 on at

    Hi JuniorAX,

    1. Range on data source it's a hard-coded range that can't be changed without development. The range on entity-level doesn't work for me, but I suppose that it should work as ranges on the query: a predefined set of ranges which can be changed in a data project

    2. Yes, it's correct.

    3. For query objects you can write code for a complex range like your mentioned, but it will not work for entities. In your case, I would try to create a computed column with returns 0 or 1 (if A, C, D returns 1, if F + special condition returns 1 else 0) and use it for filtering.

    docs.microsoft.com/.../data-entity-computed-columns-virtual-fields

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    1. which methods should i use from sysComputed column?

    I think the method should be int

    and i should use returnInt(0) or return int(1) if the condition matches as there is no return boolean.

    However what should i use the condition in sysComputed column methods? how to say if EnumField value = A return 1?

    2. how about using initializeDataSource method for creating a range?
    The topic is not exactly the same but would it work?
    i didn't know how to do it ( as i'm using my own entity)
    community.dynamics.com/.../733900

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,099 on at

    Hi JuniorAX,

    1. You will need to use many methods (couple if, and or). You can find many examples by references in standard code how to create computed field using SysComputed class method (one of example PayPredPredictionResultView.computeHasLateWarning)

    2. The last comment in the thread that it doesn't work, I haven't tried it (but I'm now interested as well)

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    I started trying, not sure if i'm doing it in the best way, but i got stuck at the last line how i'm going to say if F AND Field1 not empty then return 1 else return 0
    can you help me in continuing the last condition?

    str A = SysComputedColumn::returnLiteral(Status::A);
        str C = SysComputedColumn::returnLiteral(Status::C);
        str D = SysComputedColumn::returnLiteral(Status::D);
        str F = SysComputedColumn::returnLiteral(Status::F);

        str Status = SysComputedColumn::returnField(tableStr(Entity1), tableStr(Table1), fieldStr(Table1, Status));
        str Field1 = SysComputedColumn::returnField(tableStr(Entity1), tableStr(Table1), fieldStr(Table1, Field1));
        
        return SysComputedColumn::if(
            SysComputedColumn::compareExpressions(A, '==', Status),
            SysComputedColumn::returnLiteral(enum2int(NoYes::Yes)),
            SysComputedColumn::if(
            SysComputedColumn::compareExpressions(C, '==', Status),
            SysComputedColumn::returnLiteral(enum2int(NoYes::Yes)),
            SysComputedColumn::if(
            SysComputedColumn::compareExpressions(D, '==', Status),
            SysComputedColumn::returnLiteral(enum2int(NoYes::Yes)),
            SysComputedColumn::compareExpressions(F, '==', Status),

    also am i doing it right or complexing things? should i use compare expression or equal compression and should i return literal or enum2int. can you advise?

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,099 on at

    Hi JuniorAX,

    First, you need to write SQL query to understand how to construct it via SysComputed

    select ... where (status = A or status = C or status = D or (status = F and field1 = my_value)

    After you need to divide the filter part (after where) by logical groups, as you see you have one 'and' condition and the rest is 'or'

    Then you just start implementing x with SysComputedColumn class bases on logical where groups (in the end you should get something like below (i didn't test it))

    List          andConditionList           = new List(Types::String);
List          orConditionList            = new List(Types::String);

//Add A status to 'or' condition list
orConditionList.addEnd(
                    SysComputedColumn::equalExpression(
                        SysComputedColumn::comparisonField(tableStr(Entity1), identifierStr(Table1) , fieldStr(Table1, Status)),
                        SysComputedColumn::comparisonLiteral(enum2int(Status::A)))
        );
		
//Add C status to 'or' condition list
orConditionList.addEnd(
                    SysComputedColumn::equalExpression(
                        SysComputedColumn::comparisonField(tableStr(Entity1), identifierStr(Table1) , fieldStr(Table1, Status)),
                        SysComputedColumn::comparisonLiteral(enum2int(Status::C)))
        );
		
//Add D status to 'or' condition list
orConditionList.addEnd(
                    SysComputedColumn::equalExpression(
                        SysComputedColumn::comparisonField(tableStr(Entity1), identifierStr(Table1) , fieldStr(Table1, Status)),
                        SysComputedColumn::comparisonLiteral(enum2int(Status::D)))
        );

//Start creating and condition for F status

//Add status F to 'and' condition list
andConditionList.addEnd(
                    SysComputedColumn::equalExpression(
                        SysComputedColumn::comparisonField(tableStr(Entity1), identifierStr(Table1) , fieldStr(Table1, Status)),
                        SysComputedColumn::comparisonLiteral(enum2int(Status::F)))
        );
		
//Add field1 to 'and' condition list
andConditionList.addEnd(
                    SysComputedColumn::equalExpression(
                        SysComputedColumn::comparisonField(tableStr(Entity1), identifierStr(Table1) , fieldStr(Table1, Field1)),
                        SysComputedColumn::comparisonLiteral(value_for_field1)
        );
		

//Add 'and' condition to 'or' condtition list
orConditionList.addEnd(SysComputedColumn::and(andConditionList));

//Final if
return SysComputedColumn::if(
                    SysComputedColumn::or(orConditionList),
                            SysComputedColumn::returnLiteral(1),
                            SysComputedColumn::returnLiteral(0)
        );

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    1. shouldn't i use equal expression instead of notEqual?

    2. does comparison field return int, that's why u used enum2int?

    3. why did you function "and" from sysCopmuted?

    //Add 'and' condition to 'or' condtition list

    orConditionList.addEnd(SysComputedColumn::and(andConditionList));

    what does it mean? does the and here means add?

    4.  SysComputedColumn::or -- does this means if any condition is met in the list (by using or) then return true?

    5. Does doing this affect performance ( i mean using alot of sysComputed methods)

    6. I will test it now and get back to you.

    7. I've been wondering, could i have put the three ranges in Entity range property and only did a new method for the last condition? not sure if it's doable

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,099 on at

    Hi JuniorAX,

    1. Yes, it was a copy-paste issue, I fixed it in the code sample. Thank you for pointing it.

    2. Enum values stored in DB as int, so that why you need to use enum2int to avoid using integer constants

    3. So if you break select where part into logic groups you will get the expression:

    condition1 or condition2 or condition3 or condition4

    where:

    condition1..3 is filter by enum value

    condition4 = (status = F and field1 = my_value)

    In the posted example, I have 2 lists: one for 'or' condition and one for 'and' (condition4), and in the end in 'or' list I need to combine 3 status filters + one and

    4. Yes, it's correct

    5. Code itself is used only during syncronization to create a physical field in "select" clause (you can see it in SQL if you find the view with entity name). Filtering by this field could be a heavy operation, but it depends on how many records you will have in the table and how sql engine decides to build a execution plan.

    7. In query\entity ranges if you create 2 query ranges for the different fields it will be translated to sql as 'and' condition, so it will not work.

    One more possible solution came to my mind is to create a advanced filtering function and use it in data project (will not work for oData), for example EcoResProductDimensionQueryRangeHelper.productDimensionRangeColor

    docs.microsoft.com/.../advanced-filtering-and-query-options

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    1. so adding to a list always represents an OR

    2. The way you added the first 3 statuses to the orList is exactly the same as the way you added Status F and field1.

    So how come the OrList has: A or C or D

    while the AND List has (F and field1 == null) since it's the same way, shouldn't it be read F or Field1=null

    in which part did you specify the and?

    or you mean when you used the "and" function it meant that all different values of andList should be anded  instead of OR?

    3. So i'm going to run the recurring integration each 3 minutes for example and each 3 minutes i'm expecting 1k records max. Is this considered bad?

    4. can you explain more about advance filtering? is it on data project in DMF or code i got confused.

    What should i do exactly? and is this better for performance other than  computed columns?

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,099 on at

    Hi JuniorAX,

    1. Adding to a expressions to list doesn't represent any logical operation, it just list, but list of expressions can be converted to 'or' or 'and' condition with SysComputedColumn::and or SysComputedColumn::or.

    2. Yes, because all 4 conditions (3 status and combined 'and' condition) should be combined in 'or' expression. The part where 'and' condition is added can be found by comment: //Add 'and' condition to 'or' condtition list

    3. Is it some kind of transaction table with high-volume amount of data? Are you planning to use incremental pushes?

    4. You can create new advanced filter function (x++, check example in standard code) and use it in data projects filters.

    Can't say anything about performance, with advanced filtering it's more obvious how SQL engine will behave, but SQL execution plan could be the same in both cases (=equal performance).

    Have you thought if field1 condition can be implemented outside F&O? You just filter data by statuses, but middleware or 3rd party system skip records in status D if field1 is empty.

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    1. so it's like
    orList = A,C,D
    AndList = F,field1=null

    Then this sentence
    orConditionList.addEnd(SysComputedColumn::and(andConditionList));
    it means orList = A,C,D,(F and field1 =null)

    then this sentence
    SysComputedColumn::or(orConditionList),

    it means orList = A or C or D or (F and field1 =null) // here's when the or is placed

    2. How im going now to put range on the entity and say if method returned value = 1? //other than that don't export

    3. yes it's a transaction (not sure if it's considered high) but around 1k each 3 minutes. and yes i'm using incremental push 

    4. i'll look more into this advanced filtering, i think doing it inside code is better, cause maybe someone will forget to put the range on data project, do u agree?

    5. yes i thought about this, but i though maybe i could do it from my side. Do you think doing it outside is better for performance?

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders

These are the community rock stars!

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

#1
Martin Dráb Profile Picture

Martin Dráb 582 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 410 Super User 2026 Season 2

#3
CU-1234529-002 Profile Picture

CU-1234529-002 185

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans