Skip to main content
Question

Exception for Data Validation Rule

  • October 31, 2019
  • 0 replies
  • 42 views
I'm working between two tables: Jobs and Price Increases. One job can have many price increases. I'm using a data validation rule to not allow a Price Increase to be added if the value in the Increase Effective Date matches a summary field that looks at the parent Job and pulls in the Maximum Increase Effective Date (one job should not have more than one price increase scheduled for one day).

This works great when adding new records. But if I try to edit and then save the Price Increase record that has the Maximum Increase Effective Date, I get my data validation error (As I should, the two date fields match).
Any ideas of how can I set an exception to the data validation rule?

This is the rule: If([Rate Effective Date]=[Service - Maximum Effective Date], "error message".

Thanks!

------------------------------
Heather
------------------------------
This topic has been closed for replies.

AdamKeever1
Forum|alt.badge.img+2
  • AdamKeever1
  • Quickbase Alumni
  • November 1, 2019
Try using a separate form for editing and do not include the [Rate Effective Date] field. You can assign which form opens for which type of action view, edit, add, grid edit:


------------------------------
Adam Keever
------------------------------
BrianCafferelli
Forum|alt.badge.img+17
Hi Heather, you can add the exception by taking advantage of the fact that record ID#s are generated when a new record is saved, but not before that time. So when you first add a record, before saving, Record ID# is blank. But if you come back later and edit the record, then Record ID# has a value. So you can tweak your custom data rule to check whether record ID# is null, to add the exception you're looking for:

If([Rate Effective Date]=[Service - Maximum Effective Date] and IsNull([Record ID#]) = true, "Can only have one price increase scheduled per day")

------------------------------
Brian Cafferelli
------------------------------
Brian Cafferelli Product Marketing Manager | Quickbase
Terms & ConditionsAccessibility statement