Hello dear hope you are doing well.
so, you have to get help from DEV team to create an aggregate value tracking.
You can control spending by enabling project module but however it will not help if spending increases.
Layer 1: Category Group Configuration
Goal: Logically group your Wellness categories so they can be queried together.
Steps:
Step 1 — Create a shared Expense Category Group
Go to: Expense management > Setup > Expense categories
Create or verify the three categories exist:
- Gym
- Swimming
- Physiotherapy
In D365, there is no native "category group" field at the expense category level that the policy engine natively aggregates across. The workaround is to use a common naming convention or financial dimension to tag these as Wellness, which your X++ logic (Layer 3) will reference.
Practical approach: Prefix all three category names with WEL- (e.g., WEL-Gym, WEL-Swimming, WEL-Physio) so your X++ code can identify the group via a name filter — the same pattern used in your EAM procurement restriction work.
Layer 2: Audit Policy for After-the-Fact Detection
Goal: Flag monthly overspend at the report level using the Aggregate rule type.
Go to: Audit workbench > Setup > Policy rule type, then Audit workbench > Setup > Audit policies
Step 1 — Create a Policy Rule Type
| Field |
Value |
| Query name |
Expense report line |
| Query type |
Aggregate |
| Document date reference |
Transaction date |
Step 2 — Configure the Aggregate Rule
On the Aggregate tab:
- Group by:
Worker + Expense category (filter to WEL- categories)
- Aggregate function:
Sum on Transaction amount
- Having condition:
Sum > 100
- Date range: Set batch to run monthly
-
This uses the Aggregate query type to sum transaction amounts across lines, grouped by employee, and raises a violation when the sum exceeds your threshold — which can then be worked in the Audit Cases workbench.
Limitation: This is a detection and reporting mechanism, not a real-time prevention + auto-split mechanism. You need Layer 3 for that.
Layer 3: X++ Auto-Split Logic (The Real Solution)
Goal: At save/submit time, calculate the employee's cumulative WEL- spend for the current month, and if the new line pushes them over $100, automatically split that line — the reimbursable portion stays, the overage gets a new line with Charge to personal.
Key Tables:
| Table |
Purpose |
TrvExpTable |
Expense report header |
TrvExpTrans |
Expense report lines |
TrvExpSubCategory |
Links to shared category |
EcoResCategory |
Category master (name-based filter for WEL-) |
Event to Subscribe To:
Use a FormDataSourceEventHandler on TrvExpTrans.OnValidatedWrite or subscribe to the TrvExpTrans table's onValidating event — this fires when the user saves a line, before submission.
X++ Logic Pseudocode:
[DataEventHandler(tableStr(TrvExpTrans), DataEventType::ValidatingWrite)]
public static void TrvExpTrans_onValidatingWrite(
Common _sender, DataEventArgs _e)
{
TrvExpTrans newLine = _sender as TrvExpTrans;
TrvExpTrans histLine;
TrvExpTable header;
EcoResCategory category;
real monthlyUsed, remaining, overage;
date periodStart, periodEnd;
// 1. Check if this line's category is a Wellness category
select firstonly category
where category.RecId == newLine.ExpenseCategoryRecId
&& strStartsWith(category.Name, 'WEL-');
if (!category.RecId)
return; // Not a wellness category, exit
// 2. Calculate period start/end (current calendar month)
periodStart = dateStartMth(today());
periodEnd = endMth(today());
// 3. Sum all POSTED + IN-FLIGHT wellness expenses for this worker
select sum(Amount) from histLine
join header
where header.RecId == histLine.ExpenseReportRecId
&& header.WorkerRecId == newLine.Worker
&& histLine.TransDate >= periodStart
&& histLine.TransDate <= periodEnd
&& histLine.PersonalExpense != NoYes::Yes
join category
where category.RecId == histLine.ExpenseCategoryRecId
&& strStartsWith(category.Name, 'WEL-');
monthlyUsed = histLine.Amount; // aggregated sum
// 4. Calculate headroom
remaining = max(0.0, 100.0 - monthlyUsed);
overage = newLine.Amount - remaining;
if (overage <= 0)
return; // Within limit, nothing to do
// 5. Trim the current line to the remaining allowance
newLine.Amount = remaining;
// 6. Insert a new personal-charge line for the overage
TrvExpTrans overageLine;
buf2Buf(newLine, overageLine);
overageLine.Amount = overage;
overageLine.PersonalExpense = NoYes::Yes; // "Charge to personal"
overageLine.RecId = 0;
overageLine.insert();
}
Share
Report
105
All responses (
Answers (
