.jpg)
Oops, I did it again…How many times have you been working on a table form or its Business Process Flow thinking a Business Rule will do the job? Whether it is a specific data calculation, or any other automation, it does not always work out. And there are only so many times you can quote the early 2000’s when working on a Model-Driven App/ Dynamics 365.
Let’s state the Stronger powers
Business Rules are our low-code way into JavaScript without actually coding into our forms event handlers. They are declarative simple rules which are solution aware, which should form part of strong Change Management practices. The key things they can do:
Field recommendations: Great for directing users on how to best complete fields.
Locking/unlocking: Whilst having permanently a field set to Read only may have a business reason to exist, conditional logic to lock /unlock it can be quite useful to direct users to the right data input.
Show field error messages: Be kind and merciful to your users with simple-language error messages, do not just assume they will just be stronger for it.
Set field value: Whilst you cannot use complex formulas and things like Power Fx, there are simple yet helpful things. From defining a value straight away e.g. “30” or “Cash” in alphanumeric texts, you can also set dates in date fields (with the option of adding or subtracting a number to it), set the value of one field to another field’s value within the same table or just clear a value altogether.
Set default value: Similarly, a starting point for your data logic to support your CRM platform’s data hygiene.
Set business required: This can switch between Business Required and Not Business Required, depending on what you would like to ask from your users and processes.
Set visibility: Show/ hide logic is quite a common requirement, and saves you trouble from coding it in.
Another thing to note is that they run on model-driven client and real time server. Just define the scope to control here these rules execute.
Big disclaimer though, all data must be within the SAME TABLE. Hmm. Not going to cut it for all our CRM needs, will it?
Gimme More
It is quite common to need to automate some data logic across tables in your data model. So, what other options do we have?
Classic Workflows
Power Automate
JavaScript
Plugins
Logic Apps and more…
The further down we go into this list, the more we move from low-code to pro-code. Fusion teams unite! Nothing like a technical rabbit hole to keep us going.
Some of the considerations when we chose our automation option include:
How many tables we are working with
What database or systems we are working with (not always or exclusively Dataverse and Power Platform)
Do we have pro-dev skills?
Do we want to have synchronous or asynchronous results? (Not just what the user wants because they will always say real time. Consider actual process needs, app performance etc. too)
Power Automate – One More Time
There are many use cases to leverage Power Automate, setting a value with data across tables is one of them. Why don’t we look at this step by step?
Just to set the scene, we are calculating the price of a stay for a hotel guest, which will vary depending on duration of stay, hotel location and room type among other reasons.
Step 1: Our trigger is the classic When a row is Added, Modified or Deleted from Dataverse. We got a hotel booking Model-Driven App in the background.
Change type can stay as Added or Modified, because we do not just want to capture new room bookings. Sometimes, existing ones have amends e.g. extending or shortening a stay.
Table name: Reservations as this is where our customer booking data is
Scope: Organization as we are keeping it simple for demo purposes. As always, please adjust to your own permissions and security model.
Select columns: We do not want to trigger this automation too many times and get stuck in an infinite loop. Thus, sticking to only amends in the Check-in Date (cred4a_checkindate as the logical name) and Check-out Date (cred4a_checkoutdate as the logical name) will do the trick. Other filtering options include OData expressions in Filter rows or JSON ones in Trigger Conditions from the Settings.
Step 2: Since this is a cross-table calculation, and our trigger fetched data from one table, let’s get the other one. We will use the Get a row by ID Dataverse action to get data from the related Rooms table. This holds data for each room type, hotel location, view location, extra amenities, special offers etc. If you need to extra data from more than two tables, you will add more actions of the same.
Table name: As mentioned, this is the Rooms table.
Row ID: We need to grab for that specific reservation the related Room Type data, particularly the base price per night. In case you’re wondering why the base price per night. Each customer pays in their own currency, and depending on exchange rates set up, the total local price is calculated. Either way, what we want in Row ID is to match our lookup field for the Rooms table which is part of the Reservations table as part of their Many to One relationship. This is our entry point. Find it from the Dynamic content tab.
Another thing to note is that when you look at Dynamic content and wondering what to pick, you may stumble upon three versions of your suspect: the Table Name version, the Type one and the Value one. We need to choose the Value one, but let’s explain the differences to avoid Toxic mistakes.
Use Type for data type validation, error prevention, and dynamic handling. For example, in complex workflows where different data types require different handling.
Use Value for accessing, transforming, and acting on the actual data. This will be the one you most frequently use.
Use Table Name for ensuring data comes from the correct source, especially in relational or multi-entity workflows.
Step 3: We will use a Compose action for our next step to get the Total Booked Days. This is a calculated field, and it will not appear straight away for our calculation in the Dynamic content tab.
You may wonder, why can we not retrieve it straight away? Calculated fields are evaluated dynamically and might not be part of the base schema or raw API response by default. List Rows for example fetches static or stored data efficiently but doesn’t always fetch transient, computed fields unless explicitly configured.
This is why by us using a Compose action to inspect, reference, or replicate the calculated data, you can work around these limitations effectively. Thus, we add in the Inputs the Total Booked Days!
Step 4: We are ready to make the calculation happen.
Here is the formula: mul(outputs('Fetch_the_Total_Booked_Days'),outputs(‘Fetch_data_from_the_Rooms_table')?['body/crd4a_pricepernight_base'])
We are using the:
multiplication function mul
Total Booked Days from the previous Compose action, as indicated by outputs('Fetch_the_Total_Booked_Days')
Price Per Night (Base) from the Get a row by ID action, as indicated by outputs(‘Fetch_data_from_the_Rooms_table')?['body/crd4a_pricepernight_base'])
Step 5: We are, for our final step, going to use the Update a row Dataverse action to add our calculated Total Booked Days into the reservation record.
Table name: Reservations as this is where the booking records are
Total reservation price: We will the Outputs from the second Compose where our calculation took place.
Now hit Save, Publish all customizations in your solution, and watch the magic happen One More Time!
Comments