I need a formula that will open an alternate version of a form to populate a field, save, and then edit a couple of fields on the full form.

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I need a formula that will open an alternate version of a form to populate a field, save, and then edit a couple of fields on the full form. I had 2 separate actions. 1. Click on a button, enter a code, and pull up information. Save the information to the form... and then 2. Click on a button to check a box and time stamp. I would like to make this a single step, and I know it's possible, but I haven't been able to make it work. Here's my formula:

If([Recon] = false,
"<a " & $style & " href='" & Dbid() & "?a=API_EditRecord&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&rid=" & [Record ID#] & "&dfid_12&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&rid=" & [Record ID#] & "&_fid_44=" & "Recon In" & ", " & [Driver] & "&_fid_88=1" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#] & "")) & "'>RECON IN</a>",

"<a " & $style & " href='" & Dbid() & "?a=API_EditRecord&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&rid=" & [Record ID#] & "&dfid_12&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&rid=" & [Record ID#] & "&_fid_44=" & "Recon Out" & ", " & [Driver] & "&_fid_88=0" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#] & "")) & "'>RECON OUT</a>"
)

I've been at this all night, so I would greatly appreciate any ideas. Thank You!
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb

Posted 10 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I have an app in the Exchange called URL Formulas for dummies which explains one way to nest formula URLS.

Note: formula URLs and not formula Rich Text fields.

Define your steps and then nest them like this.

Define each step as a separate formula variable, in order to improve readability. Then just slice off as much of this salamiI as you need.

$URLONE
& "&rdr=" & URLEncode($URLTWO)
& URLEncode("&rdr=" & URLEncode($URLTHREE))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))
& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSEVEN))))))
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
Concentrating on this first portion of the formula:

Dbid() & "?a=API_EditRecord&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&rid=" & [Record ID#] & "&dfid_12&rdr="

You are attempting to use API_EditRecord to edit the record [Record ID#] using form dfid=12 ( I think you have a typo here) but you haven't supplied which field it to be changed or what its value is to be.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Her is an example showing how to use formula variables

var text AddRecord =
URLRoot() & "db/" & [_DBID_PO_DETAIL_LINES]
& "?act=API_AddRecord&_fid_6=" & URLEncode ([Purchase Order])
& "&_fid_17=" & [Qty]
& "&_fid_10=" & [Related Priced Material];

var text DeleteRecord =
URLRoot() & "db/" & [_DBID_PO_DETAIL_LINES]
& "?act=API_DeleteRecord&rid=" & [Record ID#];

var text DisplayRecord=
URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS]
& "?a=dr&rid=" & [Purchase Order];

$AddRecord
& "&rdr=" & URLEncode($DeleteRecord)
& URLEncode("&rdr=" & URLEncode($DisplayRecord))
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Mark, I read your pdf on formula, and I tried nesting. I am using a rich text formula, because I am creating my own buttons, so I wanted to be able to do this in the Formula Text Version. I have a formula that allows me to check the box and time-stamp, and THEN open the form, but I need the information from the form to Timestamp with the information. This is in a record that already exists, I'm not creating a new one. Also, I d didn't supply a field because the field requires manual entry, but when someone enters the code, I wanted the other 2 steps to carry out (check the box, and enter a time stamp). Maybe I do have to keep this a 2-step process, but I was hoping to consolidate.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Here's my code for being able to do this in reverse:

If([Recon] = false,
"<a " & $style & " href='" & Dbid() & "?a=API_EditRecord&rid=" & [Record ID#] & "&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&_fid_44=" & "Recon In" & ", " & [Driver] & "&_fid_88=1" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=er&rid=" & [Record ID#] & "&dfid=12") & "'>RECON IN</a>",
"<a " & $style & " href='" & Dbid() & "?a=API_EditRecord&rid=" & [Record ID#] & "&apptoken=d58b2yeetqfh3d7snhfjdjf7sv2&_fid_44=" & "Recon Out" & ", " & [Driver] &"&_fid_88=0" &  "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=er&rid=" & [Record ID#] & "&dfid=12") & "'>RECON OUT</a>"
)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Two comments

API _Edit Record will Edit a Record. It does not stop midway for user input.

The syntax for getting a Formula a Rich Text field is more difficult. I suggest that you get a regular formula URL working first.

I suggest that for readability you use formula variables.

You can probably get this to work with using “?a=er to have the user Edit the record manually, and then use NextURL=. ...

So it would be like

var text ManualEdit = .........a=er....;

var text Edit = ......API_EditRecord ....;

var text DisplayRecord= ..... ?a=dr....;

$ManualEdit
& “& NextURL=“ & URLEncode($Edit)
& “&URLEncode(“&rdr=“ & URLEncode($DisplayRecord))
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
I do have the code working as far as doing the 2 step process, which there are benefits to actually. I really need to learn more about coding. I can get what I need done by sourcing the code from all of the QuickBase resources, but would love to become fluent.  Any recommendations for what I should study?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I’m not aware of a single source to study. I have a collection of techniques saved for different situations.

Contact me off line if you like for one on one assistance. QuickBaseCoach.com.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
I just checked out your site! I didn't realize you actually provided the service. I'm bookmarking your site. I always have plenty of QuickBase projects, so I'm sure I'll be in touch!