Distinct Dates
I'm having trouble trying to build a report that only shows distinct dates for a given variable. Scenario: I have multiple entries with activity date ranges. If those multiple entries contain the same date(s), I only want to count each calendar date once, no matter how many entries there are that contain that date. Does anyone have advice on how to achieve this?
The syntax for these formula queries is tricky. There are a bunch of resources I can refer you to for how to learn the syntax.
The Quickbase Junkie (Sharon Faust) https://www.quickbasejunkie.com/ has a lot of excellent free and paid documents and videos.
Query Language Syntax https://help.quickbase.com/api-guide/do_query.html
Formula Query Main Help Article
https://help.quickbase.com/user-assistance/formula_queries.html
Query Syntax https://help.quickbase.com/api-guide/componentsquery.html
https://community.quickbase.com/blogs/brian-cafferelli1/2021/10/14/formula-queries-are-now-generally-available (see list of links at the end)
Quickbase junkie help article https://www.quickbasejunkie.com/blog/formula-query-functions?utm_source=qb&utm_medium=r&utm_id=0But here is the formula for a formula checkbox field to flag the "1st" record for overlapping ranges., So you should be able to filter your report to just get one hit per Activity Type per overlapping date range.
var text QUERY =
"{7.OAF.'" & [Approximate Entry Date] & "'}" // replace 7 with the field ID of [Approximate Exit Date]
& " AND "
& "{6.OBF.'" & [Approximate Exit Date] & "'}" // replace 6 with the field ID of [Approximate Entry Date]
& " AND "
& "{8.EX.'" & [A. Activity Type] & "'}"; // replace 8 with the field ID for the [A. Activity Type][Record ID#]=
ToNumber(
Left(
ToText(
GetFieldValues(
GetRecords($QUERY),3)),";"))
