Transferring data from one collection to another

I need to copy rows from one collection to another collection based on a particular field’s contents in the source database. For instance, I have a collection full of book titles, isbn numbers, lots of other information including the number of copies we have of that book. I need to add a record to the destination collection with the title and isbn number, one time for all records with a copy count of 1, and add multiple records where the copy count is greater than one. So, if a book (in source collection) has a copy count of 1, then one record will be inserted in the destination collection. If a book has a copy count of 2, then 2 identical records will be inserted into the destination collection, etc. Can someone help me with the code for that?
Thanks,
Ron

I think you could do something like this:

see code below

Hi Bruno,

Thank you for your quick response.

I get the following errors when I try this code.
Property ‘hasNext’ does not exist on type ‘any
Same error for property ‘next’

Here’s the code:

export const copyRowsCount = async () => {
let { items } = await wixData . query ( ‘bookList’ ). limit ( 1000 ). find ()
let allItems = items
if ( items . length > 0 ) {
while ( items . hasNext ()) {
allItems = [… allItems , await items . next ()]
}
} // This is going to retrieve all the items from the Source collection.

let results =
allItems . forEach (( item ) => {
console . log ( item . count )
for ( let i = 0 ; i < item . count ; i ++) {
results . push ({ title : item . title , isbn : item . isbn })
}
}) //This is going to select the items to be added.

await wixData . insert ( ‘Destination’ , results )
}

Any help would be appreciated.

Thanks,
Ron

@roncarran I’m sorry, a little error on my code. Sorry about that. Did too fast. I think, now we’re good.

see code below

@bwprado

Hi,

Now I get errors on “allitems”
You’re trying to assign an object of type ‘any[]’ to a variable that id declared as ‘WixDataQueryResult’. It’s recommended that you use a new variable, such as “let newVar = …” to make sure that all code completion work correctly.

And for allItems.forEach I get
Property forEach does not exist on type ‘WixDataQueryResult’.

Sorry for all the problems. Thanks for helping.

Ron

I think I missed a property:

import wixData from 'wix-data'

export const copyRowsCount = async () => {
  let query = await wixData.query('Source').limit(1000).find()
  let allItems = query.items //this was the error.

  let results = []
  allItems.forEach((item) => {
    for (let i = 0; i < item.count; i++) {
      results.push({ title: item.title, isbn: item.isbn })
    }
  }) //This is going to select the items to be added.

  await wixData.bulkInsert('Destination', results)
}

I was reading the WIX Data API today and remembered there was one more thing wrong with the code, the .insert() method.

I changed to the correct one but there is a limit of 1000 items now. Let me know if you need the code to be more than 1000 items.

If WIX allows more than 1000 insert requests, then maybe, this code would work for more than 1000 items.

import wixData from 'wix-data'

export const copyRowsCount = async () => {
  let query = await wixData.query('Source').limit(1000).find()
  let allItems = query.items //this was the error.
  if (allItems.lenght > 0) {
    while (query.hasNext()) {
      query = await query.next()
      allItems = [...allItems, ...query.items]
    }
  } // This is going to retrieve all the items from the Source collection.

  let results = []
  allItems.forEach((item) => {
    for (let i = 0; i < item.count; i++) {
      results.push(wixData.insert('Destination', { title: item.title, isbn: item.isbn }))
    }
  }) //This is going to select the items to be added.
  await Promise.all(results)
}

Otherwise, it should be done in batches, using the .bulkInsert() method.

Hi Bruno,

The only error I get now is in the line results . push ( await wixData . insert ( ‘Destination’ , { title : item . title , isbn : item . isbn })). The await shows this error: ‘await’ expressions are only allowed within async functions and at the top levels of modules.

Also, I do have to transfer more than 1000 records. Not sure if you said that this would work, or I should do it in batches.

I just removed the await key that was a mistake, but I don’t know WIX will allow more than 1000 requests, let me know.

Will do, thanks.

Thanks for the code. No syntax errors, but I’m not even able to call the function. I guess I just don’t understand how the Wix architecture works. It’s been a long time since I coded websites, and that was done with Microsoft’s .net, with an integrated development environment where I could debug. With Wix, I just don’t understand how to do that.

Here’s my code:

import wixData from ‘wix-data’ ;

$w . onReady ( function () {
// Write your JavaScript here

// To select an element by ID use: $w(‘#elementID’)

// Click ‘Preview’ to run your code
});

/* from Bruno Prado */

export const copyRowsCount = async () => {

let query = await wixData . query ( ‘Source’ ). limit ( 10 ). find ()

let allItems = query . items
if ( allItems . length > 0 ) {
while ( query . hasNext ()) {
query = await query . next ()
allItems = [… allItems , … query . items ]
}
} // This is going to retrieve all the items from the Source collection.

let results =
allItems . forEach (( item ) => {
for ( let i = 0 ; i < item . count ; i ++) {
results . push ( wixData . insert ( ‘Destination’ , { title : item . title , isbn : item . isbn }))
}

}) //This is going to select the items to be added.
await Promise . all ( results )
}

export function btnStart_click ( event ) {
copyRowsCount ;

}

Try:

exportfunction btnStart_click(event){
    copyRowsCount()
}

That way you were not invoking the function, just its content.

Try adding console.log() with the variables you want to check if something returned, for example:

if (allItems.length > 0) {
    while(query.hasNext()) {
        query = awaitquery.next()
        allItems = [...allItems, ...query.items]
    }
} // This is going to retrieve all the items from the Source collection.
console.log(allItems) // Check if something returned.

Also, you need to rename the collections you want to copy from and to, in my example I used ‘Source’ and ‘Destination’ because I was not sure what was the name of yours.

Thanks for all your help on this. I think I’ve got it now.

Ron

Hi Bruno,

Sorry to bother you with this again, but when I run your code, it copies some number of records to the new collection, but a different amount every time I run it, and never even close to the total number I need copied. I created a shorter source collection of about 350 items, and I get somewhere between 70 and 150 copied each time I run it. Is this possible a timing problem? Again, I appreciate your help.

Here’s the code I’m running.

export const copyRowsCount = async () => {

let query = await wixData . query ( ‘SmallBookList’ ). limit ( 1000 ). find ()

let allItems = query . items
if ( allItems . length > 0 ) {
while ( query . hasNext ()) {
query = await query . next ()
allItems = [… allItems , … query . items ]
}
} // This is going to retrieve all the items from the Source collection.

console . log ( allItems );
console . log ( allItems . length );

let cnt = 0 ;
let results =
allItems . forEach (( item ) => {
cnt ++;
$w ( ‘#txtRecordNum’ ). text = String ( cnt );
//console.log(item);
for ( let i = 0 ; i < item . copies ; i ++) {
results . push ( wixData . insert ( ‘ISBN’ , { title : item . title , isbn : item . isbn }))
//console.log(item);
}

}) //This is going to select the items to be added.
await Promise . all ( results )

}