Scheduling App: Filter enumlist by staff available and times already booked

[greggillam]

I have a scheduling component to one of my apps and I need to give schedulers a list of options based on general availability (the start and end times submitted by users) and filtering out any events that fall within an already booked time slot.

Example: John submits his availability, indicating he can work from 6am to 8pm M-F this week. John is scheduled for an event that runs from 6-10am on Monday. A separate scheduler is now staffing a Monday event that runs from 7-11am and their list of available options needs to exclude John as he is already booked during that time. If the scheduler were to staff a event that runs from 10:30am - 2:30pm on Monday, however, we would want to show John as being available.

I have the valid if expression (below) setup correctly, up to the point of filtering out staff that are already booked during the time of the current event being staffed. The last/sixth variable in the and() sub-expression below is where I'm attempting to do so (red text). I am trying to generate an exclusion list of staff from events that are on the same date as the current row and that have a start or end time within the start to end time of the current row.  Any help would be most appreciated. Thank you.

select(Availability[Name Commute Notes],
and(
[Job Title]="Medical Screener",
[_thisrow].[week of]=[week of],
[_thisrow].[region]=[region],
[_thisrow].[Start Date/Time]>=index([start times],weekday([start date/time])-1),
[_thisrow].[End Date/Time]<=index([end times],weekday([end date/time])-1),
not(in([Name Commute Notes],
select(Data_Demand[screeners staffed],
AND(
([_thisrow].[Event Date]=[Event Date]),
IF(
([_THISROW].[Start Date/Time] <= [Start Date/Time]),
([_THISROW].[End Date/Time] > [Start Date/Time]),
([_THISROW].[Start Date/Time] < [End Date/Time]))
))))
))

[Marc_Dillon]

It could be an issue with ambiguity of the context referred to by [_thisrow]. Whenever I do a nested select like that, I always explicitly use the correct [_THISROW-N] instead of just [_THISROW]. Side-effect is that it makes it easier to read, or at least to me.

[greggillam]

Thanks Marc. I added "-1" to second select() [_thisrow] expressions and it didn't make a difference. I don't believe it would be relevant anyway because I'm referencing the first query in the beginning of the in() expression and trying to compare that to the existing table, which is the second select() expression. In other words, both select()'s are examining different datasets.

Two other things that I'm not sure are influencing this but I'd like to rule out:

1. I have the expression set for the suggested values and another expression in valid if that compares the number of staff assigned to the number requested - an equality expression comparing a number column to a count() of the enumlist column.
2. That majority of edits done to this table are done inline. I've tested this and the values do seem to filter out but only after manually syncing the app. I'm trying to have them filter out immediately during inline editing, which is what was occurring before I added the sixth condition to the and() expression (highlighted red in initial post).

Appreciate your help here.

[greggillam]

Could the issue have anything to do with the fact that the second select() is technically generating a list of lists? [screeners staffed] is an enumlist column, so I'm comparing a text value with [name commute notes] to a list of enumlist values with select() and [screeners staffed].

[Marc_Dillon]

I added "-1" to second select() [_thisrow] expressions and it didn't make a difference

---

I would have expected a '-2' in the second select. 

I don't believe it would be relevant anyway because I'm referencing the first query in the beginning of the in() expression and trying to compare that to the existing table, which is the second select() expression. In other words, both select()'s are examining different datasets.

---

Not sure how that would make it irrelevant. The ambiguity is which column are you referring to. And since they all seem to be named the same across the tables, that exasperates the ambiguity.

I have the expression set for the suggested values and another expression in valid if that compares the number of staff assigned to the number requested - an equality expression comparing a number column to a count() of the enumlist column.

---

Shouldn't be an issue.

That majority of edits done to this table are done inline. I've tested this and the values do seem to filter out but only after manually syncing the app. I'm trying to have them filter out immediately during inline editing, which is what was occurring before I added the sixth condition to the and() expression (highlighted red in initial post).

---

"inline" as in an inline action on a table view, or what? Shouldn't matter I don't think. And I'm certainly not surprised about what you've described in the second half, that sort of additional context nesting will affect the calculation timing in a few areas in Appsheet.

the second select() is technically generating a list of lists? [screeners staffed] is an enumlist column

---

Yes, definitely. "Flatten" the list of lists by wrapping the select in a SPLIT(TEXT(...) , " , " )

[greggillam]

Thanks Marc. I think it is mainly a sync issue. Are you able to suggest a way to better set up this expression? It syncs immediately without the second select(), so is there another way to exclude these values from the list (slice, virtual column that does the second select computation and then just filter against that in the above expression, etc.)?

By inline editing I'm referring to the quickedit feature available for tables. Inline wasn't the best way to describe this, sorry about that.

When you say the ambiguity is which column I'm referring to, what exactly do you mean? I'm still not certain how [_thisrow-#] would help me here, but that could just be because I'm not 100% familiar with the function. I honestly had never heard of it when you mentioned it and had to do some research to figure out the intent.

[Marc_Dillon]

Are you able to suggest a way to better set up this expression?

---

Not really. Schedule conflict validation will always be large complex expressions.

ambiguity

---

Because all of your tables use the same column name (not saying this is good or bad though), you could be accidentally referring to the same column in one of the other tables, simply by using the wrong thisrow-n.

Here is a great explanation image for thisrow-n.

[greggillam]

The two tables being referenced in this expression do share some common column names, but in the second select I am trying to compare the current row against data in the same table. As I mentioned the expression was working instantly before the second select() which would seem to indicate all was setup correctly in the expression structure.

I reviewed Steve’s post where you pulled the picture when I researched this previously and understand the concept, just not how it would apply to my particular expression. Nevertheless I appreciate you trying to help. I’ll keep at it and let you know when I figure it out. 

[Steve]

---

@greggillam wrote:

I have a scheduling component to one of my apps and I need to give schedulers a list of options based on general availability (the start and end times submitted by users) and filtering out any events that fall within an already booked time slot.

---

Rather than using a nested SELECT(), I'd use list subtraction: (all possible options) - (unavailable options)

[greggillam]

Thanks Steve. I saw this approach in one of your articles and though it does seem to improve efficiency somewhat, it still does not immediately remove the values already selected during a competing time slot that day. I'm thinking it's just a sync delay in appsheet. In the screenshots below, you can see how I'm selecting 4 staff for an event (screenshot 1) and then in the next event those same 4 are still listed as available options (screenshot 2); however, when I edit the next event row in any way (screenshot 3) it seems to sync and the already used options are no longer present (screenshot 4).

1.   2.

3.  (event location in second row edited)

4. 

Here is the latest expression, for reference:

sort(select(Availability[Name Commute Notes],
and(
[Job Title]="Medical Screener",
[_THISROW].[Week Of]=[Week Of],
[_THISROW].[Region]=[Region],
[_THISROW].[Start Date/Time]>=index([Start Times],weekday([Start Date/Time])-1),
[_THISROW].[End Date/Time]<=index([End Times],weekday([End Date/Time])-1)))
-
split(text(select(data_demand[Screeners Staffed],
      AND(
        (date([_THISROW].[Start Date/Time])=date([Start Date/Time])),
					IF(
          ([_THISROW].[Start Date/Time] <= [Start Date/Time]),
          ([_THISROW].[End Date/Time] >= [Start Date/Time]),
          ([_THISROW].[Start Date/Time] <= [End Date/Time])))))," , ")
          )