Skip to main content
Question

Help me write a formula that is dynamic

  • Oct 29, 2025 8:15 PM UTC
  • 4 replies
  • 164 views

Forum|alt.badge.img+9

Me again.

This is the perfectionist in me. I'd love suggestions on if it's possible (and how) to write a formula that calculates a person's age based upon DOB field, converts it into a category ("0-12", "13-17", etc.), AND ALSO where that field can apply the reference date based upon a report filter.

So, if in the future I need to see a report and the age needs to convert to the date where it would be in the beginning of that date range (or even more ideally, if it can reference the first date of service within the filtered time range on a connected table and apply the rule as it would have been in that time frame), that way I can always see what we would have received as a report-out at that point in time if I needed to go back and spot-check something.

I know that sounds terribly convoluted. It's for grants reporting, and honestly these are some of the items that get at some of the most obnoxious calculations we're asked to report out on...... If I can't make it perfect, no big deal, but I thought I'd see what you all can conjure up.

Example: Person is 17 at the time they come to receive services for the first time. The dynamic field should be able to reference my "Services" table, see when the first Service record within the filtered quarter is, and apply the age range. The following quarter, we'd actually report them out again, but applied to the new quarter -- and let's say they turned 18, so for THAT filter, I want it to now produce the updated age group.

Ultimately, accuracy within my present reporting period is of chief importance, but ideally, the ability to pull up historic records and check would really be fantastic.

4 replies

Forum|alt.badge.img+9
  • Author
  • Qrew Commander
  • Oct 29, 2025 9:10 PM UTC

Side note: Qrew forum request -- the ability to edit posts haha. I'm going to add on some additional things and make this already-dense request a little more so! :-D 

Here is one other related thing I'm trying to do -- which is not directly related to the above, but does affect it, so it's worth mentioning.

For grant reporting, I also need to be able to capture "New This Year", and "New This Quarter". Just to make things EXTRA fun, everyone is considered "New" at the beginning of the FY, even when they're legacy clients......

I see in a competitor database that was developed specifically for these kinds of grants, there are tick boxes for "New this quarter" and "New this year" right in a master Client table -- but without being able to see how those are behaving, I'm curious how I might build something like that within my QB app. Maybe, it's a report on a dashboard that somehow counts "Clients with any Service records this quarter, where the earliest service record since the last fiscal year falls within this quarter"?  These are the reporting requirements that turn my brain inside out. 🙃🤯


MarkShnierYou
Forum|alt.badge.img+24

Once that tests OK then make a new field for [Age Category]

IF(

IsNull([My Date of Birth field]), "",

[Age] <=12, "0-12",

 [Age] <=17, "13-17",

[Age] <=25, "18-25",

etc

[Age] <= 65, "50-65",

, "66+")

 

 


MarkShnierYou
Forum|alt.badge.img+24

 It sounds like you have a relationship where one person has many "Service Provided records". 

You can make a new field there for Age at Service Provided date.

var date DOB = [My Date of Service Provided field];  // update with your own field here

Year(Today())-Year($DOB)
-
If(
Month(Today())<Month($DOB)
or
(Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0)

 Then you can make a [Age Category at Date of Service] field there too. 


Top Step
Forum|alt.badge.img+12
  • Observing
  • Oct 23, 2026 1:19 AM UTC

Building on Mark's idea, calculate the age on the Services table. A formula can't read the dates in a report filter, but it can use the date of each service.

One tweak so it works off the service date instead of today. With the client's DOB looked up onto Services:

var date DOB = [Client DOB];
var date D = [Service Date];
var number Age = Year($D) - Year($DOB) - If(Month($D) < Month($DOB) or (Month($D) = Month($DOB) and Day($D) < Day($DOB)), 1, 0);
If($Age <= 12, "0-12", $Age <= 17, "13-17", "18+")

Each service then carries the age group as of that day, and it never changes later, so old quarters still show what you would have reported at the time.

For the first service in a quarter, add a text [Fiscal Quarter] field on Services and a formula checkbox that's only true on the earliest one:

var text Q = "{12.EX." & [Related Client] & "}AND{15.EX.'" & [Fiscal Quarter] & "'}AND{7.BF.'" & ToText([Service Date]) & "'}";
Size(GetRecords($Q)) = 0

Here 12 is Related Client, 15 is Fiscal Quarter and 7 is Service Date. Filter the report to that checkbox and the quarter you want, then group by the age category. Your 17-turning-18 client lands in the right group each quarter. Two services on the same first day would both count, so watch for that.