What is a database without data integrity! Ensure the quality of data entered into your Quick Base apps using Custom Data Rules. These rules apply no matter what form is used or even if Grid Edit is used.
In this video, I walk you step-by-step how to set up custom data rules for any table in Quick Base. I break down the formula details for:
Adding Custom Data Rules is simple, once you know the steps:
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!
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")
FROM THE VIDEO: "...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 so 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 so 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 the then this 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 there's no error right no problem let the record be save so now I've got my if statement right starting with my I F 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 then 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 and I'm just gonna set a start date to be be full or sorry a finish date to be 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 so 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 right 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 on YouTube now!
I'm always interested in hearing from you about what you're working on and what you would like to learn more about.
Tips, shortcuts, checklists, reviews, tutorials, and courses from Quick Base Junkie!
Don't worry, your information will not be shared.
Take your Quick Base skills to the next level with Premium Courses.