How to retrieve unique count of records in the summary report

  • 1
  • 1
  • Question
  • Updated 4 years ago
  • Answered

We have a requirement of creating a summary report based on a secondary data.

Details:

There are 3 tables:

1) Project

2) Location - Location name, Geography.,

3) Project Location which has many to one relationship with the above 2 tables

I just need to report number of projects corresponding to each geography.

When I create summary report to display count of Project Locations grouped by Geography, I'm not getting the proper count.

For example: There are 2 locations that belong to same geography assigned to a project, then the Geography should have count as '1' instead of '2'.

Photo of Dinesh

Dinesh

  • 0 Points

Posted 4 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,548 Points 50k badge 2x thumb
No problem.
Let just coax each Project Location to self identify if they were the lucky Project Locations which was 1st for that Project. Then we will count them up.

On the Relationship between Project and Projects Locations, make a Summary of the Minimum Record Id# of the Project Locations.  Then look that up do wn to the Project Locations.  Call it [Record ID# for the 1st Project Location for this Project].

Then on the other relationship between Project Locations and Geographies, make a Summey field to count the # of Project Locations subject to the filer where the [Record ID#] is equally to the value in the field [Record ID# for the 1st Project Location for this Project]
Photo of Dinesh

Dinesh

  • 0 Points
Thanks for trying to help me. Although this doesn't give the exact count of the projects for the Geography, this does not take the duplicates of the projects while counting. This is the only solution even I had in mind before posting the question. With this method, we are considering only the geography of the first location added to the project and ignoring the geography of the remaining locations added to the project. This should be a limitation in QuickBase to retrieve the exact count of projects for each geography considering all locations added to the project.