Import database and dynamically add data to reference field

Ask the community
,
1

I appreciate anyone who can chime in here. I have spent many hours on this researching and trying different things. I am pretty new to coding in general so I appreciate patient and kind responses.

I am transitioning from a 20+ year old series of databases and importing the data into wix/corvid. The databases I am dealing with have a few thousand records each.

The goal here is to import the data and create reference fields as well as use the automatically generated _id created in corvid.

My ideal scenario would for this to be done automatically once importing the data. I was able to do what I was wanting through using a hook “afterQuery” event on the database.

The problem with this is that the data is not permanent and chews up a lot of resources every time you query the database. Please see my code below where I am reaching out to find the ID to a specific parcel and assigning that ID to a reference field in my Taxes database.

export async function Taxes_afterQuery(item, context) {
 let parcel = item.parcelNumber;

 const NewID = await wixData.query("ParcelNumbers")
 .eq("parcelNumber", parcel)
 .find()
 let NewParcelID = NewID.items[0]._id;
 //console.log(NewParcelID);
 
 item.idParcel = NewParcelID;

 return await item;
}

Is there a better way to do this so the data is permanent. I only need to do this after the initial import. From that point on as I add data I can take care of it correctly.

I hope this makes sense to someone.

2

I believe I understand what you are trying to do. You have the parcelnumber in 1 collection and another collection parcels which now carry a wix_id, but there is no relation between the 2 except for the non-ref field parcel number, hence the query.
One solution might be to … start again with importing and, if and only if, the parcel numbers are unique, not to rely on wix-data to provide an _id, but to generate one yourself … being the parcel number. You do this by proving an _id=parcelnumber when writing the parcels-collection. When you then declare parcelnumber in the first table as a ref-field, voila, you can do it in 1 query with .include (not .get, for .get does not support .include).
Does this make any sense at all? Or maybe I didn’t get the problem right.

3

Thank you for your response. This does make sense and is very similar to what I am doing now as a workaround. Thank you!

Still hoping there is a way to do what I am hoping from the original post as it will help not only with reference fields but perminantly morphing the data in the database after it is imported.

I guess I can also go to excel and do all the morphing in advanced, including the _id if I import the data first to wix and then export it into a csv – morph data – then return the data to wix…