ERP Data Validation Rules: Types, Examples and Setup
ERP data validation rules are predefined checks that test a record against your business logic before the system accepts it. A good rule set stops a wrong customer ID, a missing tax code or an impossible quantity at the moment of entry, not weeks later during reconciliation. This guide covers the main rule types, practical examples for orders, purchase orders, inventory, customers and finance, and a step-by-step way to design, test and change rules without breaking production.
TL;DR
- Validation rules check data against criteria such as required fields, formats, ranges, uniqueness and links to master data before a record is posted.
- The most common types are completeness, format, range, uniqueness, referential integrity and cross-field (business logic) checks.
- Decide for each rule whether a failure should warn the user, block the record or route it to a reviewer.
- Test rules with real, messy data and repeat the tests every time a rule changes.
- Rules only protect what passes through them. Data that arrives by email, PDF or spreadsheet needs checking before it is keyed in, too.
Table of Contents
- What ERP data validation rules are
- The core types of validation rules
- Practical rule examples by record type
- How to design a rule set that works
- Testing rules and validating data during implementation
- Changing or clearing a validation rule safely
- The gap before the ERP: data that arrives by email
- See it on your own order flow
- FAQ
- Sources
What ERP data validation rules are
A validation rule is a condition your ERP evaluates when someone saves, imports or posts a record. If the condition fails, the system reacts in a way you configured. In SAP S/4HANA, for example, the Rule Manager checks data against validation rules before posting, and depending on the setup the user may continue after a message or must correct the error first.
Other platforms follow the same idea with different vocabulary. ERP.net separates System Business Rules and User Business Rules: the first are predefined for each entity, the second are custom rules that developers or administrators write for organization-specific needs. Rules are applied at the front end for immediate feedback and at the back end for security and integrity.
That split is worth copying in your own design. Front-end checks help the person typing. Back-end checks protect the database from imports, integrations and anything else that skips the screen.
Why a built-in ERP is not enough on its own
An ERP records transactions, and out of the box it often enforces only basic controls. One advisory firm argues that an ERP does not validate business logic or enforce consistency across entities unless you configure it to. Whether you agree fully or not, the practical lesson is the same: the rules you write are what turn a recording system into a controlled one.
The core types of validation rules
SAP’s Universal Model names five primary types of input validation: validation against master data, range validation, format validation, relation-driven validation and erroneous data tolerance. Broader data quality guides list more granular types. Here is a working set most ERP teams use.
| Rule type | What it tests | ERP example |
|---|---|---|
| Completeness | A required value is present | Sales order cannot be saved without a ship-to address |
| Format | The value has the right shape | Tax ID matches the expected pattern, dates use one format |
| Range | A number sits between a minimum and maximum | Order quantity is greater than zero and below a sanity ceiling |
| Uniqueness | No duplicate where one is not allowed | A supplier invoice number appears once per supplier |
| Referential integrity | The value exists in a parent or master table | Every order line references an existing item number |
| Cross-field | Two or more fields agree with each other | Delivery date is not earlier than order date |
| Business rule | A policy condition is met | Order total stays within the customer’s credit limit |
IBM’s overview describes range checks as testing whether numerical data falls within minimum and maximum values and uniqueness checks as requiring entries in designated columns to be unique. A guide from a testing vendor goes further and lists ten commonly applied types, including type, lookup, consistency and cross-system checks, with an example anyone in order management will recognize: every order record must reference an existing customer ID.
What happens when a rule fails
The rule is only half the design. The other half is the response. Failed records can be rejected, warned about or quarantined, and the right choice depends on the risk.
- Block when a bad value would corrupt downstream data, such as a missing item number or a customer that does not exist.
- Warn when a person may have a legitimate reason to continue, such as a price that is unusually far from the last one.
- Route to a reviewer when someone with authority must decide. Workday’s custom validations, for instance, can warn users, prevent submission or alert approvers, and each validation holds a transaction type, a validation type, an error message and condition rules.
Write the error message for the person who will read it. “Invalid value” costs a support ticket. “Item number not found in the item master, check for a typo or a discontinued part” does not.
Practical rule examples by record type
The examples below are starting points. Adapt thresholds to your own data and policies.
Sales orders
- Completeness: customer, ship-to, item, quantity, unit of measure and requested date are all present.
- Referential integrity: customer ID and every item number exist in master data.
- Range: quantity is positive, and the unit price is within an agreed tolerance of the price list.
- Cross-field: requested delivery date is on or after the order date.
- Business rule: a credit-limit check. One ERP development guide describes comparing the current balance, open orders and the current order against the credit limit before the record is inserted or modified.
Purchase orders and confirmations
- Supplier exists and is active.
- Quantity and price on a confirmation match the PO within a set tolerance.
- Confirmed delivery date does not fall outside the lead time you agreed.
- PO number is unique and not already closed.
We go deeper on these in our guide to validating purchase orders before ERP release.
Inventory and item master
- Item number follows your naming convention (format).
- Unit of measure comes from an approved list (lookup).
- Stock adjustments stay within a plausible range, with larger ones going to a reviewer.
- No two items share the same manufacturer part number (uniqueness).
Customer and supplier records
- Required fields: legal name, billing address, payment terms, tax status.
- Duplicate detection on name plus address or tax ID.
- Payment terms exist in the terms table.
- Bank details match the expected format before a payment run can use them.
Financial data
- Chart of accounts values are validated against the master.
- Debits equal credits before posting.
- Posting period is open.
- Mandatory cost center or project on specific account types.
The Onetribe article mentioned earlier argues that mid-market finance teams often depend on month-end reconciliation, by which point differences have compounded for a month. Its alternative is to catch problems at entry with range checks, duplicate detection, mandatory fields and account validation against the master. That is a company’s opinion, not a standard, but it matches how most controllers think about prevention.
How to design a rule set that works
The most common failure is not a wrong rule. One practitioner write-up notes that validation rules look simple until they behave differently in production, because the real data is messier than the test data. Design with that in mind.
- Start from the errors you already have. Pull the last few months of corrections, returned invoices, wrong shipments and manual fixes. Group them by field. The top three groups are your first rules.
- Write each rule in one plain sentence. “Order quantity must be a positive number and not exceed 10,000 units unless approved.” If you cannot say it simply, the rule is two rules.
- Assign an owner. Someone in the business, not only IT, decides whether a rule blocks or warns, and approves changes.
- Choose the response. Block, warn or route, as above, and write the message text.
- Pick the enforcement point. Screen-level for speed of feedback, back-end for integrity, and import-level for anything arriving from outside.
- Document it. Keep a register with the rule, its purpose, owner, response, date created and date of last change.
Treat repeated failures as a signal
If one data domain keeps failing validation, one ERP consultancy suggests it should trigger a root cause review and a governance refinement rather than another round of manual cleanup. If customer records fail the tax ID check every week, the fix may be at the intake form, not at the rule.
Testing rules and validating data during implementation
Rules need tests, and so does the data they protect. An academic paper presented at IACIS in 2021 recommends making data validation a top priority during the conversion phase. Its suggestions include comparing record counts from legacy tables to new tables, comparing legacy totals to new totals, and running end-to-end business processes in a test environment while checking the data at each step.
The same authors note that an organization will never get perfectly clean data from a conversion, and that errors from an inaccurate crosswalk are hard to find and usually surface only after go-live, when cleaning them is slow and costly. Plan for that reality: reconcile early, and keep your validation rules active after the cutover.
A simple test pack
- One passing record for each rule.
- One failing record for each rule, confirming the message and the response.
- Edge values: zero, the exact minimum, the exact maximum, a blank, a space, a very long string.
- Real examples pulled from past problems, anonymized if needed.
- A regression run after every rule change, so a new rule does not break one that worked. Developers of import tools recommend versioned releases and change logs with repeatable tests for the same reason.
Imports and migrations
Bulk loads deserve their own checks, because they bypass the screens where front-end rules live. Control totals matter most here: row counts and sums compared against the source can reveal missing or failed extracts. A staging layer helps too. The same Sage import guide recommends staging for normalizing casing, trimming whitespace and enriching data with lookup tables before data reaches the ERP.
If you are moving customer or operational data between systems, our partners’ piece on CRM data migration preparation covers cleanup steps that apply to ERP loads as well. For checking that data landed correctly after a move, this explainer on ETL versus reverse ETL helps you decide where verification should sit.
Oracle shows the same principle at product level. Its Redwood feature validates configured items on sales orders imported through a REST API, so errors surface during import rather than fulfillment.
Changing or clearing a validation rule safely
Rules age. Products change, tax rules change, a customer segment appears that your range never anticipated. Editing a rule is usually simple. In Zoho ERP, for example, you open Settings, the relevant module and its Validation Rules tab to edit or delete a rule. The menu path differs in every ERP, but the safety checklist does not.
- Check what depends on it. Integrations, imports and reports may assume the rule exists.
- Ask the rule’s owner. A rule that looks pointless may exist because of an audit finding.
- Prefer relaxing to deleting. Change a block to a warning first, and watch what happens.
- Test in a sandbox, then run your regression pack.
- Log the change in the rule register with the reason and date.
- Review the first week of production data for new failures or suspicious gaps.
Oracle’s EPM documentation notes that validation messages are generated when entered data violates rules, and that limits can be placed on submitted planning unit data with a designated reviewer, owner or notifier. Whoever holds that role in your system should sign off on changes.
The gap before the ERP: data that arrives by email
Rules inside the ERP only see a record once somebody creates it. For wholesalers, manufacturers and technical sales teams, much of that data starts in an inbox: a PDF purchase order, an Excel order list, a scanned confirmation, a free-text request. Someone reads it, retypes it and hopes the rules catch any slip.
That makes the keying step the weakest link. A validation rule can reject a wrong item number, but it cannot tell that the quantity should have been 120 instead of 12 if both are plausible. Better results come from validating the data as it is extracted, before it becomes an ERP record, using the same logic you would apply at entry:
- Match customer and item references against master data.
- Compare prices, quantities and dates with tolerances.
- Flag missing mandatory fields for a person to complete.
- Let a human approve the verified result before it posts.
If you want the mechanics, see how the email to ERP workflow works, how PDF data extraction to ERP is done, and how to handle an Excel attachment without retyping. For the approval step, our post on a one click approval workflow shows how verified data moves into the ERP.
Our perspective on validation
We think about validation as a chain, not a single gate. The ERP’s rules are the last link, and they are essential. But the earlier a check happens, the cheaper the fix, and the earliest moment is when an order lands in someone’s inbox.
That is why we built Ampwise to work where the work already happens, in Outlook and Gmail. It reads emails, PDFs, Excel files and scanned documents without templates, extracts the fields, and checks them before anything is entered. Staff see verified data and approve it with one click, instead of retyping it.
Our advice to any team is the same. Write down the rules your best clerk applies in their head: “this customer always orders in cartons”, “this price looks off”. Those unwritten checks are the most valuable rules you have, and they are the ones worth automating first.
Evert
See it on your own order flow
Ampwise AI automates extraction, validation and direct entry of orders, inquiries, invoices, PO confirmations and incoming goods from Outlook and Gmail into your ERP. It works without templates or changes to how your customers send documents, and it supports multiple ERP integrations. Our own aim is to reduce manual data entry by up to 90%, so your team spends its time on exceptions instead of keystrokes. Explore Ampwise AI and see how it fits your inbox-to-ERP process.
FAQ
What is data validation in simple terms?
It is a check that confirms data is acceptable before the system stores it. In an ERP, that means testing an entry against rules such as required fields, valid formats, allowed ranges and matching master records. If the data fails, the user is warned, blocked or sent to a reviewer.
How do I change or delete a validation rule in an ERP?
Most systems keep rules in a settings or configuration area tied to a module or object. In Zoho ERP, you go to Settings, the module and the Validation Rules tab. Before saving, check what integrations depend on the rule, test in a sandbox and record the change.
Which validation rules should I implement first?
Start with the errors that cost you the most: missing mandatory fields, references to records that do not exist, duplicates and out-of-range quantities or prices. These cover most day-to-day problems and are simple to explain to users. Add cross-field and business-policy rules once the basics are stable.
Should a failed validation block the record or only warn?
Block when the error would damage downstream data or financial integrity. Warn when a legitimate exception is plausible, and route to an approver when someone needs authority to decide. Review warnings regularly, because a warning that everyone ignores is not protecting anything.
Do validation rules cover data that arrives by email?
Only after someone enters it. Orders and confirmations sent as PDFs, spreadsheets or free text need to be extracted and checked first, ideally with the same logic your ERP uses. Doing that before entry catches errors while they are still cheap to fix.
Sources
- Understanding the Basics of Validation and Substitution, SAP Learning
- Data Validation and Business Rules, ERP.net Developer Docs
- Why Your ERP Doesn’t Govern Your Data, Onetribe Advisory
- Inputting Validation Options, SAP Learning
- What Is Data Validation?, IBM
- What is data validation? Methods and examples, Tricentis
- What is data validation? Types, rules and techniques, Orbit Analytics
- Data Integrity Tools, Workday Documentation
- How to Link Multiple Tables for Complex Multi-Table Validation
- How to Build a Data Governance Framework for Your ERP, KPCTeam
- Understanding success factors for ERP implementation, IACIS 2021
- Sage Import Templates for Developers
- Redwood: Validate Configured Items on Sales Orders, Oracle
- Creating and Updating Data Validation Rules, Oracle EPM
- Validation Rules, Zoho ERP Help
