I need a unique list from Available employees of numbers that occur more than SHIFT_LENGTH times No idea where to start yet. Should only have 1-4 entries in this table at a time that need to be calculated so don’t be afraid of offering extreme ideas. I don’t need to know the count just that the count is more than SHIFT_LENGTH
Take a look at this sample I made and see if it can help you.
The implementation is probably different from yours so far, but should at least point you in some sort of helpful direction.
I would go about this formulation in a different way than I think you’re going.
It seems to me, and correct me if I’m wrong, that the “Available Employees” column is the result of a select() that’s pulling in ALL of some record type - likely something from the “Shifts” table or something.
it looks like it’s just pulling in everything.
If my assumption is true, what I would do is create a virtual column on the employee table that actually holds a number - the count of how many shifts a person has (for whatever given time period).
Then, when you do you “Available Employees” select() formula, you can include that into your criteria.
After typing this out and re-reading some of your text - I’m probly way off the mark here, but I hope I’m pointing you in a different direction that might be easier.
@Bahbus that’s quite the hack you’ve come up with to count the items in a list. Very clever.
It was @GreenFlux’s idea. I just…did stuff…then turned it into a sample app and cleaned up as many edge cases as possible.
This question baffled me so that we changed from “List of available times” to “List of unavailable times” and added a mapping of employees to possible stores. So now were taking all possible workers minus if you are unavailable anywhere between start and end time.
So Available employees was pulling every record of an open hr to work at the store that was inbetween the start and end time of a shift callout per employee. So Employee 234 can work 3,4,5,6…,13th hr of the day but not the 14th hr so they could not be a possible candidate for this shift.
I’m confused. Did you make these changes after your initial post or describing the lead up to the initial post?
We changed after the post. After bringing the issue up to the other guys on our team we decided that the change of data and how we went about it is likely easier than any solution in appsheet.
Your solution would unfortunately not work in our case although I have bookmarked it for future use in another app of ours, since there can be multiple shifts at a time that the employee would need to be counted for. Maybe if I used a current shift to check or something I could implement your method.