Filtering Summary Field Through Multiple Cross-Apps Relationships

  • 39
  • 1
  • Question
  • Updated 6 months ago
  • In Progress
Hi, I'm looking for some help on the cleanest way to filter a Date Summary Field that gets carried to a record via two separate cross-app relationships. I've been messing around with it for a while and figure I'd see what the community thinks

This involves three applications:

1.      Job Sites application (master app of realm which connects to all other apps)

2.      Inspection application (various inspection forms)

3.      Scorecard application (aggregation application which connects to all other apps and pulls in key data for monthly/quarterly/annual reporting)

Job Sites have a one-to-many relationship with tables in Inspections. Job Sites have a one-to-many relationship with tables in the Scorecards.

I’m almost complete building a scorecard application that will pull data via summary fields from multiple applications. I’m trying to find a way to let the user set a period via two date fields in a Scorecard record which can then be used as filters in one of the aforementioned summary field.

There are 12 ‘metrics’ that will be reported on and all have values somewhere in a Quick Base application, and all will have a parent Job Site, as will each Scorecard record.

I attached an image that I created that shows the summary field and the date fields that I would like to use. THANKS!

Photo of Heather Bryant

Heather Bryant

  • 1,106 Points 1k badge 2x thumb

Posted 6 months ago

  • 39
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
You are on to a great concept of being able to apply a global filter to a whole set of reports or a whole set of reports on a single dashboard. I do this for my clients,

The low tech Solution is to make a single record in single table called global filters. It will be Record ID# 1. Link that that to any other child tables with a reference field which is a formula numeric with a formula of 1

Look up your filters to the details tables and use that in your summary fields as a filter.

When I implement this I do it fancier where I have a Table of users with key fiield of userid. And then I build the relationship based in a reference field of [Current User] with a formula of User()

Then the buttons to set the filters will either create or edit the User Record in that table. That allows simultaneous users not to get in each other's way. But in a low volume app for these reports wi5 path low risk of simulaneuos users setting the filter, you may be able to get away with the simple approach.

Contact me via my website if you want help implementing the fancier version.