Required Fields Skipped by a Custom Submit Button in Quickbase? Enforce Them With a Custom Data Rule

A question in the Quickbase community asked why required fields stop working on a custom Submit button (original thread here). The asker had a Payment Requests table with a rich text button on the form. While a request is New, the button says Submit. Clicking it runs an API_EditRecord URL that sets Payment Request Status to "Pending FP&A Approval" and then redirects to a page that closes the popup. The required fields were checked on the Add form, but on the Edit form people could submit with them still empty.

The fix is to stop relying on the form for this rule. Put it on the table as a custom data rule. Custom data rules run on every add and edit, whether it comes from a form, grid edit, import, pipeline or an API call, so the Submit button can't get around them.

I rebuilt the table in a sandbox app to check the pieces I could test through the API. One of those tests found a problem with the formula as it's usually written, so read Step 2 before you copy anything.

Why the form's required setting doesn't catch it

Marking a field required on a form is a rule about that form. Quickbase checks it when someone saves that form, and nowhere else.

The Submit button doesn't save the form. It's a link to API_EditRecord, which is a separate request that changes the record directly. That request has no idea what the form says is required. It only changes the fields in the URL (status and a couple of others), so a blank Cost Center isn't touched and nothing complains.

In my sandbox I made a request with no Cost Center, then ran the same kind of URL:

https://<your realm>.quickbase.com/db/<table id>?a=API_EditRecord&rid=1&_fid_7=Pending%20FP%26A%20Approval

Quickbase answered with <errcode>0</errcode> and <num_fields_changed>1</num_fields_changed>. The request moved to Pending FP&A Approval with Cost Center still empty.

Why not make the field required on the table?

You can mark a field required in its field properties, but that doesn't fit here:

• It's all-or-nothing. Cost Center would be required even while the request is still New and someone is just saving a draft.
• Quickbase won't let you turn it on while existing records have that field empty. When I tried on the sandbox table, the API refused with "field contains null values."

What you want is "Cost Center is required when the status becomes Pending FP&A Approval." A custom data rule can say exactly that.

Step 1: Open the table's custom data rules

Custom data rules are set per table on the table's Advanced settings page:

1. Open the Payment Requests table and go to its Settings.
2. Choose Advanced settings.
3. Find the Custom data rules section. This is where you enter the rule formula and turn rules on or off.

The rule is a normal Quickbase formula. If the record is fine, the formula returns nothing. If it isn't, it returns text, and that text is the error message. Messages must be text in quotes.

Step 2: Write the rule (watch out for IsNull on text fields)

Here's the version you'll often see suggested:

If([Payment Request Status] = "Pending FP&A Approval" and IsNull([Cost Center]),
  "Cost Center is required before you submit.")

Before relying on it, I put the same formula into a test formula field on the sandbox table, so I could see what it returns for each record. For a request with status Pending FP&A Approval and an empty Cost Center, it returned nothing. The rule would never fire.

The reason: IsNull() on a Text field came back false even when the field was empty. An empty text field holds empty text, not null. So for a text field, check for empty text instead:

If(
  [Payment Request Status] = "Pending FP&A Approval" and Trim([Cost Center]) = "",
  "Cost Center is required before you submit. Add a Cost Center, save, then click Submit again."
)

With Trim([Cost Center]) = "", my test field returned the message for the blank request and nothing for the others:

Trim() also catches a value that's only spaces.

If the required field is a number or currency field, IsNull() has its own catch. Numeric fields have a "treat blank as zero" setting that's on by default, and while it's on, a blank amount reads as 0 in formulas, so IsNull([Amount]) came back false in my sandbox. When I turned that setting off for the field, IsNull([Amount]) returned true for the blank record. So either turn the setting off for that field or test the field for zero instead.

If Cost Center is a multiple-choice text field or a lookup, the same text check works. If it's a reference to a Cost Centers table, check the reference field itself (it's numeric), and remember the blank-as-zero point above.

Paste your version into the custom data rules box, make sure the rule is on, and save.

Why the status check matters

The rule only fires when the status is Pending FP&A Approval. That gives you the behavior the asker wanted:

• While a request is New, people can save it with gaps, as a draft.
• The Submit button sets the status to Pending FP&A Approval. That edit has to pass the rule, so a missing Cost Center blocks the whole edit and the status stays New.
• Once a request is already pending, any later edit also has to keep Cost Center filled in. Nobody can blank it out afterward.

You don't need to change the Save button at all.

Checking more than one field

If() can take several condition and message pairs, and it returns the first one that matches:

If(
  [Payment Request Status] = "Pending FP&A Approval" and Trim([Cost Center]) = "",
  "Cost Center is required before you submit.",
  [Payment Request Status] = "Pending FP&A Approval" and [Amount] <= 0,
  "Enter the amount before you submit."
)

People then fix one thing at a time. If you want every problem listed at once, Quickbase's help on custom data rules shows how to build several messages for one record with variables.

Step 3: Test it like a user would

Use a test record before telling everyone it's done:

1. Create a request (say, Sarah Lee's "Office chairs" request) and leave Cost Center blank. Save it. It should save, because it's still New.
2. Click Submit. The edit should be rejected and the status should stay New.
3. Add a Cost Center, save, and click Submit again. It should go through to Pending FP&A Approval.
4. Try a grid edit or an import that sets a blank-Cost-Center request to Pending FP&A Approval. That should be blocked too.

Two notes from Quickbase's help on custom data rules:

• Rules only check records being added or changed. Existing records that already break the rule stay as they are until someone edits them, and then the edit has to pass.
• Data rule errors show up after the usual errors (required, unique, max length). If a save is already failing for one of those, you'll see that first.

Step 4: Make the error clear, and avoid it when you can

When a form save fails a rule, the message appears on the form. When the Submit button's API_EditRecord call fails, the message comes back in the API response instead. The page in rdr isn't where people end up, because the edit didn't succeed. Instead they see Quickbase's error response with your message in it ("Custom data rule failed:" followed by your text). That's why the message should say what's missing and what to do next, not just "Validation failed."

You can also stop most people from hitting the error at all. Have the button check the same thing and show a short note instead of the Submit button when something's missing. Here's a cut-down version of the asker's button formula with that check added (field 23 is the status field, and the styles are left out to keep it short):

var text closewindow = URLRoot() & "db/" & AppID() & "?a=dbpage&pageid=11";

var text Submit = URLRoot() & "db/" & Dbid()
  & "?a=API_EditRecord&rid=" & [Record ID#]
  & "&_fid_23=" & URLEncode("Pending FP&A Approval")
  & "&rdr=" & URLEncode($closewindow);

If([Payment Request Status] = "New",
  If(Trim([Cost Center]) = "",
    "<span style='color:#b91c1c;font-weight:bold'>Add a Cost Center and save to enable Submit.</span>",
    "<a class='SaveBeforeNavigating' href='" & $Submit & "'>Submit</a>"
  )
)

Keep two things in mind:

• The button is calculated from the saved record. If someone types a Cost Center on the form but hasn't saved yet, the button still shows the note until they save. That's why the note says "and save."
• The button check is a convenience, not the guard. Someone can still change the status in grid edit, an import, a pipeline or their own API call. The custom data rule is what actually enforces it, so keep both.

Summary

• Form-level required fields only apply when that form is saved. A Submit button that calls API_EditRecord skips them.
• Put the rule on the table: Settings > Advanced settings > Custom data rules. It runs on forms, grid edits, imports, pipelines and API calls.
• Make the rule depend on the status, so drafts still save and only the submit step needs Cost Center.
• For text fields, test Trim([Field]) = "" rather than IsNull([Field]). For numeric fields, watch the blank-as-zero setting.
• Write a clear message, since button users see it in the API response, and have the button show a note when the record isn't ready.