Forum Discussion

Qrew Member

4 months ago

Solved

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 sam...

reports

MarkShnier__You
4 months ago
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)),";"))

MarkShnier__You

Qrew Legend

4 months ago

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.

matakis

Qrew Member

4 months ago

Thank you so much, Mark. Right now there are less than 100 records; I don't anticipate it will exceed 150 records per year moving forward (probably less than that). Each record has around 40 unique fields, but I am only concerned with two of them (the start and end date for each record).

I should also note that around half of the fields are pulling in from a Dropbox pipeline (refreshing once per week, if that matters for performance purposes). There is a unique 5-digit record ID assigned to each record.

We want to validate the number of unique days across every single record. In effect, we are checking how many unique calendar days during which an activity occurred across all records. We want to determine the number of unique calendar days by the activity type for each record (separate field(s)), so we want to assess how many unique calendar days "Activity A" occurred and during how many days "Activity B" occurred across all records, with each calendar date being counted only once. I'm not sure which type of report would be ideal for this, perhaps a pie chart? Hopefully this addresses your last paragraph.

Could you share more about how to build the Formula Query, assuming it's still appropriate based on what we're trying to achieve? Thanks again.

MarkShnier__You
Qrew Legend
4 months ago
Can you tell me the names of your fields for the start and end dates and also the field for Activity Type.
- matakis
  Qrew Member
  4 months ago
  "Approximate Entry Date"
  "Approximate Exit Date"
  "A. Activity Type"
  - MarkShnier__You
    Qrew Legend
    4 months ago
    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)),";"))

Forum Discussion

Distinct Dates

Related Content

Historical Date Change Tracking

Formula Date Field

Date Field Formula

Calculating number of days between two date fields

Add 1 day to date formula