Filtering data from entity in call

 If you want to fetch or view data from a data entity you might want to filter it to limit your results. This can be done by applying the filter functionality.

If you want to fetch all data in an entity you add data/[Entity name] in your environment URL. For example https://myd365environment.sandbox.operations.dynamics.com/data/WorkListSalesTables.


Add filter to URL

If that entity returns data irrelevant to you, you can filter it by adding filter keyword and column names and values to your URL. Under is an example where I only want one specific order. The column for order is named OrderNumber and the order number I want to retrieve data from is 100051.

Readable version:

https://myd365environment.sandbox.operations.dynamics.com/data/MyEntity?$filter=OrderNumber eq '100051'

Parsed to Web URL:

https://myd365environment.sandbox.operations.dynamics.com/data/WorkListSalesTables/?$filter=OrderNumber%20eq%20%27100051%27

If you have more values to filter on you add them with the and syntax.

Readable version: 

https://myd365environment.sandbox.operations.dynamics.com/data/MyEntity?$filter=OrderNumber eq '100051' and dataAreaId eq 'fn02'

Parsed to Web URL:

https://myd365environment.sandbox.operations.dynamics.com/data/WorkListSalesTables/?$filter=OrderNumber%20eq%20%27100051%27and%20dataAreaId%20eq%20%27fn02%27

This returns only one order

Operators:

https://support.lusid.com/knowledgebase/article/KA-01914/en-us

OperatorMeaningDescriptionFilter expression examples
eqEqualsReturns a record if the value exactly matches the specified value.displayName eq 'MyPortfolio'
isDerived eq True
neqNot equalsReturns a record if the value does not match the specified value.displayName neq 'MyPortfolio'
isDerived neq True
gtGreater thanReturns a record if the value is greater than the specified value.created gt 2019-01-15T10:00:00
unit gt 10
gteGreater than or equal toReturns a record if the value is greater than or equal to the specified value.created gte 2019-01-01
unit gte 10
ltLess thanReturns a record if the value is less than the specified value.created lt 2019-01-01
unit lt 10
lteLess than or equal toReturns a record if the value is less than or equal to the specified value.created lte 2019-01-01
unit lte 10
startswith Returns a record if the value starts with the specified value.displayName startswith ‘MyPo’
in Returns a record if the value matches any of the specified values.displayName in 'MyPortfolio', 'MyOtherPortfolio'
not in Returns a record if the value is not in any of the specified values.displayName not in 'MyPortfolio', 'MyOtherPortfolio'
ctContains tokenReturns a record if the string value contains the specified token (that is, word). 

For example, if an instrument has the name 'British Airways':
 

  • name ct 'airways' returns it
  • name ct 'air' does not return it
  • name ctsw 'air' returns it
  • name ctsw 'rway' does not return it
ctswContains token starting withReturns a record if the string value contains a word starting with the specified token. 
exists Returns a record if the value is set.properties[Portfolio/myscope/mycode] exists
not exists Returns a record if the value is not set.properties[Portfolio/myscope/mycode] not exists
andLogical AND operatorConnects two or more filter expressions and returns a record if all resolve to true.isDerived eq True and created lt 2017-03-15
orLogical OR operatorConnects two or more filter expressions and returns a record if one resolves to true.isDerived eq True or displayName eq 'MyPortfolio'
  Note you can combine the and and or operators; and is evaluated before or following standard Boolean operator precedence. If you want to change the precedence, use parentheses. So the order of evaluation is: parentheses, and, or.displayName eq 'MyPortfolio' or (isDerived eq True and created lt 2017-03-15)
any If used with a filter expression, returns a record if any of the values in a set match. If used without, returns a record if there are any values. For use with multi-value properties and relationships only.properties[Portfolio/myscope/mycode] any (~ eq 'Growth')
properties[Portfolio/myscope/mycode] any
all Returns a record if all of the values in a set match a filter expression. For use with multi-value properties and relationships only.properties[Portfolio/myscope/mycode] all (~ eq 'Growth')
none Returns a record if there are no values in a set. For use with multi-value properties and relationships only.properties[Portfolio/myscope/mycode] none
Location: Bergen, Norway

Comments

Post a Comment

Popular posts from this blog

Call a simple Logic app from X++

Image
Logic apps can be called manually from D365FO with code I've made a basic Logic App that receive a short JSON. 1. Get JSON schema Get your JSON sample and go here and paste it in  https://www.liquid-technologies.com/online-json-to-schema-converter . Press Generate Schema and you have your schema in the box below. Example JSON : {   "sku": "875828",   "externalMaterialReference": "400267" } Example JSON Schema { "$schema" : "http://json-schema.org/draft-04/schema#" , "type" : "object" , "properties" : { "sku" : { "type" : "string" }, "externalMaterialReference" : { "type" : "string" } }, "required" : [ "sku" , "externalMaterialReference" ] } 2. Create Logic App In the Azure Portal or in your Visual Studio, cre
Read more

SysOperationFramework with use of Query

Image
 The SysOperation framework is very good and effective, but unforutnatly a bit complicated to get in to and do as you want.  I had one case in which a simple table should be updated, the job was to be able to set in batch. Also it had to use a query so that the user could easily set up the ranges as he pleased. Note that I will use the same query for updating the table which I mentioned. So the same query is used all the way through ending in using Quer::updateRecordset For this you need: Data contract class Controller class Service class Simple query oject Action Menu item Main menu where to make the menu item available. 1. Data contract class Note the simple query highlighted in bright yellow .  [DataContractAttribute     , SysOperationGroupAttribute('Parameters',  "Parameters", '1')] internal final class CustAutoCancelUpdateDataContract {     str             packedQuery;     MCRAutoCancel   autoCancel;     public Query getQuery()     {         return new Qu
Read more

Retail Attributes on sales lines

Image
Recently I've dived into Retail. There will be some posts about retail and the problems I've faced with solutions. First off will be the Retail Attributes, they can be problematic here is a data base diagram illustrating a simplyfied view, assuming the value is of type text. And here is the select statement for listing all sales lines with their attributes.         SalesTable salesTable;         SalesLine salesLine;         RetailSalesTableInstanceValue instanceValue;         EcoResAttribute attribute;         EcoResAttributeValue attributeValue;         EcoResTextValue      textValue;     while select salesId                  from  salesLine             join SalesId                   from salesTable                 where salesTable.SalesId == salesLine.SalesId             join SalesId, RecID                 from instanceValue                 where    instanceValue.SalesId == salesLine.SalesId                 &&  
Read more