Hey there coders!
I have been putting in days into this function and can't get it to work. The main function about this super cool functions is to search a field in a Data Collection for string values and check in a Referenced Data Collection if that string exists. If it does connect the records, if it doesn't create a new record in the reference Data Collection and return the newly created _id and connect the records.
The usage!
I got a Data Collection with thousands of hotel staff inside. On each staff record there is a string field called hotelName. That field can contain "Hilton Stockholm" and so on. I also have a Data Collection called Hotels and because there is thousands of records I do not want to manually update and create all the records. So I thought I would create a smart function that can search and connect staff to hotels automatically.
Here is my code.
// Functions to search and update reference fields in Wix Data Collections
// This function will check if text value exists in reference Data Collection
// and return the reference record _id. If not exist create a new one with
// searched value and return the newly inserted _id back
export async function doesRefExist(refCollectionName, searchValue) {
return wixData.query(refCollectionName)
.eq("title", searchValue)
.limit(1)
.find()
.then(async (refresults) => {
if (refresults.totalCount > 0) {
return await refresults.items[0]._id;
}
return await wixData.insert(refCollectionName, {title: searchValue}).then((inserted) => { return inserted._id});
})
}
// This function will start the looping through all items in Data Collection and start
// searching for reference field values and if found connect to the correct reference
export async function searchAndUpdatereference(collectionName, fieldToUpdate, fieldToSearch, refCollectionName) {
await wixData.query(collectionName)
.isNotEmpty(fieldToSearch)
.limit(10) // I take 10 records a time
.find()
.then(async (results) => {
if (results.totalCount > 0) {
let items = results.items;
const hitAndRun = async () => {
await items.forEach(async(item) => {
await doesRefExist(refCollectionName, item[fieldToSearch]).then((refId) => {
console.log("-------------------------- ONE ITEM ------------------------------");
console.log(refId);
console.log("Search for: " + item[fieldToSearch] + " for record: " + item.title);
console.log("---------- START UPDATING --------------");
item[fieldToUpdate] = refId;
wixData.update(collectionName, item).then(async(updated) => {
console.log(updated);
})
console.log("------------------------- ONE ITEM ENDS -------------------------------");
})
});
}
hitAndRun();
console.log("--------------------- DONE --------------------------");
}
})
}
// I execute the function with
searchAndUpdatereference("HotelStaff", "hotel", "hotelName", "Hotels");
So I want the functions to search in the field hotelName after "Hilton Stockholm" and if it finds that property in the Data Collection Hotels it should update the item with the _id from Hotels in the field hotel in the HotelStaff Data Collection.
If it can't find it the function should create it and then connect it.
It all works in a way but it creates more records in Hotels with the name "Hilton Stockholm" and does not seem to find that the second time a hotelstaff has that string in the field hotelName.
I am getting angry and frustruated and probably it is dead simple but I just can't see the error.
Please help a cold and sad dude from Sweden :)
#datacollection #insert #update #wixData #async #await #promise
Hi,
I noticed two problem with your code, first of all, when you insert or update a reference field you should use the insertReference and replaceReference functions.
I noticed another problem in the following function:
export function doesRefExist(refCollectionName, searchValue) { return wixData.query(refCollectionName) .contains("title", searchValue) .find() .then((refresults) => { console.log("Records found:" + refresults.totalCount); if (refresults.totalCount > 0) { return refresults.items[0]._id; } let newItem = { title:searchValue } return wixData.insert(refCollectionName, newItem); }) }
If you get (totalCount > 0 ) the function returns the ID of the item, but if the total count is equal to 0, the function returns an item, then, the returned data is used to set the reference field, to set the reference field you should use an ID, not an item.
Please update us with your progress,
Or :)
Anyone? @Liran Kurtz (WIX) @Yisrael (Wix)