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 2 years 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,842 Points 1k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
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

  • 30,224 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.
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,842 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,842 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>"
)
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,842 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?
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,842 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!