Adding multiple entries in a cell and effectively count

[Dean_Toland]

I’m hoping someone out there can see an easy solution for this problem that I can’t seem to wrap my head around. :slightly_smiling_face: I’ve racked my brain and just am not coming up with a workable solution. Below is a screenshot of a view in my base where I track home inspections for construction purposes, broken down with fields for each Friday in a month to cover that week period. When an inspector goes out, I update the address record (obfuscated on the left) to include the inspector as a collaborator in the week ending date field. This part works great because as the screenshot also shows it keeps count of inspections broken down by address area (current grouping shown) and then tallies at the bottom each week.

My problem is that sometimes more than one inspection per week is needed and there doesn’t seem to be a way to effectively track that. The easiest solution that I’m sure everyone is thinking of is to not use a collaborator field, simply use a number field or even single-select with number options to indicate the number of inspections that week, which would be great except for one thing - we have to track who did the inspection. I tried using just numbers before and having a separate “inspector” field with a collaborator dropdown, but it wasn’t effective because (unlike in this particular screenshot) the inspector is not always the same on each property, sometimes the same location will have different inspectors going out.

So I’m stuck. I can track the inspector of each visit, or I can track multiple weekly visits, but I can’t seem to track both while still keeping a clean base and being able to count records effectively. Unless I’m missing something, which I truly hope I am. Can anyone think of a good way to track multiple entries per field that can be counted?

[W_Vann_Hall]

I’d say, “Reconfigure your date columns to handle multiple collaborators” – except Airtable’s current summary functionality is pretty weak, and there’s no easy way to tally the number of entries in a multiple-entry field. As you’ve found, you’re limited to calculating the number or percentage of empty versus filled fields, which does you no good. (The same limitations would apply if you converted to a multi-select of inspector names.)

I’d say, “Well, in that case create a second column per date, and use COUNTA() or COUNTALL() to calculate how many entries are in the inspector field” – except Airtable seemingly casts multiple-entry collaborator and multi-select fields to a comma-delimited string before executing COUNT[x]() routines, making them useless for this purpose.

So I’m left with saying, “You could use linked records, along with a second summary field per week.” Your new table would contain one record per Inspector. When you add a new week column, you’d define it as a Linked Records field and specify the Inspectors table as the target. You’d also define a second column to tally inspectors per address per week, either as a Roll-up (here COUNTA() does work) or Count field. Fortunately, adding an Inspector from a linked table takes the same 3 clicks as adding a collaborator.

It’s far from an idea solution, but it is workable. If you’re interested, I tossed together a quick demo here.

[HammerOz]

HI Dean

It is a bit hard to fully respond without seeing your full base structure, but one thing that stands out is your fields for each date. That is going to cause you problems.

If you were to continue with “Week” structure you would need a join table “Week inspection”. However, I would question data model (not that I see exactly what you have).

Instead consider an inspection table and a property / location table. The specific inspector, date, linked property and collaborator are entered in the inspection table. Create a calculated field that returns the week of the inspection date. You can group by thisThis way go are capturing the specific inspection and yet you can still group by

If you need to plan ahead and the inspection weeks are needed you will likely need the extra table “Inspection weeks” in addition to the actual inspection table. So you would create an inspection, enter the specific date and select the inspection week.

Just some ideas anyway!