How can I create a record in a table when I click in a button of another table (with conserving some values)

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a "Requisition" table, with a button "Make into Purchase Order".
Another table is "Purchase Orders" . I would like a button in the Requisition form when clicked, it would creates a PO with the same values.
 I added a "Req ID" in the PO Table, the Req Table also has a field "PO ID".

I'm trying with copyMasterDetailButtonHandler, but  can only duplicate the record in the same table. I want to copy it from 'Req table' to ' PO table'
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
  • confident

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You can do it with a formula url button.  Either using the API_AddRecord or API_GenAddRecordForm.

You will have to define the values that you want to copy, but that is pretty straight forward.  An example is what appears in any of your default "add" buttons.


URLRoot() & "db/" & [_DBID_MESSAGES] & "?a=API_GenAddRecordForm&_fid_53=" & URLEncode ([Record ID#])& "&z=" & Rurl()

Where the line:
&_fid_53=" & URLEncode ([Record ID#])& "
Is the information that is being written to the new form.
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
I couldn't make it work, I used API_GenAddRecordForm
URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS] & "?a=API_GenAddRecordForm&_fid_6=" & URLEncode ([Record ID#])& "&_fid_29=" & URLEncode ([Vendor])&  "&z=" & Rurl()
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'm not 100% sure, but because the table [DBID_Purchase_Orders] is in a different app, you might have to hard code the dbid (i.e. bm4a6kyi8).

Is it in the same domain?  i.e www.YOURCOMPANY.quickbase.com
(Edited)
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
Yes there are in the same domain, I'm able now to copy some fields, I figured out the 'fid' values was wrong. I'll try to copy the others and see what happens. Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Ayoub,
When you say it does not work, what are the symptoms of the problem.  What is the error message?
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
Sorry if I wasn't too clear.

I don't have any error in my formula. The problem is that I'm not able to copy data from 'Requisition table' that are from other tables 'Vendor' and 'Requisition item' tables.

I want to copy data from 'Requisition' form to 'PO' form with a click of a button 'Make into a PO'
The requisition table has relationships with two tables which are : 'Vendor' and 'Requisition Items'.
The PO table has relationships with two tables which are : 'Vendor' and 'PO items'

Everything works great except :

 copying the 'requisition items' from 'Requisition' to 'PO'  &  copying the 'Vendor' data from 'Requisition' to 'PO' .

No errors in my formula but these two lines don't seem to copy the data from 'Req' to 'PO':
& "&_fid_34=" & URLEncode ([Requisition items])
& "&_fid_53=" & URLEncode ([Vendor])
Is it because they are data from other tables (Foreign keys), they need a certain method ?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Are you expecting that code to be able to copy in multiple line items shen you do this
& "&_fid_34=" & URLEncode ([Requisition items])

If so, that will not work, and a whole different approach would be needed to create multiple requisition line items.  That would require more advanced techniques.


Ads for the vendor not copying, are you actually trying to copy the vendor name into a data entry text field, or are you actually trying to populate the field typically called [Related Vendor].

That seems far more likely that you need to copy [Related vendor] into your PO so that a relationship can pull in all the vendor information.
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
Yes I was trying to copy the report 'Requisition items' to 'PO' with this line :
& "&_fid_34=" & URLEncode ([Requisition items])

What kind of procedure should I follow to copy 'Requisition items'  from ''Requisition' to 'PO' along with the other fields ?

I'm trying to copy the 'Vendor' to a data entry text field. Are you saying a relationship between 'Requistion' and 'PO' would solve the problem ?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
The vendor issue is the easier part of your question.  There is no reason that it should not work, if your source field of [vendor] has the name of the vendor in it and the target field is a data entry text field and you have the correct field ID# of 53.

As for getting the lines to copy across there are two or three approaches I can see.

Contract with a QSP for a scripting solution.
Contract with someone like myself who does not typically use a scripting solution, but can still make it happen in one click using a saved table to table import and using API_RunImport.  The solution is native and once you understood it, you could use it in many situations, and you would not need to learn script - its all native.

Thew problem is that it would take me too much time to type out the step by step as to how to set that up.

The other solution is that when you create the PO from the Requisition, maybe you can just have a Report link field on the Purchase Order which would show the actual line items from the Requisition right on the Purchase order form.

A Report link field does not need to have an actual relationship.  It just needs two fields in common between the form the record that that is the "Parent' and the "child" record which has the line items.  So if you record the [Record ID#] of the Requisition onto the PO record, then you can just have a Report link to show those same line items on the PO.
Photo of Ayoub Salhi

Ayoub Salhi

  • 230 Points 100 badge 2x thumb
The Report link worked perfectly for the Requisition items. The problem with the vendor field is that I was copying it in the lookup field of vendor in 'PO' table. So I created a new field that was able to do it.
Thanks a lot for the help.