How to Copy Record and Copy Children

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered

My QB has a table of "Invoices". Each month the users "copy" the invoice for the new month. Most fields stay the same, including a list of children "Lines". When they copy an invoice, the child lines are not also copied. Why is this?

As per many-to-many rules, I created a "hidden" Invoice-to-Line table that is just the "Related Invoice" and "Related Line" (and the fields in the relationship). I guess I need to have QB create new entries in Invoice-to-Line when an Invoice is copied, with all of the Lines connected to the new Invoice.

Is this possible? (An Invoice can have up to 200 Lines, so this is a requirement for the users.) How do I implement this?

Thanks in advance!

Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Some background:
https://help.quickbase.com/api-guide/api_copymasterdetail.html

A  video:
https://www.youtube.com/watch?v=oXb2-rqtgaU

And the wizard:
is in your app setting.


Let us know if you get stuck or have more questions.
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

Thanks for the quick response!

It looks like it's easy to have 1 (or a handful) or "Master" rows/entities, which are "Templates" for new rows. Based off the link and video you sent, this doesn't quiet meet my needs.

1) "Invoice" is not in my list of possible "Copy Master & Detail Records". My guess is b/c it is dependent on a "Header Invoice".

2) There are going to be thousands of invoices that need to be copied each month. And each invoice has its own set of lines. I cannot create 3k "import lines from xyz invoice". Also, it needs to be dynamic.

     A) The users will add new Invoices.

     B) Today an Invoice might have 5 Lines, but 6 months from now they might add a new line and so the Invoice will now have 6 or 10 Lines. Thus, each Invoice needs to be able to pass its attached Lines to the new copy.

Does this make sense?

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,736 Points 50k badge 2x thumb
When you create the button using the Wizard, it asks you if you want to copy the parent too.
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

Could you provide a little more detail? I'm not sure what you mean.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,736 Points 50k badge 2x thumb
Did you locate the Wizard?  Please locate it and try it.  It will allow you to copy an invoice and all its children.  isn't that what you want?  Mathew showed you where to find that Wizard to make the button.
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

I don't see "Wizard" anywhere when I follow the steps: Home -> Settings -> App management. -> Copy master & detail records.

However, when I navigate to that page, and click the drop down "Choose a table", Invoices is not an option, as I said above.

Additionally, as I explained before, I do not see how "Copy master & detail records" creates a dynamic for every Invoice. Based on what Mathew provided above, I do not think this solution will work for my needs.

Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
When you go into the CopyMasterDetail "wizard" it will ask you how you want to set it up.  You can either set it up so that you "copy" the record you're on and all the children from its relationships or you can set it so that it will copy a "specific" parent's child records.

In your case, I imagine that you will want to "copy" your Invoice from one month to the next with all the children in tow; and then just modify the Invoice details (like the Invoice # for example).

When you finish setting this up, QuickBase will create a button for you in your table (you get to name it as part of the setup).  You then put this on your Invoices so you can copy them when you want to.  If you choose "this" setup; I recommend also creating a dedicate text-field in your Invoices table called [Copy].  So when the feature copies your invoice and line items; that the "Copy of" text can go directly into this field instead of a valid-data field which it would otherwise do.

If you then need some kind of "dynamic" Invoice Numbering method; you will need this to work from some kind of formula which probably would reference/include the Date somehow and the Record ID# of the Invoice that gets created.

This is actually the perfect native solution for your needs; if you need help working through it in your App, I'm sure that Matthew, Mark or myself can help - I see that you were having trouble finding your Invoices table in the drop down.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,096 Points 50k badge 2x thumb
It seems bizarre that your invoices table is not listed as a choice.  So you are saying that is not a choice on that drop down list.

There is a way to make the same button using the API but I don't understand why invoices is not a choice for you
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb
Correct - it is not in that list. Only 13 of my 16 tables are displayed as options. Could this be b/c Invoices are children of Invoice Header (which is listed).
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,096 Points 50k badge 2x thumb
Is there at least 1 field on the invoice table which is a pure text data entry field (not multiple choice)?  It needs at least 1 test data entry field to be chosen.
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

Yes, it has "Invoice #" as a Text. (It has more, but the others are from relationships.)

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,096 Points 50k badge 2x thumb
You can try this format to make your own button using the API method.


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.

var Text ReportOfInvoicesCreatedToday =
URLRoot() & "db/" & Dbid() & "?a=q&qid=5"; // make a report of Invoices created today by the current user.  ie two filters, and substitute in the report ID for the 5.

$CopyMasterDetail
& "&rdr=" & URLEncode($ReportOfInvoicesCreatedToday)
Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb

Hi all - I wanted to update this post now that I've found the problem. (Actually it was a co-worker of mine who figured it out.) My "Invoices" table was not showing up in the list because it had a conditional relationship with Invoice-to-Line. I was attempting to limit the Lines you could connect to an Invoice based on a shared field, and thus created the condition. By removing the condition, Invoices appeared in the CopyMasterDetail and now my "copy invoice" button works great.

Thanks for all your help!

Photo of Sarah Wallis

Sarah Wallis

  • 160 Points 100 badge 2x thumb
I don't see how to mark an solution as the answer...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 44,580 Points 20k badge 2x thumb
Thx for posting that response. It's a terrible limitation that I have run up against with CDDs preventing Copy Master Detail from working.

Alas, this version of the Forum has no way to mark a Question Answered other than by the moderator.
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
I am trying to create a copy button on a table called Actions, but like Sarah above, it is not listed as as a table option in App Management. What can I do to get the table to show up, so that I can copy action items?

Once that problem is solved, is there a way to put the button in grid edit? We need to create the same action item on multiple projects. We will just need to change the project name for each action.

This conversation is no longer open for comments or replies.