Discussions

 View Only
Expand all | Collapse all

Time In/Time Out Formula

  • 1.  Time In/Time Out Formula

    Posted 05-16-2022 19:19
    Hello, can someone help me with the formula for getting the current date/time and set it as value on field with a click of a button? Basically, I'm trying to create a time in/out table where it will have two buttons and two fields. Clicking the Time In button populates the Time Int field with the current date/time, same thing for the Time Out button.

    ------------------------------
    frnmj
    ------------------------------


  • 2.  RE: Time In/Time Out Formula

    Posted 05-16-2022 19:25
    not tested but try thisas a formula URL button

    var text URL = urlroot() & "db/" & dbid() & "?act=API_EditRecord"
    & "&apptoken=xxxxxxxxx" // if you have app tokens enabled
    & "&_fid_99=now";  /// assumes the field ID 99 is the date time field to be updated.

    var text RefreshPage = URLRoot() & "db/" & Dbid() & "?a=doredirect&z=" & Rurl();


    $URL
    & "&rdr=" & URLEncode($RefreshPage)

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Time In/Time Out Formula

    Posted 05-16-2022 19:33
    Edited by frnmj 05-16-2022 19:52

    Thank you so much for the quick reply, I tried it out but I could not save the formula due to an error. It says it's Expecting datetime but found text.



    ------------------------------
    frnmj
    ------------------------------



  • 4.  RE: Time In/Time Out Formula

    Posted 05-16-2022 19:52

    I changed the field type from "Formula - Date/Time" to "Formula - Text" and the error went away. However, when I tried it out it takes me to this page:



    ------------------------------
    frnmj
    ------------------------------



  • 5.  RE: Time In/Time Out Formula

    Posted 05-16-2022 20:24
    The field type for the button to push is formula URL.  You need to adjust the 99 to be the field ID of the date time field you want to update.

    I suggest that you disable the need for application tokens.  That is set in the App Properties.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Time In/Time Out Formula

    Posted 05-16-2022 21:45

    I have now changed the field type to "Formula - URL" and disabled application tokens. However, when I click on the button, it takes me to this page: 



    ------------------------------
    frnmj
    ------------------------------



  • 7.  RE: Time In/Time Out Formula

    Posted 05-16-2022 21:48
    Please post your code for the button

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Time In/Time Out Formula

    Posted 05-16-2022 21:50
    Actually,   I see I was missing the record ID parameter, please adjust this part of the code.

    var text URL = urlroot() & "db/" & dbid() & "?act=API_EditRecord"
    & "&rid=" & [Record ID#]
    & "&_fid_99=now";  /// assumes the field ID 99 is the date time field to be updated.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Time In/Time Out Formula

    Posted 05-16-2022 22:14
    Edited by frnmj 05-16-2022 22:25
    Thank you so much! It's working now. Right now, it's working in Edit/View so I'll use this one for the Time Out Button.



    Is there a way to make it work in "Add" so I can use it for Time In as well? For now, I'm using a Dynamic Form Rule to set the field value of Time In field to the current date/time after the record is saved. 

    ------------------------------
    frnmj
    ------------------------------



  • 10.  RE: Time In/Time Out Formula

    Posted 05-17-2022 09:41
    The formula for that is more complicated and the field type need to be formula rich text.

    var text RID = IF([Record ID#] > 0, ToText([Record ID%]), "%%rid%%)";

    var text URL = urlroot() & "db/" & dbid() & "?act=API_EditRecord"
    & "&rid=" &  $RID
    & "&_fid_99=now"; 

    "<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #4b7097; border-radius: 5px; color: #ffffff; display: inline-block; padding: 0px 0px 0px 0px; width:100px; text-align: center; text-shadow: none; border: 2px solid #030404; font-size: 12px \"href='"
    & $URL
    & "'>Clock In</a>"


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Time In/Time Out Formula

    Posted 05-17-2022 14:01
    Edited by frnmj 05-17-2022 14:30

    I really appreciate your help. I applied your formula and made sure to change the fid to the correct one but I get an error. Here's a screenshot:

    Also, I think there's a typo in the first line:

    var text RID = IF([Record ID#] > 0, ToText([Record ID%]), "%%rid%%)";

    I changed it to this:

    var text RID = If([Record ID#] > 0, ToText([Record ID#]), "%%rid%%)");

    EDIT

    I found an extra parenthesis in the first line and removed it, here's the updated one:

    var text RID = If([Record ID#] > 0, ToText([Record ID#]), "%%rid%%");

    The error message has now changed to:
    <errdetail>Missing "rid" parameter.</errdetail>



    ------------------------------
    frnmj
    ------------------------------



  • 12.  RE: Time In/Time Out Formula

    Posted 05-17-2022 14:46
    Please copy paste your current formula.  I can't debug a formula that I don't see :)

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Time In/Time Out Formula

    Posted 05-17-2022 14:58

    Sorry about that, here's the formula:

    var text rid = If([Record ID#]>0, ToText([Record ID#]), "%%rid%%");

    var text URL = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord"
    & "&rid=" & $rid
    & "&_fid_6=now";

    "<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #4b7097; border-radius: 5px; color: #ffffff; display: inline-block; padding: 0px 0px 0px 0px; width:100px; text-align: center; text-shadow: none; border: 2px solid #030404; font-size: 12px \"href='"
    & $URL
    & "'>Clock In</a>"



    ------------------------------
    frnmj
    ------------------------------



  • 14.  RE: Time In/Time Out Formula

    Posted 05-17-2022 15:09
    Hmmmm, the formula looks correct.  Does the formula fail in both Add and edit modes?
    Do you have the settings for the table in advanced settings set to 

    Learn more

     and this is a formula rich text field type?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Time In/Time Out Formula

    Posted 05-17-2022 15:28

    Seems like it works on Edit mode as it successfully changed the value but it takes me to this page.

    "Auto save when redirected away from the page." is enabled and field type is Formula - Rich Text. 



    ------------------------------
    frnmj
    ------------------------------



  • 16.  RE: Time In/Time Out Formula

    Posted 05-17-2022 15:22
    Apologies for barging in, but there is one sneaky drawback to the %%rid%% method that may be the issue here.

    If there are no other changes on the form and therefore nothing to save, the rid in the URL will not be replaced.

    So if you or your users are going straight from the new record to the button, you'll get this error.

    -Sharon

    ------------------------------
    Quick Base Junkie
    Quick Base Junkie
    https://quickbasejunkie.com
    ------------------------------



  • 17.  RE: Time In/Time Out Formula

    Posted 05-17-2022 15:32
    You're right, there's nothing else inside the form except for the Clock-In button. My idea of the process is that the user will have to create a new record every time they clock in. That's why I only have the Clock-In button in the form.

    ------------------------------
    frnmj
    ------------------------------



  • 18.  RE: Time In/Time Out Formula

    Posted 05-17-2022 15:42
    There is another error in my code aside from Sharon's comment

    var text RID = If([Record ID#]>0, ToText([Record ID#]), "%%rid%%");

    var text Stamp = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord"
    & "&rid=" & $RID
    & "&_fid_6=now";

    var text Display = URLRoot() & "db/" & Dbid() & "?a=dr&rid="

    var text URL = 
    $Stamp
    & "&rdr=" & URLEncode($Display) & $RID
     

    "<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #4b7097; border-radius: 5px; color: #ffffff; display: inline-block; padding: 0px 0px 0px 0px; width:100px; text-align: center; text-shadow: none; border: 2px solid #030404; font-size: 12px \"href='"
    & $URL
    & "'>Clock In</a>"



    If the above does not work I will set up a test myself.  If Sharon's comment is correct (which is likely is!), then you can get around that by setting up a dummy date field called dummy and have it default to today's date.  That should cause a change in the record when saved.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 19.  RE: Time In/Time Out Formula

    Posted 05-17-2022 16:26
    Edited by frnmj 05-17-2022 17:11

    With the new formula, it now works perfectly in Edit mode but throws the same error when in Add mode (Which confirms Sharon's comment).

    I went ahead and added the dummy date field and ticked Today as the default value but it still gives me the same error message. Am I missing a step?

    EDIT

    I got it to work by using a Dynamic Form Rule instead of defaulting the value of the dummy via field properties. I unticked it default value option and let the Dynamic Form Rule "change" the value of the dummy field. 

    Thank you so much! I really appreciate it. 

    EDIT

    Is there a way to redirect it to the report/main table page instead of the Saved view mode?

    ------------------------------
    frnmj
    ------------------------------



  • 20.  RE: Time In/Time Out Formula

    Posted 05-17-2022 19:40
    Thx for sharing that with me and @Sharon Faust.  Interesting that a default value does not change the record on Add New , but a dynamic form rule does.​
    This code should land you on the table home page.

    var text Stamp = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord"
    & "&rid=" & $RID
    & "&_fid_6=now";

    var text DisplayTableHomePage = URLRoot() & "db/" & Dbid() & "?a=td"

    var text URL = 
    $Stamp
    & "&rdr=" & URLEncode($DisplayTableHomePage)


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------