When working with Dynamics
365 CRM, we often encounter scenarios where simple calculations are required on
entity forms—like multiplying quantity by price, calculating age from
birthdate, or dynamically showing status labels based on logic.
🎉 That’s where Formula Fields come
into play!
📌 What is a Formula Field?
A Formula Field in
D365 CRM is a no-code way to perform real-time calculations using system data.
It leverages Power Fx—the same Excel-like language used in Canvas Apps—and is
executed on the server whenever the record is retrieved. 💻⚙️
Introduced as part of the
low-code wave in Power Platform, Formula Fields reduce the need for plugins,
workflows, or JavaScript for simple logic.
✅ Advantages of Formula Fields
🔹 No Code Required: Easy to
configure using the UI—no developer skills needed.
🔹 Real-Time
Calculation: Values are calculated and stored in memory when retrieved.
🔹 Consistent
Output: Same logic is applied everywhere the field is used.
🔹 Faster
Development: Quickly define logic without plugin deployment.
🔹 Secure &
Scalable: Runs on server-side, ensuring better performance than client-side
scripts.
❌ Disadvantages of Formula Fields
🔸 Read-Only: These fields are not
editable—they're calculated automatically.
🔸 Limited
Complexity: Not ideal for very complex business logic or external data
fetches.
🔸 No Triggering:
You can’t trigger workflows or plugins based on formula field changes.
🔸 Calculated at
Read-Time: No persistent storage—value isn’t stored in DB, so you can’t
filter directly in Advanced Find/Views (unless used with Virtual Tables or
other techniques).
🛠️ Real-Time Example 1:
Calculating Total Amount 💵
Let’s say we have a custom
entity called Sales Order Line with the following fields:
- Quantity (Whole Number)
- Unit Price (Currency)
- Total Amount (Formula Field) = Quantity
* Unit Price
👇 Step-by-Step Guide to
Create a Formula Field in D365 CRM
🧭 Step 1: Navigate to the
Table
Go to Power Apps →
Solutions → Your Solution → Tables → Sales Order Line.
🧩 Step 2: Add a New Column
1. Click
on + Add column
2. Name
it: Total Amount
3. Data
Type: Formula
4. Data
Format: Currency
5. Click
Next
🧮 Step 3: Define Formula
Use Power Fx formula:
✅ Tip: Fields are accessed by their logical
names. Intellisense helps you select them.
🖱️ Step 4: Save and Publish
Click Done, Save
Table, and Publish All Customizations.
💥 That's it! Now every time a record is
opened, the system will dynamically calculate the Total Amount by
multiplying the Quantity and Unit Price.
🧠
Additional Example 2: Displaying Customer Priority Based on Revenue 🏆
Let’s say you have a Customer table (Account),
and you want to show a Priority Level based on their Annual Revenue.
You want to implement this logic:
- If
AnnualRevenue > 1,000,000 → "High"
- If
AnnualRevenue between 500,000 and 1,000,000 → "Medium"
- Else
→ "Low"
This is a perfect use case for a Formula Field!
👇
Step-by-Step: Creating Priority Level Formula Field
🧭
Step 1: Go to Account Table
Navigate to:
Power Apps → Solutions → Your Solution → Tables → Account
➕
Step 2: Add a New Column
1.
Click + Add Column
2.
Name it: Priority Level
3.
Data Type: Formula
4.
Data Format: Text
5.
Click Next
🧮
Step 3: Use This Power Fx Formula:
✅
This formula uses nested If logic, similar to Excel. It’s easy to read and
intuitive.
💾
Step 4: Save and Publish
Click Done, then Save Table, and finally Publish
All Customizations.
🧪
How It Works
Whenever an Account record is opened or used in a
view/form, the Priority Level field will automatically evaluate and display:
- “High”
for big customers 💼💸
- “Medium”
for mid-sized customers 💼
- “Low”
for the rest 🙌
🎯
Bonus Use Case
You can now use this Priority field in:
- Forms
to show colored indicators using conditional formatting 🎨
- Dashboards
to group customers by value 📊
- Email
Templates to personalize messages like:
“Thanks for being one of our High Priority customers!” ✉️
📊 Where It’s Used
- Custom forms
- Subgrids
- Reports (readonly data)
- View column display
- Dashboards
🧠 Pro Tip:
Use formula fields
for display and reporting purposes. If you need the value stored for filtering
or audit trails, consider a calculated field or a plugin instead.
📝 Summary
|
Feature |
Formula Field |
|
Custom Code Needed |
❌ No |
|
Editable |
❌ No |
|
Stored in DB |
❌ No (calculated on read) |
|
Ideal For |
💡 Light Calculations, Display Logic |
|
Not Ideal For |
🔄 Triggers, Complex Flows, DB Queries |
💬 Have you tried using formula fields
in your D365 CRM projects? Let me know how it helped simplify your logic,
or share any challenges you faced!
