NirajShah
Qrew Cadet
Joined 7 years ago
User Profile
User Widgets
Contributions
Concatenating date and text fields into one date/time field
I am trying to concatenate four fields into one date/time field but struggling with the general syntax. I have a [start date] field which is a date field, i have an [hour] field which is text with options 1-12, a [minute] field with options :00, :15, :30, :45, and an [ampm] text field with options for AM/PM. Any idea how to combine these four fields into one date/time formula field??Automation to add one record to another table when something is triggered in another table
I have three tables: Purchase Orders, Cost Sheets, and Entries. The relationship is such that a Purchase Order can have multiple entries and Cost Sheets can have multiple entries. I have an "Approval" button on the Cost Sheets table, when triggered, automatically approves all entries associated with that cost sheet. On the entries table, users are required to select a Vendor for each entry. Each entry is associated with a Cost Sheet, such that one cost sheet may have anywhere from 1 to infinite number of entries that may also belong to 1 to infinite number of vendors. The challenge I am having is how to automatically generate a purchase order for each __unique __vendor when a cost sheet (and entries) are approved. Currently, the automation fails since the related vendor field is required to be unique, and once I remove that requirement, the automation runs but creates multiple purchase orders for the same vendor.Re: Rich Text Formula Field for Approve / Reject button and redirection
Got it working, finally! Thanks for all the help! Here is the final code below. The first button edits the current record, redirects to another record in another table and edits that record, and then brings the user back to the current record they were on. How can I mark this as solved or answered? var text approve = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Approved") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Related Store Item]) & "&_fid_14=" & URLEncode([# of Features Requested])) & URLEncode("&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]))); var text reject = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Rejected") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]));0Views0likes1CommentRe: Rich Text Formula Field for Approve / Reject button and redirection
Good point - and this may be where I am messing up. The first id 14 is being set as a user field in the current table. Then I am (attempting) to do a redirect to another table and record to define that tables fid 14 as a value from the first table. I hope that makes sense in all my madness!0Views0likes0CommentsRe: Rich Text Formula Field for Approve / Reject button and redirection
Thanks for all the feedback, I think I'm close to figuring this one out. Here's where I am now; Here is the meat of my formula that I'd love feedback on. The version below was accepted by QB but for some reason fid 14 did not populate when the approve option was selected but did when reject was selected. As I commented below the redirect I have in the approve is not populating the target table and goes to an xml page (see below): This XML file does not appear to have any style information associated with it. The document tree is shown below. <qdbapi> <action>API_EditRecord</action> <errcode>0</errcode> <errtext>No error</errtext> <rid>1</rid> <num_fields_changed>0</num_fields_changed> <update_id>1545345688859</update_id> </qdbapi> var text approve = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Approved") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Related Store Item])) & "&_fid_14=" & URLEncode([# of Features Requested]); //the redirect is not populating the fields and when i add another redirect it comes back with a syntax error var text reject = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Rejected") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#])); Funny thing is, when I remove the redirect from the approval, fid 14 is populating fine.0Views0likes3CommentsRe: Rich Text Formula Field for Approve / Reject button and redirection
Thanks to your help, I'm super close to where I want to be! Here is my code for the rich text field that I created, all is well, except for the last redirect for the approved button. I highlighted in bold, QB is throwing an "expected text" syntax error. I tried to follow your convention above, but have been scratching my head over it for an hour now.. var text approve = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Approved") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Related Store Item]) & "&_fid_14=" & URLEncode([# of Features Requested])); & URLEncode("&rdr=" & URLEncode(URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#])); var text reject = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Rejected") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&rdr=" & URLEncode(URLRoot() & "db/xxxxxx?a=dr&rid=" & ([Record ID#])); var text cancel = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_12=" & URLEncode("Cancelled") & "&_fid_13=" & URLEncode(Now()) & "&_fid_14=" & URLEncode(User()) & "&z=" & Rurl(); //Colors of the buttons. The second color is the mouseover color. var text buttoncolor= "@#76c043@#367c2b@"; var text buttoncolortwo= "@#c04e43@#89362e@"; var text buttoncolorthree= "@#efab49 @#a96a0f@"; var text buttonone= $approve & $buttoncolor & "Approve"; var text buttontwo= $reject & $buttoncolortwo & "Reject"; var text buttonthree= $cancel & $buttoncolorthree & "Cancel"; var bool ShowButtonOne = If(Now()>[Date Created], true, false); // ========================Begin Pattern======================== var text onclickone = "\" onclick=\"LinkAway('"; var text onclicktwo = "', false); return false;\""; //Button bar If($ShowButtonOne=true, "<div style=\"position:relative; width: 330px; height: 25px; \">" & If($buttonone<>"", "<a href=\"" & Left($buttonone, "@") & $onclickone & Left($buttonone, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none; \"> <span style=\"position: absolute; display: table-cell; text-align:center; width:160px; height:25px; border-radius: 5px; background-color:" & Part($buttonone,2, "@") & "; font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttonone,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttonone,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttonone, "@") & "</span></span></a>","") & If($buttontwo<>"","<a href=\"" & Left($buttontwo, "@") & $onclickone & Left($buttontwo, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none;\"> <span style=\"position: absolute; display: table-cell; text-align:center; width:160px; height:25px; border-radius: 5px; background-color:" & Part($buttontwo,2, "@") & "; left: 170px; font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttontwo,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttontwo,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttontwo, "@") & "</span></span></a>","") & //If($buttonthree<>"" and [Status]<>"On Hold","<a href=\"" & Left($buttonthree, "@") & $onclickone & Left($buttonthree, "@") & $onclicktwo & "\" style=\"color:white;text-decoration: none;\"> <span style=\"position: absolute; display: table-cell; text-align:center; width:130px; height:25px; border-radius: 5px; background-color:" & Part($buttonthree,2, "@") & ";left: 280px; font-weight: bold; \" onMouseOver=\"this.style.backgroundColor='" & Part($buttonthree,3, "@") & "'\" onMouseOut=\"this.style.backgroundColor='" & Part($buttonthree,2, "@") & "'\"><span style=\"position:relative; top:25%;\">" & Right($buttonthree, "@") & "</span></span></a>","") & "</div>", "" )22Views0likes0CommentsRe: Rich Text Formula Field for Approve / Reject button and redirection
Agreed, thanks for the assistance. Here is what I have for the reject button: var text reject = URLRoot() & "db/" & [_DBID_] & "?a=API_EditRecord&apptoken=xxxxx&rid=" & URLEncode ([Record ID#]) & "&_fid_xx=" & URLEncode("Reject") & "&_fid_xx=" & URLEncode(Now()) & "&_fid_xx=" & URLEncode(User()); var text DisplaySomeRecord = URLRoot() & "db/xxxxx?a=dr&rid=" & ([Record ID#]); $reject & URLEncode("&rdr=" & URLEncode($DisplaySomeRecord)) This works, but redirects to an xml page: This XML file does not appear to have any style information associated with it. The document tree is shown below. <qdbapi> <action>API_EditRecord</action> <errcode>0</errcode> <errtext>No error</errtext> <rid>1</rid> <num_fields_changed>1</num_fields_changed> <update_id>1545343689363</update_id> </qdbapi>3Views0likes0Comments