Problems setting references with imported data

I’m new to Corvid, so bare with me :)…

I imported data from a MySQL database via cvs files successfully into multiple databases in my site. But now I have issues connecting the tables fields correctly via references →

I have a table pictures which has a field “artist_id” . In my prior database this was a reference to the “id” field of the artist table and so the “artist_id” got imported as type NUMBER but has no connection to that other table yet.

When I convert this field to a REFERENCE and connect it to the “artist” table, it does work, but it then marks all fields in red and says “Cell value type is Number. Change to Reference.” (see screenshots).

I then have to click on this message in every field … it shows me the correct id from the referenced artist table in a dropdown … and only after I clicked on that, the red warning disappears and it is correctly referenced.

But I would have to do this with EVERY single entry. (which is bonkers…)
I don’t understand why it does the type conversion automatically for all field when I already changed the type via the “Manage Field” panel. After all, it already shows the correct connected data aka id in the error dropdown…

Is there any way to do this conversion automatically with all fields?
Or is there another way to do my referencing without having to go through everything manually?

Here’s what I did:

  1. The field artist_id was changed to type REFERENCE

  1. Fields are marked red and I have to click on every field to update it …

  1. After clicking on “change to Reference” it shows the correct id (so why does it not convert automatically…?!) I have to click on it and only then the error disappears.

Your issue is not code related and therefore should not be in the Wix Corvid Forum as it is related to importing csv files and datasets.

You would be better suited going through Wix Support instead.

For more info on Wix and using reference fields in datasets see here.

Also, make sure that you have read up about using Wix Datasets and their different fields as you would have found an answer there for you too.

Main Fields
Every database collection has a Main field which are used by reference fields to create connections between collections. A collection’s Main field is indicated by a lock icon next to its field name. By default, the Title field is the Main field. You can define any text field in your collection to be the Main field except for the ID system field.

This is more related to the difference between SQL and noSQL database architecture (Wix uses noSQL). You should read up on it if you care to, as it will also help you be a bit smarter when designing your database.