How do I copy a record via api?

  • 38
  • 1
  • Question
  • Updated 5 months ago
  • Answered

I need to create a copy of a record to a separate table before changing the Revision value and deleting the contents of several fields.  I've got the update to the Revision value working, and how to delete the field contents - now I need to figure out how to copy the record before making these changes.  Unfortunately, there doesn't seem to be an APICopy action.

Thanks in advance!

Photo of Devona

Devona

  • 20 Points

Posted 3 years ago

  • 38
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
You will use the API_Add Record.  each source field needs to be mapped into the corresponding target field.



It will look something like

urlroot() & "db/" & [_DBID_TARGET_TABLE] & "?act=API_AddRecord"

& "&_fid_6=" & urlencode([source field])

& "&_fid_7=" & urlencode([a different source field])

& "&_fid_8=" & urlencode([yet a different source field])
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Hi, I'm trying to create an API button to copy a record along with a child and multiple grandchild records (I have looping relationships so I can't use the Copy Master Detail wizard), what is the format for this? Thank you!!! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
The API and the Wizard button use the same underlying API. So they have the same limitations.

I have a client who absolutely needed to copy with children / grand children, and we ended up contracting with another QSP to write s script.

Contact me off line for more information.

QuickBaseCoach.com
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Ahhh, shoot. Well, what about this - What if I copy the middle record (the parent, but not the grandparent)? When I do that just by clicking more - copy this record, I have the option to create a new grandparent record in my related job (grandparent record) dropdown on the parent record form. Is there a way to get it to automatically copy the parent record with its children and then send the user directly to the add/edit view of the new grandparent record form? 

Sorry if that's convoluted. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
If you can get the copy button working on the Parent, then I can help land you on the Add Grand Parent Record.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Awesome! So I would just start with the API button you described above on the Parent?



urlroot() & "db/" & [_DBID_TARGET_TABLE] & "?act=API_AddRecord"

& "&_fid_6=" & urlencode([source field])

& "&_fid_7=" & urlencode([a different source field])

& "&_fid_8=" & urlencode([yet a different source field])
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Or would I do the Copy Master Detail API? 


I saw this in a different post:

var text CopyMasterDetail = URLRoot() & "db/" & Dbid() & "?a=API_CopyMasterDetail&destrid=0&sourcerid=" & ToText([Record ID#])
& "&apptoken=" // if you have app tokens enable, put it in here
& "&copyfid=6" // replace the 6 insert the fid for the invoice# field here
& "&relfids=7"; // replace the 7 with the fid for the report link field on the relationships between invoice and lines
// typically it would be called Invoice Lines or Perhaps Lines.
// remember to end your formula variable with a semicolon.


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Yes, you need to use the api in order to control where to land the user.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Which direction do you recommend I go with it? API_CopyMasterDetail or API_AddRecord ?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
I don’t understand. Only the copy master detail api will copy children and grand children.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Sorry - I haven't used API to create a button before. Is this the code I need to use? 

var text CopyMasterDetail = URLRoot() & "db/" & Dbid() & "?a=API_CopyMasterDetail&destrid=0&sourcerid=" & ToText([Record ID#])
& "&apptoken=" // if you have app tokens enable, put it in here
& "&copyfid=6" // replace the 6 insert the fid for the invoice# field here
& "&relfids=7"; // replace the 7 with the fid for the report link field on the relationships between invoice and lines
// typically it would be called Invoice Lines or Perhaps Lines.
// remember to end your formula variable with a semicolon.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
And, will that work even though I have looping relationships?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
No, if you have looping relationships form the Parent down to children / grandchildren it will not work. 

I had thought that you felt that if you did the copy parent child at the parent level that it might work.  And then you would figure out how to add the grand parent manually.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Yes, that's the idea - I know I can create a master detail button on the parent level, but I'm just not sure how to format that code. I asked the looping question because I had been under the impression that if I had any looping relationships AT ALL in the app that it wouldn't work, I didn't realize it was just the relationships directly related to the tables I'm trying to manipulate. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
yes, that code above is the general template

var text CopyMasterDetail = URLRoot() & "db/" & Dbid() & "?a=API_CopyMasterDetail&destrid=0&sourcerid=" & ToText([Record ID#])
& "&apptoken=" // if you have app tokens enable, put it in here
& "&copyfid=6" // replace the 6 insert the fid for the invoice# field here
& "&relfids=7"; // replace the 7 with the fid for the report link field on the relationships between invoice and lines

The help for the URL parameters is here 
https://help.quickbase.com/api-guide/#API_CopyMasterDetail.htm




When you run an API, when it fails it will spew back on the screen error messages and when it succeeds, it will also spew back on the screen with a success message.

So once you get it working, then you will need to have what is called a redirect to land the user somewhere  or else the user will be exposed to the success message which they will think is an error message.

The format for that is 

$CopyMasterDetail
& "&rdr=" & URLEncode(urlroot() & "db/" & .... the rest of the URL where you want to land the user).


Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Okay thank you! I will work on that and let you know when I have it working. I was looking at the API guide a bit earlier and trying to learn about how it works. 
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Thanks! I got it working - it is successfully making duplicates of the parent and grandchild records.

Is there a way I can specify the value of a certain field with this API call? I know then you use API to create a new record you can specify the value of fields, can you do the same with API_CopyMasterDetail?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Congrats.  Give yourself a pat on the back.

So when you use the Copy Master detail it will copy every field, like it or not.
So what I used to do do when I have fields to clear out or fields to change, like say a [Status] field would be to have a formula URL field which would detect that the record name now begins with "Copy of", and  and make the users push a big red button for Copy Step 2.  That would clear certain fields.

Another probably better option now with less code is to use an Automation to do this. Especially if you are not landing the user immediately on the Parent record you just created, then the Automation will have the split second it needs to run and do the updates before the user sees the record.



Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
>I know then you use API to create a new record you can specify the value of fields, can you do the same with API_CopyMasterDetail?

The API method API_CopyMasterDetail executes on the server and can only be configured by setting one of the documented parameters:

https://help.quickbase.com/api-guide/index.html#API_CopyMasterDetail.html

However, you can write your own recursive script that executes in the browser using other API methods and JavaScript. The fact that the script is recursive is not really a difficulty as using a feature in JavaScript called async generators you can easily iterate through the relationship tree and at each node (ie record) of the tree perform the appropriate copy or initialization of a field. In fact, it is possible to iterate over the tree in Depth First (Inorder, PreOrder, PostOrder) or Breadth First (Level Order) order:



Here is a quick script that recursively iterates over a table which is self-related:
async function* generator(dbid, rid, clist, relatedFid) {
  let clistArray = clist.split(".");
  let queue = [rid];
  while (queue.length) {
    let recordID = queue.shift();
    yield recordID;
    let xml = await Promise.resolve(
      $.get(dbid, {
        act: "API_DoQuery",
        query: `{${relatedFid}.EX.${recordID}`,
        clist: clist,
        fmt: "structured",
        includeRids: "1"
      })
    );
    let childRids = $("record", xml).map(function() {
      let data = [];
      clistArray.forEach(fid) {
        data.push($(`f[id=${fid}]`).text());
      }
      return data;
    }).get();
    queue.push(...childRids);
  }
}
This script does not do anything other than visit each node/record in the relationship tree and yields the [Record ID#] but it could easily be adapted to any specific task such as copying or initializing fields from the records that are visited.

Scripts like this are useful in a variety of other contexts other than recursively copying records. For example, you can easily calculate total weight or cost for a hierarchical assembly modeled using a table related to itself. Or you could perform various queries on an organization chart again modeled using a table related to itself.

If you need functionality similar to this feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt
(Edited)
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Thank you both very much!!! I actually wound up figuring out a way to get QB to natively copymasterdetail for my grandparent table by turning off recurse and only including one additional child table, and then setting up an action to create a child record in the other related table I need (the one causing looping relationship issues). So now I'm working on creating an automation to clear out a couple of status fields in that grandparent record when a record with "copy" in the title is added - what do you use as filter criteria for automations when you're asking it to edit itself? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
You set up the edit records filter in the Automation where the [Record ID#] of the trigger record = the Record ID of the table being edited.
Photo of Elena Larrabee

Elena Larrabee

  • 856 Points 500 badge 2x thumb
Oh, of course. Well thank you SO MUCH for your help, I really appreciate it. Everything seems to be working now.