How can I copy a record to a different table, while updating the value in two fields from the original?

  • 0
  • 2
  • Question
  • Updated 3 years ago
  • Answered
How can I copy a record to a different table, while updating the value in two fields from the original?  Use case is we have a proposal table that contains active proposals, and from time to time those proposals have to be revised.  At the time the proposal is  revised, we'd like to copy what we had up to that point to a Revision History table and then change the Revision value on the original record and change the status value on the original record.
Photo of Devona

Devona

  • 20 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
So, are you looking to have a single button do that?  How will the new revision value be calculated?  What's the pattern.  Like 1234, or A B C D.

What is the new status to be on the current record?
Photo of Devona

Devona

  • 20 Points
The new revision status is calculated via a formula field that I've set up separately, and it's a 1-2-3 pattern (would LOVE an A-B-C-D pattern but couldn't figure out an easy way to do that).  Status is a text field field, and will be "Working PSA Tasks".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
I have posted an app in the Exchange called URL formuals for Dummies, which helps you understand how to make formula URL buttons which will do successive actions.

Basically you want  to push a button which will do three successive actions.

1. Add a record to a table.

2. Edit the record that you are sitting on.

3. Display the record that you are sitting on.

So the technique is to use a formula variable for each of those three steps and then string them together.  If you have not used formula variables before, you can read the help info http://www.quickbase.com/user-assistance/Default.html#formula_variables.html

You will need to decide if you want to use Application tokens as an extra security layer.  I suggest for now, that you turn off that requirement by going to the Application Properties tab abd disabling that requirement.



// first we will create the record in another table.

// we need to populate each field, one by one by identifying its field ID# and what goes into that field.  The xx refers to the field ID#

var text URLONE = urlroot() & "db/" & [_DBID_ the table that you are writing to from its advanced properties tab] & "?act=API_AddRecord"

& "&_fid_xx=" & URLEncode([some field here])

& "&_fid_xx=" & URLEncode([some other field here])

& "&_fid_xx=" & URLEncode([some other other field here]);

// next we update the record we are sitting on.



var text URTWO = urlroot() & "db/" & dbid() & "?act=API_EditRecord&rid=" & ToText([Record ID#])


& "&_fid_xx=" & [New Revision value])

& "&_fid_xx=" & URLENCode("Working PSA Tasks");

// next we will need to display the record.



var text URLThree= urlroot() & "db/" & dbid() & "?a=dr&rid=" & ToText([Record ID#]);




// OK now we string them, all together.

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






Photo of Devona

Devona

  • 20 Points
I understand the concept, but need some clarification on this portion:

& "&_fid_xx=" & URLEncode([some field here])

fid will be the field in the new table that's being populated; all I have to do is replace [some field here] with the field ID/field name?  from which the value is derived?  How does it know from which table to pull that data?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Right, so it would be like
& "&_fid_15=" & URLEncode([Priority])

The value will pull fomr the record you are sitting on when you click the button.

The table name is the one that you specify in square brackets or if you are on a record that is the table that you are puling from, then you can use the short for dbid()

It knows which record, from the rid= .....  ie and Record ID =