Discussions

 View Only
Expand all | Collapse all

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

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

    Posted 10-13-2017 18:54
    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'


  • 2.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-13-2017 18:59
    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.


  • 3.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-17-2017 19:25
    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()


  • 4.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-17-2017 19:31
    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">http://www.YOURCOMPANY.quickbase.com">www.YOURCOMPANY.quickbase.com


  • 5.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-17-2017 19:38
    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


  • 6.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-17-2017 19:35
    Ayoub,
    When you say it does not work, what are the symptoms of the problem.  What is the error message?


  • 7.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-17-2017 19:38
    I'm able now to copy some fields now, I figured out the 'fid' values was
    wrong. I'll try to copy the others and see what happens. Thanks


  • 8.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-18-2017 14:19
    What I wasn't able to copy is 'foreign keys' of other tables from the "Requisition" table.
    per example, I have a "Vendor" table that has a relation with the "Requisition" table containing many lookup fields :
    I have another table "Requisition Items" which i'm using a report of it in my requisition form :


    I am not able to copy the data of the vendor & Requisition items from "Requisition" table to "PO" table .  Even that I specified the fields in the formula :
    URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS] & "?a=API_GenAddRecordForm&_fid_34=" & URLEncode ([Requisition items])& "&_fid_10=" & URLEncode ([Purchase Authority])& "&_fid_9=" & URLEncode ([Descrition])& "&_fid_11=" & URLEncode ([Contract #])& "&_fid_73=" & URLEncode ([Vendor])& "&_fid_18=" & URLEncode ([Voucher #])& "&_fid_19=" & URLEncode ([Voucher Date])& "&_fid_14=" & URLEncode ([Authorized By])& "&_fid_15=" & URLEncode ([Authorization Date])& "&_fid_12=" & URLEncode ([Requested By])&"&_fid_54=" & URLEncode ([Creation Date])& "&_fid_90=" & URLEncode ([Order #])& "&_fid_53=" & URLEncode ([Vendor])&"&z=" & Rurl()


  • 9.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 00:41
    Can you better explain your question, as i cannot understand it.

    Also, I cannot help deciphir any syntax errors in your formula, if that is a problem as its all written on one long line


    I suggest that you write your formuals vertically like this
    URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS] & "?a=API_GenAddRecordForm"
    & "&_fid_34=" & URLEncode ([Requisition items])
    & "&_fid_10=" & URLEncode ([Purchase Authority])& "&_fid_9=" & URLEncode ([Descrition])
    & "&_fid_11=" & URLEncode ([Contract #])
    & "&_fid_73=" & URLEncode ([Vendor])
    & "&_fid_18=" & URLEncode ([Voucher #])
    & "&_fid_19=" & URLEncode ([Voucher Date])
    & "&_fid_14=" & URLEncode ([Authorized By])
    & "&_fid_15=" & URLEncode ([Authorization Date])
    & "&_fid_12=" & URLEncode ([Requested By])
    &"&_fid_54=" & URLEncode ([Creation Date])
    & "&_fid_90=" & URLEncode ([Order #])
    & "&_fid_53=" & URLEncode ([Vendor])
    &"&z=" & Rurl()

    Please explain what the symptoms of the problem are.  Do you have a syntax error?  or what is not working.

    Also out of the blue you mention a PO table.  Does that table have a relationship to any of your other tables.?


  • 10.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 13:28
    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 ?


  • 11.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 13:51
    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.


  • 12.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 14:32
    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 ?


  • 13.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 15:15
    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.


  • 14.  RE: How can I create a record in a table when I click in a button of another table (with conserving some values)

    Posted 10-19-2017 18:43
    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.