Count the number of fields within a record?

[Melisa_Goh]

I have a staffing schedule that where each employee is a record, and each day is its own field.

Each day field marks whether employees were on shift, off shift, or on PTO that day.

How can I count up the number of days employees were on PTO?

Thanks for any suggestions!

[JonathanBowen]

Hi @Melisa_Goh - how about this? You have 7 fields (or maybe 5), one for each day of the week. Have another 7 (or 5) with this formula:

IF(Day1 = 'PTO', 1, 0)

(modify Day1 appropriately for each day of your week)

Then have another formula field that sums up the new formula fields you have created.

{Day 1 Formula} + {Day 2 Formula}

Here’s a 2 day example:

Then hide all of the Day 1 Formula fields to tidy up the view.

JB

[Melisa_Goh]

Really appreciated, @JonathanBowen , this makes sense! However, we’ve got a whole year’s worth of scheduling to add up; this method would require 365 (plus 1) extra fields, which seems prohibitive. Is there an easier way to accomplish this?

[JonathanBowen]

@Melisa_Goh, got you. You definitely don’t want another 366 fields!

Really my advice in this scenario would be to make days into rows (records), not fields/columns. So, something like this:

Two tables, one for people and one for the schedule (or a record of each day’s status for a person).

The Schedule table has a link to People ({Person} field) - you would probably need the date, the On Shift/PTO status could be a single select and add whatever other fields you need. Once in this format, you could use grouped view to report on total across the whole population, totals by person, totals by status and so on.

In the data model, there’s no difference between one day and any other day - they have the same attributes, just different values, so this would tend to point me to them being rows in a linked table rather than columns in the people table.

That said, I’m just talking in general terms here and this idea may not work with your specific scenario.

JB

[Melisa_Goh]

I see, Jonathan, interesting idea! I’ll play with it and see if that works, thanks! --Mel