Save New Row in Form on Postgres Data Source

[jhill-mindex]

Checklist

• I have searched budibase discussions and github issues to check if my issue already exists

Similar issue here but much earlier version:
#10142

Hosting

• Cloud
  • Tenant ID: comida

Describe the bug
When I go to save a new row in my Postgres DB, nothing pops up on screen for an error but the row isn't inserted. I found in the developer tool logs the following Request payload:

{
"apApplicantName": "XXXXX",
"apAddress": "XXXXX",
"apCity": "XXXXX",
"apState": "XXXXX",
"apZIP": "XXXXX",
"__value": {
"apApplicantName": "XXXXX",
"apAddress": "XXXXX",
"apCity": "XXXXX",
"apState": "XXXXX",
"apZIP": "XXXXX"
},
"__valid": true,
"__currentStep": 1,
"__currentStepValid": true,
"tableId": "datasource_plus_b623bc1531d74eaa9836268104314ca6__tblapplicant"
}

with the following Response Payload:

{"status":500,"validationErrors":{"apTaxMapID":["can't be blank"]}}

When I check my Postgres table to see if apTaxMapID is not nullable it only shows that the applicantID column is not nullable, it doesn't show anything about apTaxMapID.

This was working previously and nothing was changed on my side.

Expected behavior
I would expect the save button to insert a new row into Postgres as it was previously.

Desktop (please complete the following information):

• OS: macOS Sequoia Version 15.5
• Browser Chrome
• Version 137.0.7151.120

Additional context
I was able to insert a row into Postgres directly with an INSERT statement even with a NULL apTaxMapID column

[ConorWebb96]

Hey @jhill-mindex,

Is this set as required within the Budibase table UI itself?

If yes, toggle off it being required and it should resolve your issue.

[jhill-mindex]

Hi Conor, thanks for the response. I'm not using a table, I'm using a form. The strange part is that the field the error message is referring to (apTaxMapID) doesn't even exist in my form at all.

Please let me know if I can provide any other details to help troubleshoot further.

[ConorWebb96]

Hey @jhill-mindex,

Yes, even if it's not included in the form. If it's required at the table level, the request will still fail as the field can't be left blank for that table because it is required.

Can you check the base table in the data area and see if the field apTaxMapID is required? If it is, you can uncheck it, and you should be able to submit your form again.

[jhill-mindex]

So I can see that column is required but I can't toggle it off.

The primary key is a different column (applicantID)

However, I do believe at one point I changed the primary key column from apTaxMapID to applicantID. I just did a fetch table again to refresh it, but I'm still not able to toggle the required off.

I appreciate the quick responses, let me know if you need more info.

[ConorWebb96]

Hey @jhill-mindex,

Have you made any changes to your database? Was this field not nullable at one point, and then was it changed? If it's auto-detected as required, that likely means it's required/not nullable at the database level.

Do you have your Postgres schema? Please send across an example so I can have a look.

I'm going to convert this to a discussion as it's very likely to be schema-related.

[jhill-mindex]

@ConorWebb96 just sent email to support@budibase.com

[ConorWebb96]

Hey @jhill-mindex,

Have you tried connecting to the same database in a new app to see if the required rule is still being pulled across?

Did you get a chance to upload that app export to the Dropbox link sent to you?

[jhill-mindex]

Hi @ConorWebb96 I did try connecting the DB to a new app and the required rule was still intact. Yes I did upload my exported app to dropbox yesterday morning. I now have a workaround thanks to @abadedgar113 but would still be worth figuring out why it's not working as expected so that I don't need to write custom queries in the future. Thanks!

[ConorWebb96]

Hey @jhill-mindex,

My mistake, I didn't get a notification. I can see your app export now. Thank you for uploading the app export.

I understand why it's required in the Budibase UI. This field is marked as the primary display column. These columns are necessary for Budibase as they are the identifiers used when setting up your relationship columns within Budibase. Due to this they are required by default.

If you switch the primary display to another field that is already required in your database, you should then be able to toggle off the required flag for that specific field.

Screenshot showing the use as display column option

Switch to a different column can toggle it off, so it's no longer required.

I hope this helps.

[jhill-mindex]

This worked, thank you very much!

[abadedgar113]

Hello @jhill-mindex,

Are you using pgadmin in accessing your postgres db or only in terminal? if you are using terminal, can you connect on it and type "\d tablename" (table name which has a column of apTaxMapID)?

Here is an example if you type it in the terminal

You can check if there other nullable column in that table.

[jhill-mindex]

Nope

[abadedgar113]

One solution for saving new rows in forms is to use custom queries. I also employ this method for creating, reading, updating, and deleting records. I have noticed that when fetching tables from PostreSQL to budibase, any modifications made to the tables require me to update other components of my application that rely on those tables.

[jhill-mindex]

@abadedgar113 thanks for the response! I'm good to implement this method but having trouble getting my custom query to bind correctly to the form. Here is the custom query:

Is this the right syntax to bind to the form value? {{[Create Applicant Form].Value.apApplicantName}}

On the save button I'm just doing this:

Am I missing something?

Thank you very much!

[abadedgar113]

Hello @jhill-mindex

You can use my query as a reference:

And here is in my save button:

in the bindings of execute query action, just select the text field or option picker on your forms.

[jhill-mindex]

That worked! Thank you very much @abadedgar113 ! Appreciate it greatly.