Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

Simple Solutions: How To Require a File Type for Attachments

file attachments formulas how to intermediate simple solutions Jan 13, 2022

It's not uncommon to have an attachment field included in your Quickbase apps.

But what do you do when you need users to upload SPECIFIC file types into that field?

The Attachment field does not have options to set what file types are acceptable, so how is this possible?

Custom Data Rules + Text Functions!

Custom Data Rules are useful for all sorts of data validation, but this one can be a bit tricky.

Access Custom Data Rules in the tables' Advanced Settings.

Below is a simple Custom Data Rule example to require the file to be a PDF.

In this formula, I use the Part function to extract the file type from the attachment and compare it to the value I want it to be "pdf".

Learn more about functions like Part, Lower, and If.

If it doesn't match, the message "The file type is not a "PDF". Please upload a PDF file." will appear when the user tries to save the record (and the save is aborted).

TIP: Add a note to your form to indicate the required file type. Use HTML to make it pop!
TIP: Many browsers allow you to drag and drop files into the "Choose File" button.

Caution

Make sure to check the box to "Turn custom data rules on" just above the formula when you're ready to test or implement.
Some attachments may have multiple file types that are basically the same type. Such as .ppt and .pptx or .jpg and .jpeg. Be sure to account for these variations if needed.
Example: Lower(Part([File],2,"."))<>"ppt" or Lower(Part([File],2,"."))<>"pptx"
If adding this Custom Data Rule to an existing table that has attachments that do not match the file type now being required, the user will see the error if they attempt to make and save ANY change to the record containing the non-conforming file. This may be a good thing, but if it's not here is another option.
OPTION: Add an additional condition to the if statement that the record must be created on or after the date you added the rule, such as: and ToDate([Date Created])>=Todate("MM-DD-YYYY")
When referring to the file type in the error message and you want to include it in a set of quotations (as in the example), be sure to escape the quotations with a preceding backslash \ (as shown in the example).
The formulas below adjust the file name/type to all lowercase. In the formula be sure to only use lowercase file types after the != (not equal) sign.

Copy/Paste Formulas

Replace the [File] in each formula with your attachment field and update references to the file type to the type you require.

Simple

If(Lower(Part([File],2,"."))!="pdf","The file type is not a \"PDF\". Please upload a PDF file.")

Enforce After a Specific Date

If(Lower(Part([File],2,"."))!="pdf" and ToDate([Date Created])>=Todate("01-13-2022"),"The file type is not a \"PDF\". Please upload a PDF file.")

Allow Multiple File Types

If(Lower(Part([File],2,"."))!="csv" and Lower(Part([File],2,"."))!="xls" and Lower(Part([File],2,"."))!="xlsx","The file is not CSV or Excel. Please upload a CSV or Excel file.")

Include Multiple Attachment Fields

list("<br>",
If(Lower(Part([File 1],2,"."))!="pdf","File 1 type is not a \"PDF\". Please upload a PDF file."),
If(Lower(Part([File 2],2,"."))!="pdf","File 2 type is not a \"PDF\". Please upload a PDF file."),
If(Lower(Part([File 3],2,"."))!="pdf","File 3 type is not a \"PDF\". Please upload a PDF file."))