How do you link 2 tables attempting to count the number of Open and Closed Dates (Table 1) within a Period (Star and end dates) detailed in another table (Table 2)?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

The crux of the problem is how to link the 2 tables (and whether you need joining tables in between). There are many Records with Start Date (and Closed dates) within the periods, so it seems to be a 1 to many from the {main table Table 1) point of view to the Many (Table 2).

The intent is to have fields in a table where the counts are recorded for each week period (Open and Closed to record how things are progressing) and create a Report & Chart showing the progress over time.

Help would be appreciated.

Photo of Mel42

Mel42

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
I think we need a better idea of what these two tales are and how they are related.   Can you give an example of real data and what these records represent?  Is this table 2 a single record table where you are setting a date range?
Photo of Mel42

Mel42

  • 0 Points
The first table holds all the details about the records. The second table was created as a way to hold Metrics data and create a report using that data. The period is defined in Table 2 with a Start Date and an End Date, within which I'm trying to count the number of Records opened during that time (and also closed as well).

And the intention is to show how the backlog of the Records is being reduced over time (in comparison to the targets of overall numbers worked out for each week this year). Anything else you need to know, just ask.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
I suggest that you load up the dates table be say every Sunday for the next 5 years loaded up by excel.  Or if you prefer, the first of every month for the next 5 years.

Then on details table create a field with by formula is like

FIrstDayOfWeek([Open Date])

Then create a relationship back to the Sundays Table with that formula field as the reference field on the right side of the relationship.

Then summarize then # of records and you will have a count of the number opened that Sunday week.

Then make a new field

FIrstDayOfWeek([Closed Date])

And then a new relationship based on that field and then you can make a summary count field of the number closed that week.

Then you have two clean fields on your Sunday's table and can subtract them to see if you are making headway or getting washed further behind each week.

Once you get thatvworkin, there is also a way to "daisy chain" the Sunday's together Ina relationship to carry forward a running total of the lifetime to date progress.  But get the first totals working and then we can come back to that.
Photo of Mel42

Mel42

  • 0 Points
Slight problem. It is only showing number fields in the refence field on the right side of the relationship when I am looking to create the relationships between the details table and the Metrics Dates table. Also, how can you have 2 different relationships between a pair of tables unless you split the Close and Open start dates into 2 seperate tables?