Copy muliple rows by id and reinsert as new record

[tonykiefer]

I'm working with a sql table and I would like to copy all records that match a chosen id and paste them back into the table as new records.  For example...If I wanted to loop through this table and copy all records with a foodID = 1, copy those records and paste them back into the table...how can this be accomplished?

I tried this but it doesn't work...pardon the pseudo code

Patch( Fruit, Defaults( Fruit ), { id: fruit.id, foodID: fruit.foodid, foodName: fruit.foodname})

ForAll(Fruit,Patch(Fruit,Defaults(Fruit),{id,foodID,foodName}))

[BCLS776]

Try this pseudocode outline:

ForAll(
    Filter(myTable, /* filter args to get the set of records you want to duplicate */) As aRecord,
    Patch(myTable, Defaults(myTable),
    {
        //create fields here
    })
)

Hope that helps,

Bryan

[tonykiefer]

Thank you for the reply...when I tried that I get this error.

This process need to occur in the same table.

Also, If I'm copying the records and pasting them as new records in the same table, how do I create fields as placeholders?

[BCLS776 · Accepted solution]

No problem, let's pre-generate the filter results:

With({aFilter: Filter(myTable, /* filter args to get the set of records you want to duplicate */)},
ForAll(
    aFilter As aRecord,
    Patch(myTable, Defaults(myTable),
    {
        //create fields here
    })
))

[tonykiefer · Accepted solution]

Hey thanks...I took your method one further step.  I copied the record from the db into a collection, then filtered those records before inserting back into the db.  Question....assuming I want to copy all records with a id of 1...when I paste them their id should be 2...How do I get that to happen?

[BCLS776]

---

@tonykiefer wrote:

Hey thanks...I took your method one further step.  I copied the record from the db into a collection, then filtered those records before inserting back into the db.  Question....assuming I want to copy all records with a id of 1...when I paste them their id should be 2...How do I get that to happen?

---

Assuming the id is a column you can change (i.e. not system-generated), you can control it with the record you create in the Patch():

    Patch(myTable, Defaults(myTable),
    {
        id: Value(aRecord.id)+1
    })