🤓Xplan Hint: Validate data with Regex

Did you know you can add data validations to text fields?

Regular Expressions, often shortened to Regex, are incredibly powerful sequences of characters that define a search pattern. When used for data validation, they act like a strict quality controller for your text fields! You can use Regex to enforce specific formats, significantly improving the accuracy and consistency of your data.

Why Use Regex for Data Validation?

Regex is invaluable for:

• Enforcing Data Formats: Ensure that information entered into text fields (like email addresses, phone numbers, or ABNs) strictly conforms to a predefined structure.
• Improving Data Quality: Drastically reduce errors and inconsistencies, making your data more reliable for reporting and analysis.
• Automating Checks: Automatically prevent entries that don't meet your specified criteria, saving you manual correction time.

How Does it Work?

Data validation rules apply a Regex pattern to a string field to check if the field's content matches the specified pattern.

Here are some Regex patterns you can use for validating data:

1. Validating a 4-Digit Postcode:
• Pattern: 

  ^\d{4}$

• Use Case: Validate a 'Postcode' field to ensure it's exactly four digits (e.g., 4000).
• Explanation: ^ (start of string), \d matches any digit (0-9), {4} specifies exactly four occurrences, $ (end of string).
2. Validating an Australian Business Number (ABN - 11 digits):
• Pattern:

  ^\d{11}$

• Use Case: Ensure an 'ABN' field contains exactly 11 digits (e.g., 12345678901).
• Explanation: ^ (start of string), \d{11} (exactly 11 digits), $ (end of string).
3. Validating an Email Address:
• Pattern: 

  ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

• Use Case: Apply this to the Contact|Detail field to ensure entries are valid email formats (e.g., name@domain.com).
• Explanation: This pattern checks for a sequence of alphanumeric characters, periods, underscores, percent signs, pluses, or hyphens, followed by an '@' symbol, then another sequence for the domain, a literal dot \., and finally at least two letters for the top-level domain. The ^ signifies the start of the string, and $ signifies the end, ensuring the entire string matches the pattern.
4. Validating an Australian Mobile Phone Number:
• Pattern: 

  ^(04)\d{8}$

• Use Case: Ensure a 'Mobile Number' field only accepts standard 10-digit Australian mobile numbers starting with '04' (e.g., 0412345678).
• Explanation: ^ (start of string), (04) (must start with "04"), \d{8} (followed by exactly 8 digits), $ (end of string).

You may have noticed that mobile number and email address are entered into the same field Contact| Detail.  This field is used for all number and address inputs in the contact group, eg home phone, sms email, home/work/other email.  Good news, you can combine Regex validations to get the desired result.

• • Pattern:

    ^(?:[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}|(04)\d{8})$

  • Use Case: This will accept values for both the email and mobile numbers as above.  You may need to expand this further if you are capturing more types of data.

Practical Application: 

Let's build validation for the contact group where you have email, mobiles and land lines. 

Step 1: Summarise examples of the data you want to validate.  Ensure your format is consistent.

Step 2: Use AI to generate the Regex code

Step 3: Copy the code provided

Step 4: In Xplan field definitions, go to the Contact group and Detail field

Step 5: Paste your validation code, enter your fail message

Step 6: Test, test and test some more.  Ensure you check values that should AND shouldn’t pass.  Use the Test value field and Test button.

You will get one of the following messages:

Step 7: Once all your positive and negative tests have passed hit Save.

Benefits you'll see

• Higher Data Accuracy: Get cleaner, more reliable data from the moment it's entered.
• Reduced Manual Effort: Spend less time correcting formatting mistakes.
• Consistent Reporting: Build reports with confidence, knowing your underlying data is consistent.

By leveraging these Regex patterns, you can significantly enhance the data integrity within Xplan!