How to Import data to a multi-reference field.

Ask the community
,
1

I’ve set up a collection which among other fields, includes two reference fields and two multi-reference fields. I now want to import my data from a CSV file using the Velo Import button. Using the system id for the items in the reference fields works fine. I cannot get the data for the multi-reference fields to work when I use the system id for the items. What is the proper format for importing data for a multi-reference field? Thanks

2

Have you tried “Tags”?

3

No. Not sure how tags would work in this situation.

I tried using spaces and then ""s between the system ids, to no avail.

4

I had to bulk upload 1500 rows of data that had multirefs in it. I could not do it with the csv uploader. I had to create JSON blobs (the multiref fields were the IDs of the referenced table). I had to write custom code to do all this. Basically, insert the row, and then insert the reference. You have to do it in a separate step.

Also, beware…if you’re uploading a lot of data, even if you run it on the backend, it still times out. Wix doesn’t really have a good way of doing bulk operations that take longer than the timeout. They suggest using a cron job for long-running jobs, which seems like a bizarre way to get around the problem. I didn’t do that. I just wrote batching code that did the DB inserts in batches. I had to keep clicking a button to trigger each batch upload, so this was pretty dang hacky.

I actually don’t know if the csv import works, or should work for multirefs. I couldn’t get it to work, but then again…my attempt at this was over a year ago.

Regarding tags, people often suggest using a tag data type instead of a multiref. However, tags don’t really accomplish things like referential integrity, nor are they actually M:N relationships. They also don’t work if you need the referenced item to be more than just a simple string, but a full-fledged object.

Multirefs are broken in a multitude of ways, so beware. I have a really relational data model and I used single and multirefs all over the place. Then I discovered how broken they are, and I had to do a lot of hacking to get around their limitations.

In a nutshell…things that don’t work with multirefs:

  1. There are no hooks that know when a multiref has changed. You cannot programmatically detect a change to your referencing table if only a multiref has changed.

  2. You can FILTER on a multiref, but it is damn near impossible to SELECT a multiref column in the referencing table. There are two ways to do it, and they are both severely severely limited.

  3. Don’t even try to put data from a multi-referenced field into a repeater.

Unfortunately, multirefs really don’t have anything to replace them with. If you need referential integrity and real M:N relationships, this is the only thing Wix gives you.

5

Thanks for this response. Multi-ref fields are a challenge. We are letting users search on four fields, two which are reference fields and two which are multi-reference fields. Wixquery only allows one multi-reference field, so one of the people working on the project had to write a lot of JavaScript to make our search function work. He was working with dummy data, and I had hoped that the CSV import was going to be straight forward, because we have probably 500 or so items to import. Unfortunately, from what you are saying, I may have to ask him to do more work, and he’s doing it on a volunteer basis. :(.

6

You can search more than one multiref field. You just can’t display them. In SQL speak, you can easily put multirefs in a WHERE clause. You cannot put more than one of them in a SELECT clause. I have a very complex search that lets you query on any number of multiref fields and this works fine. But when you get the results back, you can’t actually display more than one multiref. I had so much trouble with the display part that I ended up creating a search index table that flattened out all the multiref fields into arrays. Our database doesn’t change that frequently. I have hooks that will reindex a row when it changes, but if someone changes only a multiref, there is no hook that will detect it. So I run a nightly sweeper job that looks for rows recently updated and indexes them.

7

@polkaset Yes, using arrays and flattening them was the solution we came up with for doing the search. I was hoping the import from the CSV file would be straight forward, but clearly that is not the case. More work for my volunteer programmer.

8

I went back and looked at my code. It was a really long time ago, so I didn’t remember everything exactly. I did not first insert the main fields, then insert the references with a second call to insertReference. I actually embedded the IDs as arrays and just inserted the row.

Here’s what one of my multiref inserts looked like:

“exposure”: [
“10f4d4ca-76f0-4af8-a59f-5612346bc398”,
“3d556d56-ca46-4c47-8835-6a99f7fbd2ee”
],

When I did this, I was pretty new to Wix, so I didn’t know what was possible/typical/etc. And I didn’t really notice the insert, then insert references APIs/pattern…

Inserting this way did work for me.

9

Thanks, I will look up the insert function. It sounds like I add the rows with the standard CSV import, then add the multi- reference fields with the insert function, using the system id for the row, the column and the elements to be added to the multi-reference field. Is that correct.

10

polkaset. I found some code for the insert function, but it relates to data entered by a user. Would you be able to share your code which used the CSV file as the source for the insert function?

Thanks

11

Problem has been solved. The CSV import function works for multi-reference fields when the data is formatted as follows: [“sys id”, “sys id”, “sys id”, . . . .].