File attachment fields: restrict file types
Many companies use file attachment fields to store things like photos of their products or customer contracts. Wouldn’t it be nice to control what types of files users are allowed to upload? Read on to learn how you can use custom data rules to restrict which types of files are can be uploaded.
What are custom data rules?
Custom Data Rules allow app builders to create business logic that can be applied anytime someone adds or updates data in a Quickbase table. Whether a user is updating a few rows in Grid Edit or uploading new records from a spreadsheet, adding a Custom Data Rule allows administrators to automatically validate changes to data and ensure that everything is in the right format.
To create custom data rules, you write a conditional formula which lists all the types of invalid data you want to disallow and a matching error message for each type of invalid data. To get started, open the table where you want to add custom data rules, then click the gear icon. Next, choose Advanced Settings and check the box labeled “Turn custom data rules on?”. Finally, enter your formula and then click Save at the top of the page.
NOTE: You could use form rules for this as well, but it’s now a best practice to avoid using form rules for data validation. The reason is, form rules only apply when users enter data one record at a time using a form. Custom data rules, on the other hand, are comprehensive. They apply anywhere data could be entered, like grid edit, via pipelines or API calls, imports, etc. Form rules are still helpful for displaying fields/sections conditionally, but you should no longer use them for validating data.
Learn more about custom data rules here.
Example custom data rules for restricting field types
Enter formulas like the ones below as a custom data rule for your table, so you can restrict what types of files may be uploaded in a specific field.
|If you want to...
||Use this formula
|Restrict a contract field to only accept PDFs.
var Text fileType = Right(ToText([Customer Contract]),".");
If($fileType != "pdf" and Length([Customer Contract]) > 0,
"Can only upload PDF files to the Customer Contract field.")
|Restrict a photo field to only accept JPGs, JPEGs, and PNGs.
var Text fileType = Right(ToText([Photo]),".");
If($fileType != "png" and $fileType != "jpg" and $fileType != "jpeg" and Length([Photo]) > 0,
"Can only upload JPG, JPEG, or PNG files to the Photo field.")
|Restrict any attachment field so it does not accept files of potentially unsafe types such as EXE or JS.
var Text fileType = Right(ToText([Attachment]),".");
If($fileType = "exe" or $fileType = "js",
"Cannot upload unsafe types of files like EXE or JS.")
Each example above restricts what type of file may be uploaded for a single file attachment field. If your table includes several file attachment fields which should restrict file type, you can expand your conditional formula to cover that. For instance, an HR on-boarding app may have fields for a resume, W9 form, and background check. If you want to ensure all files upload to all three of those fields are PDFs, your formula will look like this:var Text fileTypeResume = Right(ToText([Resume]),".");
var Text fileTypeWNine = Right(ToText([W9]),".");
var Text fileTypeBackgroundCheck = Right(ToText([Background Check]),".");
var Text checkResume = If($fileTypeResume != "pdf" and Length([Resume]) > 0,
"Resume: Can only upload PDF files to this field.");
var Text checkWNine = If($fileTypeWNine != "pdf" and Length([W9]) > 0,
"W9: Can only upload PDF files to this field.");
var Text checkBackgroundCheck = If($fileTypeBackgroundCheck != "pdf" and Length([Background Check]) > 0,
"Background Check: Can only upload PDF files to this field.");
List(" ", $checkResume, $checkWNine, $checkBackgroundCheck)
When writing your error messages, be specific. For instance, if your custom data rules cover multiple fields then your error messages should let the user know which field they need to fix.