Multiselect options columns

[kpturner]

I have a Postgresql schema I am importing. In the schema I have several fields that have special types. For example,

Column: languages
Type: "Language"[]

Where the Language type can be one of EN, FR, DE, ES, IT, PL, PT, RO, RU, TR, UK

So this means that the languages column can contain one or many languages in a comma delimited list.

This seems to fit nicely with the multiselect column type in BB. However, when I come to edit the imported column in BB it does not give me the option of making it multiselect. It does present options as an option, but that won't fit the bill here. I only seem to be able to use the multiselect option if I add the column in BB.

How does one work around that?

[ConorWebb96]

Hey @kpturner,

You are correct in assuming that it should be structured this way. The reason why this doesn't work is due to it needing to be supported by all data sources. Yes, it would work for postgres, as it is supported but as there isn't inbuilt logic to accommodate the other data sources we've never added support for it. Since it is a data source plus, (meaning it can create crud functionality out of the box) any feature we add most be compatible with the others.

Now with all that being said, I do think it would be a good one to add to the core product. So I would suggest creating a feature request.

As you have said, currently the only workaround would be to create the multi-select field within Budibase itself. Then add your options within that field.

I hope this makes sense and is helpful.

[kpturner]

I am not totally sure how to work around this still. Can I create like a custom/virtual field for a table (that does not get updated on the source schema) and then map the data to the "real" field when I update/create records somehow?

[kpturner]

If I create the field in the BB UI then it will add that field to the source schema. I cannot do that. The schema must be the source of truth (it is generated from a tool called Prisma). I cannot omit the field and then add it via BB after I have imported it. It has to be a one way street. The schema cannot have any reliance on manual intervention should we regenerate it after making changes.

[kpturner]

Is it not possible to add a multiselect field to a screen (that does not belong to the schema at all), and then map it over to the real field on the schema when records are created/updated?

[kpturner]

Looking at it I think it is

[ConorWebb96]

Hey @kpturner,

I've had more in-depth look at this.

You might be able to use custom queries to return the data in a format that budibase can use. Or you can use custom queries to write directly to the field. Custom queries along with the execute query action should let you write to that data type. https://docs.budibase.com/docs/data-sources#tutorial-posting-form-data-to-a-custom-query. This along with a multi select with custom hardcoded options might be enough for you in this case. Definitely worth checking out. https://docs.budibase.com/docs/data-actions#execute-query

Additionally, I’ve found an issue related to to JSON support for Postgres/MySQL. Feel free to give this a bump as this would likely solve this issue if added. #3382

Hopefully this helps.

[kpturner]

Ok thanks will take a look