How can I use the "copy master and detail record" capability in Quickbase to write into a separate table?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I am using the native ability to copy master and detail record. I don't see the option of specifying a destination table to copy the record into? How would I specify a destination table to copy the record into?

Photo of Krishna

Krishna

  • 10 Points

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Can you clarify your needs.  Are you tying to copy a single record to another table.  Or multiple records?  Or a parent and its children?
Photo of Krishna

Krishna

  • 10 Points
I am trying to copy a single parent record and its children into another table.
Follow on Question: Can this action be triggered by the change in value of a specified field on the parent record?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Sorry, but I can't help with how to do that in a single click.  It would take real programming.  I can think of a way to have a button to copy the parent and then another button on that newly created parent to copy across the children.  But not all in one step and not upon the trigger of saving the original parent with a change in a field.  May be someone else will chime in with a solution, but it would need to be some real code, so you may need to hire a developer to do that for you.
Photo of Krishna

Krishna

  • 10 Points
Thanks for your response! Disregard the "trigger" request. QB natively offers me the ability to "copy master and detail records" but it writes the copy into the same master table. How can I get it to write this copy into a separate table?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
That is not what it does natively.  So you can't do that without coding.  Note that when you say "copy into a separate table" do you mean a separate parent table and also a separate Child table - so copying to 2 tables respectively for the Parent and for the children?
Photo of Krishna

Krishna

  • 10 Points
Ok, to get us on the same page. I was referring to Home – Settings – App Management – Manage App Content – Copy Master and Detail Record when I said “native functionality”. So, yes it is available natively.  This little feature here creates a copy of the master record and all selected detail records and writes it into the original master  and detail tables. My question is: how do I get it to write the copies into different master and detail tables?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
My comment was meant to mean that yes I am aware of what Master and Detail does but no, there is not a native setting to select the target tables to copy to.  As you have discovered, the Parents end up in the Parent table and the Children end up in the Children table.  The most typical use for this feature is when say you have a Project with Child Tasks, and you want to now do a similar project to an existing one - the existing one typically being a Task Template set of say a dozen standard Tasks for the Project. So since that is the typical use, it does not contemplate the concept of mapping fields across to different tables.

The direction I was heading in my questions to you was to explore if a saved import could work for you.  That is the one place I know where multiple records can natively be copied (ie without coding) from one table to another.  You build an import which is a Table to Table copy and specify the filters and the "mapping" which which records get copied across and how the source fields map to the target fields.  That Import set up can be Saved and then run at will with a URL formula button and no coding.

Now, is there a really good reason which you want to copy these to a new table?  Perhaps you can accomplish your goals within a single table ( and another for the children, of course) and some kind of status indicator on the parent (looked up down to the children) to indicate that the copy is a different type of record.
Photo of Krishna

Krishna

  • 10 Points
There is no really good reason to use a different table, if I could use the “Copy Master and Detail record” feature and set a status indicator field on the parent record in one shot. Is that possible?  It’s not preferable to have the user go in and manually set a status indicator field after the copy.
 Also how would you incorporate a saved Import setup in a formula url button?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
There is no native way that i know of to select a certain field and populate it during the Copy Master Detail process.  What I do is to have a formula text field which detects if a certain field begins with "Copy of".  YOU will note that when you use that Copy Master Detail button it re-populates a text field to begin with "Copy of".  If I detect that I put up a HUGE red obnoxious message asking the user to click a URL formula button which clears certain fields that I did not want copied over.  In your case if you have no fields to clear, you could have that button set a flag on the newly copied record.

var text URLONE = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [PO ID#]
& "&_fid_126=" // Revision
& "&_fid_217=" // Revised By
& "&_fid_130=" // Revised Date
& "&_fid_8=" & Today()    // Order Date
& "&_fid_169=" // Temporary Copy Of PO field

// redisplay the record
var text URLTWO =
URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [PO ID#];

$URLONE
& "&rdr=" & URLEncode($URLTWO)

As for your second question, if you build a saved import, you can run it with a URL formula like
URLRoot() & "db/" & [_DBID_CAPCON_COST_PLAN_BUDGETS]  & "?a=API_RunImport&id=10"


In the example above, I am running saved import with ID = 10
Photo of Leanne

Leanne

  • 284 Points 250 badge 2x thumb
see above
Photo of Leanne

Leanne

  • 284 Points 250 badge 2x thumb
To take this question a little further (I think it's in the same direction)...

If you want to copy or import the detail records from a parent table into another parent table (with the same detail table), is there a way to create a button to automate that.

Here is an example:
My RFP table is the parent to the Line Items table
The IO table is also parent to the Line Items table

When we create a new IO we want to pull the child records (and lookup fields) from a related RFP record. This is not a template, the child records will be different for each RFP. Currently the lookup fields work fine, anyway to import the line items details either upon IO creation or once an IO is created would be perfect.

Thanks so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Leanne
Its usually better to post a new question so more users see it.   But I did see it.

You can use my suggestion above to build a saved import and then run it with an API call.

If you build a saved import, you can run it with a URL formula like
URLRoot() & "db/" & [_DBID_CAPCON_COST_PLAN_BUDGETS]  & "?a=API_RunImport&id=10"

I would have to know more about your app to understand if there is a way to let the target records know what to populate into the [related parent] field.
Photo of Leanne

Leanne

  • 284 Points 250 badge 2x thumb
Ok sorry for the posting error. Could you also post the code you mentioned to have the user delete certain fields? Thanks again:)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I don't understand what you mean about deleting fields??  Was that in this post thread?
Photo of Leanne

Leanne

  • 284 Points 250 badge 2x thumb
Opps sorry, clearing the fields. You had mentioned: "If I detect that I put up a HUGE red obnoxious message asking the user to click a URL formula button which clears certain fields that I did not want copied over."
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Some fields are being set to zero.  some are being set to nothing.  The first fid 126 is being sent to blank.


var text URLONE = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [PO ID#]
& "&_fid_126=" // Revision
& "&_fid_217=" // Revised By
& "&_fid_130=" // Revised Date
& "&_fid_8=" & Today()    // Order Date
& "&_fid_169=" // Temporary Copy Of PO field
& "&_fid_260=" & URLEncode("reset after PO Copy")// Submitted Log
& "&_fid_61=" & URLEncode("reset after PO Copy") // 1st Approval CAM Log
& "&_fid_62=" & URLEncode("reset after PO Copy") // 2nd Approval CAM Log
& "&_fid_339=" & URLEncode("reset after PO Copy") // Approval 1 IC Log
& "&_fid_468=" & URLEncode("reset after PO Copy") // Approval 2 IC Log
& "&_fid_435=" & URLEncode("reset after PO Copy") // Sent to Vendor Log
& "&_fid_360=0" //Record ID of Submitter
& "&_fid_426=0" //Record ID of CAM 1 Approver
& "&_fid_362=0" //Record ID of CAM 2 Approver
& "&_fid_467=0" //Record ID of IC 1 Approver (not really in use)
& "&_fid_365=0" // Signature exists Submitter
& "&_fid_427=0" // Signature exists CAM 1 Approver
& "&_fid_366=0" // Signature exists ÇAM 2 Approver
& "&_fid_467=0" // Signature exists IC 1 Approver (not in use)
& "&_fid_568=No" // Budget PO Approved for CPB

& "&_fid_368=" // ETA
& "&_fid_374=" // Date flagged as Delivered or manually updated Date Delivered
& "&_fid_369="; // Expected Ship Date


// redisplay the record
var text URLTWO =
URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [PO ID#];

$URLONE
& "&rdr=" & URLEncode($URLTWO)