Is it possible to copy a record from one table to another?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

Table A has records that I need to copy into Table B.

I don't need all of the fields from Table A to be copied to Table B - only certain ones.

Table B has different field lables than Table A.

Is it possible to create a button that will create a new record in Table B using the data from the record in Table A?

If so, is this something I can do myself within QB or would I need a programmer/3rd party to do it for me?

Thanks!!

Photo of Karen

Karen

  • 222 Points 100 badge 2x thumb

Posted 6 years ago

  • 0
  • 1
So are these two tables unrelated?  Do you basically want to pop open an Add new record form with a bunch of fields filled in?  Or did you actually want the record to be created.

Its not difficult to do yourself if all you want to do is push a button on a record A and either start a new record form for a record in table B or actually create it.
Photo of Karen

Karen

  • 222 Points 100 badge 2x thumb
The two tables are joined if that is what you mean?

Yes, I want to push a button on record A and have it create a new record in Table B that contains the data from record A.
Just to be super clear - do you want to pop up an add record form with some fields pre-populated?  Or do you want to actually create the new record?
Photo of Karen

Karen

  • 222 Points 100 badge 2x thumb
I want to pop up an add record form (in Table B) with some fields pre-populated (with the data from the record in Table A).
So you would make a URL formula field like this example

URLRoot() & "db/" & [_DBID_xxxxxxxxx] & "?a=API_GenAddRecordForm"

& "&_fid_YYY=" & totext([Record ID#])

& "&_fid_WWW=" & totext([some other numeric field])

& "&_fid_ZZZ=" & urlencode([some other text field])

& "&z=" & Rurl()

If you look at the URL formula for any system generated ADD record button, you will see a similar format.

The first line gives the start of the URL. You will need to get the DBID value from the advanced properties tab for the table. for example it might look like [_DBID_ORDERS]

The YYY needs to be the field ID# on the child table for the field called [Related Parent] (whatever your parent table is called. That is how the child gets connected opt the parent.

The WWW and ZZZ are just the field ID numbers of the field that you are populating. They are on the usage tab of the field or else you can choose to show them on your field list.


For numeric fields you need to flip them to be text

For text fields, you need to wrap them in that URLENcode, as URLs do not like spaces or special characters.

The very last part is optional. If upon save you want to return to the parent record after you save, then leave it in. If you want to "stick the landing" like in gymnastics, then leave it out and upon save you will stick on the child record.
Photo of Karen

Karen

  • 222 Points 100 badge 2x thumb
Follow-up question.... I need to add some fields to my formula. The fields I need to copy are check box fields. Is it possible to copy those?

Example, in Table A there are 3 check box fields:
 Business Group: Security
 Business Group: Collaboration
 Business Group: Enterprise Networking

How do I write the formula so that if one or more of these is checked on the form in Table A, they should also be checked on the form (the copied record) in Table B?
Thanks!!
& "&_fid_123=" & [my true false checkbox field]

It appears that you do not need to "ToText()" it or "URLEncode()" it, just  use it just as it is.

When I want to force a value into a checkbox field such as true to false, I always just use a 0 or 1

for example to force to true would be

& "&_fid_123=1"

There may be other ways that work, but that is how i do it.
Photo of EH

EH

  • 282 Points 250 badge 2x thumb

Hi

I'm using the above formula to copy some fields from table to table however the copied fields appear in format mm/dd/yyyy while in the original field they are dd/mm/yyyy, any solusion for this?
Photo of EH

EH

  • 282 Points 250 badge 2x thumb


Hi guys

I'm using the above formula to copy some fields from table to table however the copied fields appear in format mm/dd/yyyy while in the original field they are dd/mm/yyyy, any solusion for this?