Till now, $filter can be applied for a field against a constant value. With this new feature $filter can be applied for a field against another field in scenarios like below
Get all contacts where First Name = Last Name
Get all Opportunities where Estimated Revenue >= Actual Revenue
Get all Cases Resolved post due date. etc..
Supported Filters:
Equal
NotEqual
GreaterThan
GreaterEqual
LessThan
LessEqual
Example: To retrieve all contacts whose First Name = Last Name
Web API Query:
<Instance Web API>/contacts?$filter=firstnameeqlastname [ Note: This Web API expression works only for version 9.1.0000.19562 or later ]
Using this approach we can update any field from a Record without actually impacting Modified By & Modified On values.
Please Note: It is not best practice to suppress Modified By & Modified On auto calculation by Dynamics, but this approach helps in scenarios like below
Scenario -1 : We introduced a new filed and want to set a default value for all the existing records, but don’t want to touch Modified By for reporting / downstream integration purposes.
Scenario – 2 : We missed updating some information during data migration and want to update the information later without Modified On/Modified By being updated.
Approach:
We will be using Pre-Operation plugin to set “modifiedon” & “modifiedby” to the existing values (fetched from Pre-Image). Same Approach can be for overriding Modified By / Modified On / Created By / Created On with some custom values.
Step-By-Step process:
1) For the Entity you want to use (Account for this example), Create a new boolean field called “Override Modified On” (new_overridemodifiedon), set default value as NO.
3) Register the plugin and add step as shown below.
Message = Update; Primary Entity = <<Entity you want to use >> Filtering Attribute = new_overridemodifiedon (created in 1st step) Pipeline Stage = Pre-Operation.
4) Register an Image under the step as below
Image Type = Pre Image Name = PreImage Parameters = modifiedby, modifedon
5) Now whenever you want to Update something without “Modified On” being touched, Check the checkbox “Override Modified On” along with your other changes.
The changes are tracked under Audit History.
But you can observe that, even after the fields are updated, Modified On doesn’t change and retains its old value.
6)It works even if you are making updates through SDK / any other integration platform. You just need to set “Override Modified On” = true, along with the update that you perform.
Hello Learners, here is the key take-away from Microsoft Business Applications Summit (MBAS)-2020.
Power Platform provides Tabular Data Stream (TDS) endpoint for CDS, using which we can access environment’s data using SSMS just like how we access the data in on-premise environment (*Only Read is permitted, cannot perform Updates)
Its in Preview and not available for all regions. It works only with version 9.1.0.17437 and above. Check if the latest version is available for your region here. Alternatively you can check the version of your Environment from Settings –>About.
2. To use the SQL connection, you need to enable the Tabular Data Stream (TDS) endpoint using below options
3. SQL Connection’s Server Name would be <organization url>,5558 (5558 is the port used for TDS endpoint. it is same for all organizations). For authentication, we can use the same Active Directory Credentials that we use to access the Organization. Application Users with Azure Client ID are not supported for now. Step-By-Step guidance is available here
4. The same SQL Connection Can be used in Power BI to analyse data. Step-By-Step guidance HERE