Getting Started

 View Only
Expand all | Collapse all

Current Week in a query

  • 1.  Current Week in a query

    Posted 10-28-2022 09:32
    What is the correct syntax for the current week in this query?
    I have looked but cannot find it

    var text THISWEEK ="{8.EX.'CURRENT WEEK'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'};

    ------------------------------
    Hans Hamm
    ------------------------------


  • 2.  RE: Current Week in a query

    Posted 10-28-2022 09:51
    The Quickbase Junkie has some great resources.  I was able to link though one of her documents to get to a Quickbase help[ page which led to this page

    https://helpv2.quickbase.com/hc/en-us/articles/4418302291860

    Operator Description

    this wk

    Starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.

    XML example:

    <query>{7.IR.'this wk'}</query>

    URL example:

    https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+wk'}

    This example returns:

    All records where the date in fid 7 occurs within the time period that starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.



    hence,  likely the syntax is 

    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'};


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Current Week in a query

    Posted 10-28-2022 10:10

    Mark I tried that originally and the error message came back as

    Please check the syntax of your formula. Look for mismatched parenthesis.... and the . after "IR" is highlighted in red



    ------------------------------
    Hans Hamm
    ------------------------------



  • 4.  RE: Current Week in a query

    Posted 10-28-2022 10:51
    Please post (copy paste) your formula that fails the formula editor.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Current Week in a query

    Posted 10-28-2022 11:05
    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'};
    GetRecords($THISWEEK),9)

    ------------------------------
    Hans Hamm
    ------------------------------



  • 6.  RE: Current Week in a query

    Posted 10-28-2022 11:07
    It would also help to see the error message but you are missing a closing quote

    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'}";

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Current Week in a query

    Posted 10-28-2022 11:15
    Mark that fixed that issue, but now when I try to sum the values. It tells me that it is expecting a number. Field 9 is numeric and the field type I am using here is numeric.

    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'}";
    SumValues(GetRecords($THISWEEK),9)



    ------------------------------
    Hans Hamm
    ------------------------------



  • 8.  RE: Current Week in a query

    Posted 10-28-2022 11:21
    Hmm, I actually cannot spot he error.  Maybe someone else here can.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Current Week in a query

    Posted 11-01-2022 08:14
    Mark,
    Wanted to respond last week, way too busy.
    Anyway - I was trying to create this query in a Kanban numeric formula field. When I tried it elsewhere it worked. Do you know if there is a way to make this work in a Kanban? I really would prefer to show it this way. Thanks!

    ------------------------------
    Hans Hamm
    ------------------------------



  • 10.  RE: Current Week in a query

    Posted 11-01-2022 08:19
    I am not sure what you mean by a Kanban numeric formula field. Are you saying that the very same field works in a regular table report or a summary report but does not work when used on a Kanban  report?

    Are you saying you were trying to sort the tiles based on this formula query field?

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Current Week in a query

    Posted 11-01-2022 11:57
    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'}";
    SumValues(GetRecords($THISWEEK),9)

    Mark I put this in a table format and while it is providing me with my total hours (FID9) for the current week (FID 8)  and only for the regular hours (FID 7). It is creating a row for every individual in the company.
    I thought my query would summarize my total hours for team 6
    What I am looking to do is this:


    ------------------------------
    Hans Hamm
    ------------------------------



  • 12.  RE: Current Week in a query

    Posted 11-01-2022 12:19
    Edited by Mark Shnier (Your Quickbase Coach) 11-01-2022 12:55
    If you want to see just 1 number as a result then you would need to put the Formula Query on some kind of admin record which only has one record.

    More likely you want to have a similar formula query on a table of Teams so that you will see the hours for each team, so 5 Teams means 5 Team records  = 5 results for hours for each respective Team.

    The formula query would need to be changed so that instead of hard coded to Team 6 it would refer to the Team number on the Team Record.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Current Week in a query

    Posted 11-01-2022 12:39
    Per your normal advice - -  DEAD ON!
    Thank you for your assistance Mark, your the best!

    ------------------------------
    Hans Hamm
    ------------------------------



  • 14.  RE: Current Week in a query

    Posted 11-01-2022 14:57
    Mark from what I am seeing there is no distinct count ability in a query, only in a summary table. Is that correct?

    ------------------------------
    Hans Hamm
    ------------------------------



  • 15.  RE: Current Week in a query

    Posted 11-01-2022 15:06
    It's not impossible but you sort of would need to do some backflips with an additional Formula Query (FQ) to identify the "first duplicate" and then another FQ to just count the first ones.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 16.  RE: Current Week in a query

    Posted 11-01-2022 16:11

    Mark,
    So how might I do this?

    The "This Week's Hours" you helped on is working dead on. Now, in the same Team Table I want to create a "This Week's Projected Hours" based on how many associates currently have hours for the week then multiply that by 9.5 hours per day by how many days we are into the week.

    I know this is not correct, but is what I tried

    var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'"& [T-Team Number]&"'} AND {7.EX.'Regular Hours'}";

    Count(GetRecords($THISWEEK,"bsa4dgc7x"))

    I saw your post of how you worked in the Record ID, but not sure how to work that into this.

    My ultimate goal is to have this as a barchart of hours vs expected hours on the managers dashboard.



    ------------------------------
    Hans Hamm
    ------------------------------