When getting data from Retool DB it automatically sets checkboxes value = true, even though in the DB value = false

[Klearner]

Hello!

i'm having a strange issue that i can't seem to figure out. So Details of project:

I'm creating a PIM system using Forms with what i can insert product detail information into Retools database sheets. I'm using multiple (currently have configured only 2) datasheets. One is called "overview" the other is "duvet" - for each sheet i have created 1 form ("overview" & "duvet")- the form acts like a container for the input fields. Doing it this way i don't need to map the individual fields to submit the data into the DB sheets.

How i'm getting the data from the Databases.

1. I have a modal button that has a table component in which displays the data & necessary fields from "overview" DB table. This table is used to find the products based on the set primary key. Also, i have the search field ,where users can search by the primary key. The SQL query i'm using to display data for the table is :
   "select * from "overview" where ({{ !searchBar.value }} or cast("range_name" as text) ilike {{ '%' + searchBar.value + '%' }});" - works fine, data inserted into the "overview_form" fields correctly.

2. To get the required data from the "duvet" datasheet i'm using the following query: "select * from "duvet" where range_name = {{ collection_table.selectedRow.data.range_name }};"
   so this looks at the selected row in the modal component table & primary key and with that finds the value from the "duvet" DB table and inserts it into the "duvet" form.

3. In the Retool DB sheets, i have set the CheckBox values to be "boolean" & default value = False. Had the same problem for the "overview" form, as uploaded the table via csv & when selected "boolean" from the menu, it didn't set the deafult value to be = false, so added that manually and it fixed the problem. But, it didn't fix the problem for the "duvet" DB, which is why i'm confused now.

4. Clear form also doesn't clear the values in the "duvet_form"

So now the issue: everything works fine with "overview" database sheets - i can get the data just as i have inserted it into the "overview_form", but when getting the data from "duvet" DB into "duvet" form, it still fills in the Checkboxes automatically - Has anyone had also a problem like this, or does anyone know how to fix it?

Many thanks!

Added a new Retool DB sheet with CSV and the same thing happened that it automatically sets the Check Box value = True.
Img of state of checkbox:

DB:

[Kabirdas]

Hey @Klearner!

Would you mind posting a screenshot of the form settings and the checkbox settings as well? If the database is returning false but the checkbox is checked it may have to do with the component's configuration

[Klearner]

@Kabirdas -Thank you for the reply! Sure -> here are the screenshots:

also, the checkbox makes a textbox component read only if the checkboxes value = true.
this is achieved like this ->

What i also noticed that when i get the data from the database, i can't click the checkbox, seems like the checkbox is only "read only"

[Kabirdas]

Hmm... I'm not seeing how the checkbox gets its data from those screenshots since the "Default value" field is empty. It looks like its value right now, is an array:

So you might have to grab that first value using [0] somewhere. But I'm not sure where exactly that is and there might be a better syntax to use here. Would you mind sharing how the data for the checkbox is passed?

[Klearner]

Hi, @Kabirdas,

thank you for your help!

Noticed that that when i first load up the form, the checkbox value doesn't contain any lists ->

but when i load the data from the DB it changes the state to have list values (value = false, but the checkbox is checked and can't be unchecked.->

[Kabirdas]

Ah, interesting! How are you loading the data from the DB?

Edit: Want to post the solution we found here, others have run into similar issues as well. Because of how SQL queries return data if you query your database for a single row it will still return as an object where each key corresponds to an array representing the corresponding column. If you're only querying for one row, they're singleton arrays but still arrays which can cause issues with how the data is interpreted by certain components:

You might try formatting it differently using something like formatDataAsArray, specifically formatDataAsArray(YOUR_DATA)[0]. This can be done within query transformer or wherever you reference the data (as below):

Hopefully that's helpful for folks!