Quality Control with Custom Data Rules in QuickbaseJan 27, 2020
What is a database without data integrity! Ensure the quality of data entered into your Quickbase apps using Custom Data Rules. These rules apply no matter what form is used or even if Grid Edit is used.
In this tutorial, I walk you step-by-step how to set up custom data rules for any table in Quickbase. I break down the formula details for:
- A single data rule
- Multiple data rules
- Combined data rules
Adding Custom Data Rules is simple, once you know the steps:
- Open the tables Advanced Settings
- Check the box to "Turn custom data rules on?"
- Enter your "If" statement formulas with the condition & message
It can feel a bit backward, so be sure to pay close attention to how those "If" statements are utilized in the Custom Data Rule formula!
RECOMMENDED TUTORIAL: Error-proof data entry with required fields
RESOURCES: Formula from the video
If(([Finish Date]<ToDate([Start Date])) and ([# of Hours Allocating]<1),"The Finish Date cannot be before the Start Date AND A minimum of 1 hour must be allocated", [Finish Date]<ToDate([Start Date]),"The Finish Date cannot be before the Start Date", [# of Hours Allocating]<1,"A minimum of 1 hour must be allocated")
See the video transcript
FROM THE TUTORIAL: "...this is where I want to create our custom data rules these rules allow me to set up my very own error messages which means that when something isn't quite right. I can display to my user exactly what the problem is, so let's say I'm having an issue with people entering a finish date before the start date. Clearly this would be an issue but without some sort of data rules or validation in place this could go on unchecked for who knows how long! There is an option to potentially add some form rules but that only would apply to that particular form when it's being used, not when something like Grid Edit is being used, so what we want to do is ensure that even when Grid Edit is being used, that these rules are being followed. To write our custom data rules we're gonna jump into the settings for the table. Once in our settings, we'll scroll all the way down to our Advanced Settings. Once again, scroll down to the advanced table settings section and you'll see the option to build custom data rules for this table. And there's also the spot where you can turn them on or turn them off, so even if you have them written you don't necessarily have to have them turned on or engaged until you are ready. We're gonna go ahead and check that box because you will be ready as soon as we get this going so for a very first rule! I'm just going to pop in my formula. Every formula you use in here is going to be your If formula so If This, Then That. Basically what we're saying is if this condition is true then there is an error, and I want you to display this error message, otherwise let things be. If there's no error, no problem, let the record be saved. So now I've got my if statement right starting with my If and then within the parentheses. I've got to have my condition that I want to check for so now I've got my finish date right my field for finish date less than my start date. Now it just so happens that the start date is of a particular format called work date so I need to convert it. You may or may not need to make that type of conversion to date but essentially we're saying if the finish date is earlier than the start date we got a problem, if that is true then I want to display this message to my users, stating that the finish date cannot be before the start date. That way they know where to focus and what they need to fix specifically. All right, let's go ahead and save this. Exit back out to our report, and because one of the great things about these custom data rules is that they will apply even when you are in Grid Edit. I'm gonna pop into Grid Edit because it'll make making updates and changes really easy, really fast. I'm just gonna set a finish date to before the start date. You notice the dates in here are really, really old that's because again this app came from the app exchange and it was written quite a while ago but it really helps to demonstrate a lot of these use cases. I'm gonna move that before the start date and try to save it's not letting me save and it's showing me why. Exactly that message that we gave saying the finish date cannot be before the start date. How awesome is that! Now I want to show you a few more things we're going to jump out of here and go back into our advanced settings..."
Feeling like a Junkie? Subscribe Now!