I tried to upload the JSON Export but as I'm a newbie I am not permitted to upload files.
I am creating a Property Management application. I have created a number of supporting db tables and related apps to populate the supporting db tables. On the main form I want a couple of Multiselect components that will retrieve their options from supporting db tables. For instance, I have a db table called 'Accessed_by' which simply lists the different possible ways to access the property, e.g. Road, Canoe, Hike-In, Bike.
I have a db table called Property_registry that contains tombstone data for the properties including the properties' name, property_type, address, accessible_by, etc. I created the accessible_by field as JSONB based on some information I read. For the Property Registry App I have a table that lists the different properties. I was able to populate the table with 3 entries before I added the JSON fields to the db table and to the App. Under the App's table I have a 'ADD' button that opens a Modal with the Property Registry form. On the form I have the 'accessed_by' Multiselect component. I have it set to dynamically fetch the options using a SQL query that returns values of the 'id' and the 'means' fields from the 'Accessible_by' db table. The Multiselect component is configure to have the 'id' as the value and 'means' as the label. I have the submit form configured using the GUI and the changeset set to Object with {{ form1.data }}. If I put everything except the Accessible By the submit will run correctly and create a new entry in the Property Registry. But if I select anything in the Accessible By Multiselect component the submit fails and returns the following information returned in the Output:
insert into "Property_registry" ("accessed_by_3", "apt_no", "city", "country", "maintained_by", "name", "oversight_by", "possession_type", "postal_code", "primary_status", "province", "ref_no", "retention_status", "secondary_status", "street_name", "street_no", "title_holder", "type") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18) returning * - invalid input syntax for type json
-
- statusCode:422
- error:"Unprocessable Entity"
- message:"insert into "Property_registry" ("accessed_by_3", "apt_no", "city", "country", "maintained_by", "name", "oversight_by", "possession_type", "postal_code", "primary_status", "province", "ref_no", "retention_status", "secondary_status", "street_name", "street_no", "title_holder", "type") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18) returning * - invalid input syntax for type json"
- data:null
- isRetoolSystemError:false
queryExecutionMetadata:{} 5 keys
* estimatedResponseSizeBytes:563
* resourceTimeTakenMs:78
* isPreview:false
* resourceType:"retoolDb"
* lastReceivedFromResourceAt:1734797502187