Skip to main content

Hello all,

so I have a table of Cars Status, which show the status of each car for each each day - Up, Down for engine prob, down for brake prob, down for tire/wheel prob etc. (My fleet is much larger than in this example, but let’s just consider a fleet of three cars.) So each car has a record in the Car Status table for each day.


In my Daily Reports table, there is one record for each day, and (among many other things) I would like to have a field that counts how many cars are “Up” on each day, as recorded in the Car Status table, and another field showing how many cars are down for “Engine” and so on.


So for each record in the Daily Reports table I need to have a formula (?) field, that looks at the Date field for that record, and then counts how many cars there are in the Car Status table which are “Up” on that same date.


Here is a read only link to a base I set up which has these tables set up as I describe, for clarification.



Thanks in advance for any help


Jonathan

Welcome to the community, @Jonathan_Coles! :grinning_face_with_big_eyes: You can get what you want with a slight change to your base design. On the [Cars Status] table, instead of entering the date into the primary field, link to the appropriate date record from the [Daily Reports] table. Turn the primary field into a formula that includes the date plus the vehicle number/ID, so that each record’s primary field is unique. Better still, add a eVehicles] table and link to each vehicle instead of entering the ID manually, which would turn this table into a three-way junction table, serving as the junction for vehicles, problems, and daily reports.


Then on eDaily Reports], your status fields can be rollup fields based on the links from eCars Status]. For “Total Number of Cars Up Today”, add a condition to only roll up records where the {Status} field contains “No Problems”. On the other, the condition would be the inverse ({Status} does not contain “No Problems”). For the aggregation formula in both rollups, use COUNTA(values), which will give you a count of the linked records instead of their contents.

Welcome to the community, @Jonathan_Coles! :grinning_face_with_big_eyes: You can get what you want with a slight change to your base design. On the [Cars Status] table, instead of entering the date into the primary field, link to the appropriate date record from the [Daily Reports] table. Turn the primary field into a formula that includes the date plus the vehicle number/ID, so that each record’s primary field is unique. Better still, add a eVehicles] table and link to each vehicle instead of entering the ID manually, which would turn this table into a three-way junction table, serving as the junction for vehicles, problems, and daily reports.


Then on eDaily Reports], your status fields can be rollup fields based on the links from eCars Status]. For “Total Number of Cars Up Today”, add a condition to only roll up records where the {Status} field contains “No Problems”. On the other, the condition would be the inverse ({Status} does not contain “No Problems”). For the aggregation formula in both rollups, use COUNTA(values), which will give you a count of the linked records instead of their contents.


Thank you so much for this Justin. I think I understand exactly what you are suggesting - and it is very smart! Unfortunately one un-changeable part of our process is that the Daily Report has to be done (for external reasons) AFTER the car status table is populated. So the record in the Daily Report table to which we would link each Car Status record doesn’t exist when the Car Status record is created…I will think about a way to change that process, I guess. Or perhaps we could pre-create Daily Report records with only the date field populated initially, so that the linking can be done from the car status table.

Thanks again.

J

Thank you so much for this Justin. I think I understand exactly what you are suggesting - and it is very smart! Unfortunately one un-changeable part of our process is that the Daily Report has to be done (for external reasons) AFTER the car status table is populated. So the record in the Daily Report table to which we would link each Car Status record doesn’t exist when the Car Status record is created…I will think about a way to change that process, I guess. Or perhaps we could pre-create Daily Report records with only the date field populated initially, so that the linking can be done from the car status table.

Thanks again.

J



I suggest seriously considering that option. While I understand that the full report might not be able to be created until a certain point in your process, those report records could still be made far in advance, which would greatly simplify the rest of the process.

Reply


Terms & Conditions