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
------------------------------
Original Message:
Sent: 11-01-2022 15:06
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
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
Original Message:
Sent: 11-01-2022 14:57
From: Hans Hamm
Subject: Current Week in a query
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
Original Message:
Sent: 11-01-2022 12:39
From: Hans Hamm
Subject: Current Week in a query
Per your normal advice - - DEAD ON!
Thank you for your assistance Mark, your the best!
------------------------------
Hans Hamm
Original Message:
Sent: 11-01-2022 12:19
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
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 Teams = 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
Original Message:
Sent: 11-01-2022 11:56
From: Hans Hamm
Subject: Current Week in a query
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
Original Message:
Sent: 11-01-2022 08:19
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
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
Original Message:
Sent: 11-01-2022 08:13
From: Hans Hamm
Subject: Current Week in a query
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
Original Message:
Sent: 10-28-2022 11:21
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
Hmm, I actually cannot spot he error. Maybe someone else here can.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
Original Message:
Sent: 10-28-2022 11:15
From: Hans Hamm
Subject: Current Week in a query
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
Original Message:
Sent: 10-28-2022 11:06
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
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
Original Message:
Sent: 10-28-2022 11:04
From: Hans Hamm
Subject: Current Week in a query
var text THISWEEK ="{8.IR.'this wk'} AND {17.EX.'6'} AND {7.EX.'Regular Hours'};
GetRecords($THISWEEK),9)
------------------------------
Hans Hamm
Original Message:
Sent: 10-28-2022 10:50
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
Please post (copy paste) your formula that fails the formula editor.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
Original Message:
Sent: 10-28-2022 10:10
From: Hans Hamm
Subject: Current Week in a query
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
Original Message:
Sent: 10-28-2022 09:51
From: Mark Shnier (Your Quickbase Coach)
Subject: Current Week in a query
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
Original Message:
Sent: 10-28-2022 09:32
From: Hans Hamm
Subject: Current Week in a query
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
------------------------------