Is it possible to create a ROLLUP field that SUMs "Field 1" of the UNIQUE values in "Field 2"?
Topic Labels:
Formulas
0
446
2
Hello everyone,
I've been racking my brain on this question for over a week and have tried countless attempts to solve this, but am still spinning my wheels. Basically I'm aggregating total work history of all our staff across all our events this year, so I need to calculate how many shifts someone has worked.
The issue is that it's possible to have multiple records of the same worker per event because different supervisors may submit the same record with different ratings.
In my example, here are all the reports (records) that we received on 1 worker for the year (Table 1):
Here is the summary record (Table 2):
In the summary record (Table 2), Total shifts should be "9" and successful shifts should be "6". The current formula in the ROLLUP SUM(ARRAYUNIQUE(values)) doesn't work because if any event has the same number of shifts as another, it'll get skipped. Because there's 2 records of DF23, I can't use SUM(values) on the "# SHIFTS" field either.
I've been able to identify records that are duplicates in Table 1 using @W_Vann_Hall's base from this post, but doing this doesn't let me include the unique shift count in the total count. Does anyone have any ideas? Thank you in advance!
Use a different field in your repots table that indicates if a record is the "first/main" record for a particular shift+worker combo. Then in your rollups, set the condition to only include results for main records.
While it is possible to use rollups to identify the first shift-worker combo, it would involve back and forth rollups.
Another option would be to have a human select a checkbox for the "main" record. Yet another option is to have an automation that runs when each record is entered to check if it is the first/main record or not.
