Distinct Dates

Question

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?

MarkShnierYou · Accepted Answer

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=0

But 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)),";"))

MarkShnierYou · Answer

This can be done with Formula Queries as long as you don't have too much data in your table. Can you tell me how many records are in this table?

The issue is that when we use a Formula Query ends up having to compare every record to every other record in the table and the number of comparisons grows exponentially with the size of the table and at some point, Quickbase will simply say it won't let you build that formula because it would impact performance too much.

The basic technique is to locate the Record ID, which has the lowest record ID number for all of its brothers and sisters who share dates in the same range. Then you filter the report to just run a report for those records.

And you also tell me whether you are checking this across every single record in the table, or are you checking this for a children of a parent record, such as appointment dates for a patient or something like that so you were looking for not brothers and sisters among all records, but brothers and sisters among records, which have the same parent.

Sign up

Login to the community