Importing to an Option Set datatype via CSV

harris · 2020-09-08T12:04:28.391Z

Importing via CSV is working for me… Except when it comes to an Option Set datatype. When I remove that column in the CSV, everything imports fine.

Here’s a look at the datatype (“DealStage”) associated with the Option Set:

image1474×78 7.85 KB

Here’s what the Option Set looks like.

image1512×968 81.1 KB

When the import is unsuccessful, this is the error I get:

image900×228 14.2 KB

One thing I’ll note: The name in the database is outdated, as I was working on things they got re-named. So I believe in the database it’s called option.opportunity_status but forward-facing, it’s DealStage.

Oh one other thing: I tried uploading by appending option. onto the beginning of the values in the CSV but that didn’t work. So for example, option.Discovery.

Any thoughts? Am I missing something here? Thanks!

harris · 2020-09-11T01:02:37.616Z

Haven’t heard anything yet, hopefully y’all don’t mind but I’m tagging a couple folks I’ve found to be incredibly helpful building my app to this point! I hope this isn’t poor etiquette. Cc @romanmg @nikolai

ed727 · 2020-09-11T12:05:33.791Z

FWIW I was able to import a CSV datatype that was linking to an option set. It was a few months ago so I don’t recall the exact process, but I seem to recall a few things:

Option set has to already be created in Bubble. It needs to contain every single option that the CSV uses in whatever column you’re linking to the option set.
Setting up the link so that the CSV column that is the option set knows which field to look for in the Bubble option set to establish the link.
If Bubble is uploading and hits an instance where a CSV thing’s option is not in the option set in Bubble, then the upload (or maybe it’s the pre-upload check) stops.

harris · 2020-09-11T17:16:40.084Z

thanks @ed727! so, i changed the name of the field along the way and i’m wondering if that’s creating some issues.

when i export/download the spreadsheet the field is called status_option_opportunity_status

i’ve tried re-uploading with that name for the column but it doesn’t work. so i think the breakdown is that the importer doesn’t know which column to map the data.

you mentioned:

ed727:

Setting up the link so that the CSV column that is the option set knows which field to look for in the Bubble option set to establish the link.

is there a specific step/way to ensure this happens?

ed727 · 2020-09-11T19:56:38.399Z

Hmmm… I just ran a test that worked for me. It was a simple upload with just an entity name, and a Type. The Type is an option set I have set up in Bubble. Here’s what I did:

Made sure my upload file was saved as CSV. Here’s the screenshot. I just had one record in the test “AAATester”, and its function was “Database”.

Mapped the fields (step #1 on the Bubble upload page – see below)

image859×560 18.9 KB

As I noted, if you want to upload something that links to something else (in this case, the Entity links to the option set Type), then that option set Type has to contain every option that the Entity you are uploading may have. Bubble won’t make them for you.
Upload was successful. Went to the Bubble database and the new entry was there.

A couple of other things I’m remembering…

A “best practice” I figured out when uploading a bunch of data with lots of fields was to name the fields on the spreadsheet (ie. the header row 1) with the exact same name as what I had in the Bubble database. It’s not required since you control what field maps to what, but it made a it a lot easier to connect everything and avoid mistakes.
If the option set field in your database is a list, I think I had to bracket the items and also tell Bubble what delimiter to use.

Basically followed the steps Bubble laid out… Introduction - Bubble Docs

harris · 2020-09-11T20:24:13.744Z

Ahh okay so I think I realize the miscommunication here.

I want users to be able to upload data themselves via CSV from the front end.

Here’s the design:

Here’s the workflow:

image1014×740 67 KB

It’s in this instance where I’m running into this issue with options. And I’ve tried different column headers to get it to accept/auto-match the upload, but that’s where it’s struggling. It says Column headers should match the fields’ captions but that’s where things are getting snagged.

For testing purposes, I’m going to try the back-end data importer here. First, for reference here’s my spreadsheet:

image2330×378 44.5 KB

First, it automatically recognizes and matches it:

image2180×1514 162 KB

Then Validate works, it gives me a confirmation message:

image2180×1514 174 KB

Then finally, it does successfully upload:

image2180×1514 126 KB

Weird… Right? It seems like there’s some difference in the logic between the back-end admin-generated upload and the front-end user-generated upload.

ed727 · 2020-09-11T20:50:49.701Z

Hmmm… that’s beyond me. I’ve only worked with upload in the administrator panel, not for a user. But I’d think that once it’s validated, it should upload fine, since that’s the purpose of the validator step.

One challenge generally I see is how would a user know the “DealStage” options you have set up in your app, since they would need to match exactly? If any don’t match that screws up the upload, but I believe the validator would catch that.

Maybe Bubble doesn’t support option sets in user uploads? Or there’s a bug. I’d ask Bubble support.

SerPounce · 2020-09-13T23:14:31.978Z

I’m running into the same issue. Were you able to solve a user uploading option sets?

harris · 2020-09-13T23:49:20.815Z

Not yet, I’m in touch with Bubble support—this is their request:

image1352×538 39.9 KB

This is going to take me a little bit to put together as they are requesting it. I did share this thread with them already.

Thanks for letting me know, I will reply back to let them know that other users are experiencing this issue too.

harris · 2020-09-14T23:54:07.773Z

@SerPounce well, this is a bit disappointing.

TL;DR It is working—not working, IMO—as intended. The back-end and front-end uploading functions differently and it does not appear that option sets are supported.

image1296×362 26.3 KB

Bit of a bummer. Now I have to put this in my documentation for my users, fortunately I can explain to them that it’s Bubble’s intent. I don’t know if that’ll inspire confidence in Bubble as that’s they’re using my app… But my options are limited at this point.

ed727 · 2020-09-15T00:30:44.505Z

That’s too bad. Could you replace your option sets with datatypes to get around this?

Also, this is something that Bubble could document better, to save others going down this path.

harris · 2020-09-15T00:58:24.648Z

Totally agree @ed727 this would be nice to have in the documentation somewhere.

The error message is so opaque and I spent a chunk of time trying to troubleshoot this thing, not to mention you all jumping in too.

I’m not sure if I feel like re-factoring to meet this edge case, I’ll have to see what users think as I go into alpha testing.

SerPounce · 2020-09-15T12:27:33.889Z

Thank you for the follow up @harris.

Damn, that’s annoying. This is definitely one of those cases where if it is acting as intended, the intention was off to start with… way off! Why on earth would they design it with that intention in mind. Complete twaddle IMO.

I will have to use text fields in the upload and then program the backend workflows to assign the appropriate option. I considered using data types as @ed727 suggested, but I significantly prefer the power, flexibility and performance of options sets.

harris · 2020-09-15T15:07:09.317Z

SerPounce:

I will have to use text fields in the upload and then program the backend workflows to assign the appropriate option. I considered using data types as @ed727 suggested, but I significantly prefer the power, flexibility and performance of options sets.

@SerPounce I’m with you on this, I think Option Sets work well for my use case.

Would you mind sharing the workaround that you’re outlining here, when you get around to it? Not urgent, but I’m not quite sure what that would look like and it might be nice to replicate.

SerPounce · 2020-09-16T11:45:45.960Z

This turned out to be way easier than I thought…

Bring in the CSV fields as text in a temporary holding table.
Then create the actual records in your final destination table and apply a filter on the option group based on the temp table’s text field as I have in the example pasted here.

Screen Shot 2020-09-16 at 7.42.23 AM1366×658 68.2 KB

harris · 2020-09-17T01:14:01.928Z

@SerPounce I heard back from Bubble Support, they’re working on this so stay tuned! I haven’t tried this workaround yet because I’m hoping the fix works out