Power Platform Community Forum Thread Details

Hello Community,

I am working on a model-driven app in Power Apps with Dataverse.

I have a table called Other Receipts with a lookup field (Bank Account) that points to the Bank Account table (Many-to-One relationship).
The Bank Account table has a rollup column (Last Reconciled On) which gives the most recent reconciliation date of the bank account.

Requirement:

In the New Other Receipt form, the Receipt Date field must be after BankAccount.LastReconciledOn. If not, the system should block the save and show an error message.

What I tried:

I attempted to implement this with a Business Rule on the Other Receipts table, but in the Condition setup I only see fields from Other Receipts itself. I don’t see any option to select related Bank Account fields.

Question:

Is it possible to use a related entity field (e.g., BankAccount.LastReconciledOn) in a Business Rule?
If not, what is the recommended way to enforce this check before saving the form?

Thank you in advance for your guidance!

Best regards,

Tolga Guven

Categories:

Power Apps Pro Dev & ISV

Hi Tolga,

Regarding your question - Is it possible to use a related entity field (e.g., BankAccount.LastReconciledOn) in a Business Rule
No, Dataverse Business Rules cannot access fields from related tables, even in Many-to-One relationships. They operate only on the fields of the entity they are defined on.
You can use JavaScript + Form Context + Web API (Client-Side Validation)
Since Business Rules can’t do this, the best way to enforce your rule before saving is with a JavaScript function on the form that:
1. Listens to the Receipt Date and Bank Account fields.
2. Fetches the related Bank Account's Last Reconciled On date via a Web API call.
3. Compares the dates.
4. Cancels the save if the rule is violated.

Below is js code

function validateReceiptDate(executionContext) {
var formContext = executionContext.getFormContext();

var bankAccountLookup = formContext.getAttribute("crs_bankaccountid"); // Replace with your schema name
var receiptDate = formContext.getAttribute("crs_receiptdate").getValue(); // Replace with your schema name

if (!bankAccountLookup || !bankAccountLookup.getValue() || !receiptDate) {
return; // Required fields are not set yet
}

var bankAccountId = bankAccountLookup.getValue()[0].id.replace("{", "").replace("}", "");

Xrm.WebApi.retrieveRecord("crs_bankaccount", bankAccountId, "?$select=crs_lastreconciledon").then(
function success(result) {
var lastReconciledOn = result.crs_lastreconciledon;
if (lastReconciledOn && new Date(receiptDate) <= new Date(lastReconciledOn)) {
formContext.ui.setFormNotification("Receipt Date must be after the Bank Account's Last Reconciled On date.", "ERROR", "receipt_date_check");
// Optionally block save
formContext.data.entity.addOnSave(function (eContext) {
eContext.getEventArgs().preventDefault();
});
} else {
formContext.ui.clearFormNotification("receipt_date_check");
}
},
function error(error) {
console.error("Error retrieving Bank Account: ", error.message);
}
);
}

Replace crs_bankaccountid, crs_receiptdate, and crs_lastreconciledon with your actual field schema names.
In the form editor for the Other Receipts entity:
Add the JS Web Resource to the form.
Register validateReceiptDate on:
Receipt Date → OnChange
Bank Account → OnChange
Form OnSave → to enforce rule when user tries to save