ContributionsMost RecentMost LikesSolutionsRe: How do I extract only the Last Name from a USER name?To do this: Create a new Formula - Text field Put in the following formula: Trim(Part(UserToName([Quickbase User]),2," ")) Where [Quickbase User] is the field where your Quickbase user is. UserToName would convert the Quickbase user to the <First Name> <Last Name> Part would get the second part of the name on the <space> delimiter, which results in the last name. Now you can use that value to compare it with any other field. ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Save a record then revert itAh.. awesome! that's a great idea too. So I think if I understand you correctly, the initial button would take the user to the new form, AND populate it with the existing values from the original fields, and then the staff member can make any necessary changes, which will then get sent to the user for the approval step. Did I get this right? I think I will see if the delay in triggering the pipeline will create any workflow issues for our team (i.e: the waiting game for the record to revert back). If it doesn't, then what I have might work, otherwise, I might implement your suggestion. It's a good one even though it doubles the number of fields in my table :) Thanks Mike! ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Save a record then revert itThat's correct Mike. This would be in lieu of our staff having to retype in all the information in a record to send as a notification, our staff would instead just make the change, and save the record to send to the end user. Meanwhile the record would revert back to its original values, and would get written permanently only when the user approves it via their email button. (i.e: it would then be updated with their own Quickbase user, for auditing purposes) With that said, I think I got it resolved. I will do some more testing, but, in case someone else might have a similar scenario. What I did is I removed the second (Revert) part of the formula, and I created a Pipeline to trigger on the record if the field "Staging" is checked, (and if the modified date is within 5 minutes, for good measure) Then I used the values in there to get the previous value {{a.$prev.First Name}} , etc... This seems to have worked like a charm! Thanks! ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Save a record then revert itPlease bear with me as I try to explain the scenario. I'm looking for a couple things out of this: 1- Is the method I'm trying to implement the best way to do this, or is there a better one 2- I'm trying to correct the behavior of my current method. I'm almost there. The goal is this: - our provisioning team would edit a record (with the information that needs to change). - They push a button (Send for Approval), and check the "Staging" button. This button has the following code: //Since [old.field] doesn't work here, we will remember the old values within the formula var text OLDFIRSTNAME = [First Name]; var text OLDLASTNAME = [Last Name]; var number OLDRELATEDCOMPANYENROLLMENTFORM = [Related Company Enrollment Form]; var textlist OLDPRODUCTERP = [Product ERP]; var textlist OLDPRODUCTADDONAPP = [Product Add-On Apps]; var textlist OLDPRODUCTACCESSORYAPP = [Product Accessory Apps]; var text OLDPLATFORM = [Platform]; var text OLDEMAIL = [Email Address]; var text OLDPHONE = [Office Phone]; //Edit (and Save based on the "SaveBeforeNavigating" class) and switch the "Send Notification" field, to trigger the notification var text SENDFORAPPROVAL = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&rid=" & [Record ID#] & "&_fid_138=true" & "&_fid_140=true" & "&apptoken=xxxxxxxxxxxxxxxx"; //Once the notification is sent, revert back all the values in the record to what they were before the edit, then redirect back to the parent record. var text REVERT = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&rid=" & [Record ID#] & "&_fid_6=" & $OLDFIRSTNAME & "&_fid_7=" & $OLDLASTNAME & "&_fid_61=" & $OLDRELATEDCOMPANYENROLLMENTFORM & "&_fid_70=" & $OLDPRODUCTERP & "&_fid_71=" & $OLDPRODUCTADDONAPP & "&_fid_72=" & $OLDPRODUCTACCESSORYAPP & "&_fid_73=" & $OLDPLATFORM & "&_fid_9=" & $OLDEMAIL & "&_fid_10=" & $OLDPHONE & "&_fid_136=false" & "&rdr=" & URLEncode(URLRoot() & "db/" & [_DBID_COMPANY_ENROLLMENT] & "?a=dr&rid=" & [Related Company Enrollment Form]); //Put it all together "<a class='Vibrant Success SaveBeforeNavigating' href='" & $SENDFORAPPROVAL & "&NextURL=" & URLEncode($REVERT) & "'>Send for Approval - Edit</a>" I want this to Save the record. Which should create a "Notification" based on _fid_138 (Send Notification - Checkbox), which is now checked. The next step would now use the "OLD" variables in the formula, to "reset" those fields back to their original values. The end user now received a notification with a URL Formula for Approving that request, will require them to sign in, and write the record under their own credential, resulting in a fully audited entry. The problem I'm having with the current setup, having "SaveBeforeNavigating", the changes don't get reverted, and I get an XML return stating that there were no errors, when i hit back on the browser, it doesn't revert the changes. Looking at the resulting URL, I see that the "OLD" values are actually the "NEW" values. So, I guess they weren't retained through the save. Any ideas what I could change to get this workflow to work? (If possible). Could triggering a pipeline after the save work? and if so, is it possible to grab the previous values of the fields within the pipeline? Alternatively, if I'm over complicating this in my head, if anyone might have any better way to accomplish this, I welcome the suggestions. Thanks! ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------Re: Attempting to create a record via email approval button with API_GenAddRecordI was about to delete the post, but thought I'd keep it in case anyone else may be looking for a similar solution. Turns out that I was over-complicating the breakdown of the multi-select fields. (used that break down method to design bulleted <li> points under a different scenario. The solution here is simply to refer to the actual multi-select field itself in the API_AddRecord call, and it works like a charm. So the working code for this would be something like this: var text SIGNIN = URLRoot() & "db/main?a=SignIn"; var text APPROVED = URLRoot() & "db/" & Dbid() & "?a=API_AddRecord&_fid_6=" & [First Name] & "&_fid_7=" & [Last Name] & "&_fid_61=" & [Related Company Enrollment Form]& "&_fid_70=" & [Product ERP] & // Multi-select field "&_fid_71=" & [Product Add-On Apps] & // Multi-select field "&_fid_72=" & [Product Accessory Apps] & // Multi-select field "&_fid_73=" & [Platform] & "&_fid_9=" & [Email Address] & "&_fid_10=" & [Office Phone] & "&apptoken=xxxxxx" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dbpage&pageID=10)"; "<a class='Vibrant Success' href='" & $SIGNIN & "&NextURL=" & URLEncode($APPROVED) & "'>APPROVED</a>" Cheers! ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Attempting to create a record via email approval button with API_GenAddRecord Hi, I'm trying to create a button that gets sent to the end user in an email message. My Use Case: Our internal support team would fill a form on behalf of the user with the necessary information, and the URL would get populated while the agent is filling the form. Once done, the agent would send that URL via some notification workflow (which I haven't quite thought through fully yet), containing an "Approve" or "Deny" button to the end user. If the user Approves, then they will be redirected to Sign-In (if not already signed in), and the record would get created, with them being the owner, thereby creating a proper audit record of their approval. All is working except populating some multi-select fields. Below is a truncated snippet of how I'm breaking down the values of the multi-select fields and inserting them into the URL formula. Some things I have tried: - I tried using the API_GenRecordAddForm , which actually works well with the multi-select fields, however, that API call can't automatically save the record, which is a requirement for this scenario. - I have looked into API_FieldAddChoices, but that is an action on its own, and I'm not sure if would be compatible while I'm trying to create a new record? maybe save and send to a "&NextURL" to an ?a=API_FieldAddChoices? - My other challenge with using this one -- if I were to do so -- is how to create a formula that breaks down the values, as this API_FieldAddChoices requires a "Choice=X&Choice=Y&Choice=Z" formatting, etc... Any guidance appreciated! // ---------- Product Bullets -------------- //ERP PRODUCTS - MULTI-SELECT FIELDS var text ERPPRODUCTS = ToText([Product ERP]); var text ERPPARTONE = Trim(Part($ERPPRODUCTS,1,";")); ...etc... var text ERPPARTTWENTY = Trim(Part($ERPPRODUCTS,20,";")); var text URLERP = $ERPPARTONE & ";" ...etc... ";" & $ERPPARTTWENTY; // Add-On Products var text ADDONPRODUCTS = ToText([Product Add-On Apps]); var text ADDONPARTONE = Trim(Part($ADDONPRODUCTS,1,";")); ...etc... var text ADDONPARTTWENTY = Trim(Part($ADDONPRODUCTS,20,";")); var text URLADDON = $ADDONPARTONE & ";" ...etc... ";" & $ADDONPARTTWENTY; // Accessory Products var text ACCESSORYPRODUCTS = ToText([Product Accessory Apps]); var text ACCESSORYPARTONE = Trim(Part($ACCESSORYPRODUCTS,1,";")); ...etc... var text ACCESSORYPARTTWENTY = Trim(Part($ACCESSORYPRODUCTS,20,";")); var text URLACCESSORY = $ACCESSORYPARTONE & ";" & ...etc... ";" & $ACCESSORYPARTTWENTY; // ------------ End Product Bullets ------------------- var text SIGNIN = URLRoot() & "db/main?a=SignIn"; var text APPROVED = URLRoot() & "db/" & Dbid() & "?a=API_GenAddRecordForm" & "&_fid_6=" & [First Name] & "&_fid_7=" & [Last Name] & "&_fid_61=" & [Related Company Enrollment Form] & "&_fid_70=" & $URLERP & "&_fid_71=" & $URLADDON & "&_fid_72=" & $URLACCESSORY & "&_fid_73=" & [Platform] & "&_fid_9=" & URLEncode([Email Address]) & "&_fid_10=" & URLEncode([Office Phone]) & "&apptoken=xxxxx" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dbpage&pageID=10)"; "<a class='Vibrant Success' href='" & $SIGNIN & "&NextURL=" & URLEncode($APPROVED) & "'>APPROVED</a>" ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Trying to ""Count"" the number of selections in a multi-select text fieldOk thanks Mark. Will open a support case to get some further guidance. ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Trying to ""Count"" the number of selections in a multi-select text fieldSorry Mark. Didn't mean to confuse things. You can disregard my "If" statement. Here is a screenshot of the values. The top one is a reference table report that shows the actual values of the multi-select field. You can see that there are 2 values: "Quickbooks Enterprise" and an "Empty" value. In the bottom screenshot is the Summary Report, showing the "Distinct Count" including the empty value in that multi-select field. What I'm looking for is for that number to be "1" instead of "2" (basically excluding the "empty" value) Does this clarify things? ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Trying to ""Count"" the number of selections in a multi-select text fieldHm. I don't think that made a difference. Still seems to be picking up the empty values. As a side note, I'm not sure understand the meaning of Trim(expression) <> "" ... is this a shorthand for my "If" statement? If(Trim(Part($value,1,";")) <> "", Trim(Part($value,1,";")),"") ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------ Re: Trying to ""Count"" the number of selections in a multi-select text fieldSure.. it's actually exactly the one suggested in this thread: var text value = ToText([Product Add-On Apps]); Count( Trim(Part($value,1,";")), Trim(Part($value,2,";")), Trim(Part($value,3,";")), Trim(Part($value,4,";")), Trim(Part($value,5,";")), Trim(Part($value,6,";")), Trim(Part($value,7,";")), Trim(Part($value,8,";")), Trim(Part($value,9,";")), Trim(Part($value,10,";")), Trim(Part($value,11,";")), Trim(Part($value,12,";")), Trim(Part($value,13,";")), Trim(Part($value,14,";")), Trim(Part($value,15,";")), Trim(Part($value,16,";")), Trim(Part($value,17,";")), Trim(Part($value,18,";")), Trim(Part($value,19,";")), Trim(Part($value,20,";")) ) This formula is being used as a report formula field in the Summary Report. ------------------------------ George Khairallah CTO gotomyerp, LLC ------------------------------