Filtering slots for scheduling process

[Ksenia1]

Hello again,

this is with reagards to my previous qestion.

I am working on an App to schedule slots with managers in certain locations. Many locations can have many managers who can offer many slots in each location.

Working with many-to-many relationships, I have a helping table (LocationManager) to combine managers with locations. And another one (Reservation) to combine the LocationManager helping table with the slots. Furthermore, the scheduled Reservation is stored in a Booking table with information on the user.

The procedure to schedule a slot is as follows: the user chooses a location -> in the location_detail view they choose the manager (through related LocationManager) -> and in the LocationManager_detail view they choose the slots through the displayed related Reservation table.

Now, I need to build in four conditions for the slots to show:

1. slots have to be more than 14 days in the future from today's point of view
2. same slot at a specific location with a specific manager can be booked not more than 4 times by users
3. if a slot has been booked in a location with a manager, this slot disappears from other locations for this manager, so the manager is only bookable at that one location for that specific slot
4. respectively, if the slot isn't booked yet with a manager, it shall be shown in every location

I have managed the first two conditions by building the following slice for table "Reservation" and refering to this slice in my LocationManager table:

AND(
[SlotID].[Slot]>TODAY()+14,
COUNT([_THISROW].[Related Bookings]) < 4
)

Now having to add the other two, I am wrapping my head around the right condition but cannot find the solution.

For #3 I guess the expression should look like this:

IN([ReservationID], Booking[ReservationID])

And for #4:

ISBLANK([_THISROW].[Related Bookings])

But how do I combine it together in the slice above?

Thank you in advance for your help.

[Ksenia1]

@Suvrutt_Gurjar any suggestions? 🙂

[Suvrutt_Gurjar]

Based on the understanding of the requirement of points 3 and 4 you have mentioned

---

@Ksenia1 wrote:

• if a slot has been booked in a location with a manager, this slot disappears from other locations for this manager, so the manager is only bookable at that one location for that specific slot
• respectively, if the slot isn't booked yet with a manager, it shall be shown in every location

---

1. Please add a column called [Manager] VC type with an expression something like

[StMaID].[ManagerID] in the "Reservations" Table

2. Please try the following expression in the Valid_if of the [SlotID] column of the "Reservations" table 

Slot[SlotID] - SELECT(14 DaysPlus Reservations[SlotID], [Manager]=[_THISROW].[Manager])

Where the first [SlotID] is the key of the Slot table, "14 DaysPlus Reservations" is the slice you mentioned in the points 1 and 2 in your post above. The second [SlotID] is the reference column in the "reservations" table.

The above Valid_if expression should remove the slots for a manager on a specific day for other locations. I believe the valid_if expression will address the requirement 4 as well.

Or alternative to step 2 above, please simply try the following as step 2 for the slice filter expression. Please do retain step 1 of creating [Manager] column though.

AND(
[SlotID].[Slot]>TODAY()+14,
COUNT([_THISROW].[Related Bookings]) < 4,
[Manager]<>[_THISROW].[Manager]
)

[Ksenia1]

Thank you @Suvrutt_Gurjar for getting back to me on that!

Step 1:

Step 2 didn't work unfortunately. Slot booked with a manager in one location still shows up in another location.

My valid if in column SlotID of Reservation table:

Alternative step 2 - unfortunately that breaks the flow and the related resevations disappear from StMa_Detail page:

My Row filter for the slice: 

AND(
[SlotID].[Slot]>TODAY()+14,
COUNT([_THISROW].[Related Bookings]) < 4,
[Manager]<>[_THISROW].[Manager],
NOT(
IN(
USEREMAIL(),
SELECT(Booking[UserEmail], [ReservationID] = [_THISROW].[ReservationID])
)
)
)

Last expression added so that the user can book same slot only once.

[Suvrutt_Gurjar]

Could you try in the valid_if the following revised expression

Filtered Slot[SlotID] - SELECT(Filtered Slot[SlotID], [Manager]=[_THISROW].[Manager])

[Ksenia1]

@Suvrutt_Gurjar unfortunately still not working.

[Suvrutt_Gurjar]

Could you updated if the slots are pre allocated in the Reservations table or the user selects them from the drop down? Is the slots column containing valid_if in the column ? Is so what is that expression?

[Ksenia1]

I hope I got you right.

The slots are pre allocated in the reservations table which looks as follows:

In the LocationManager_Detail view the slots are shown in the related Reservations sections. Here the User selects the slots/ reservation

and in the following view adds a record to the related booking:

Other than trying your provided valid_if expression, no valid_if formula is being used.

[Suvrutt_Gurjar]

Due to the operation sequence of slot booking, I believe it is a bit extensive implementation.

As per my more testing, it shows that the filter expression mentioned below that I suggested earlier and you tested with some addition should work. It filters out the date once booked for a manager for one location , then it does not show in other locations for that manager.

AND(
[SlotID].[Slot]>TODAY()+14,
COUNT([_THISROW].[Related Bookings]) < 4,
[Manager]<>[_THISROW].[Manager],
NOT(
IN(
USEREMAIL(),
SELECT(Booking[UserEmail], [ReservationID] = [_THISROW].[ReservationID])
)
)
)

1. Please use the slice "Filtered Slot" with the above expression in a new reverse reference virtual column called say "Add A New Reservations"  in the LocationManager table to add new reservations with an expression something like 

REF_ROWS("Filtered Slot", "StMaID") and that column should have referenced table as the slice "Filtered Slot"

Please use this reverse reference column on the slice "Filtered Slot" to add new reservations from the LocationManager table.

2) To see the existing reservations for the manager and location in the LocationManager table, please create a read only slice called say "All Reservations" on the table Reservations without any filter expression.

Please create a reverse reference VC on this read only slice with an expression something like 

REF_ROWS("All Reservatiobs", "StMaID") in the LocationManager table and that column should have referenced table as the slice "All Reservations"

Now in the LocationManager detail view, the user will see/ use two inline view, "All Reservations"  to see the existing reservations and the view "Add A New Reservation"  to add a new reservation.

3) Please hide the system created reverse reference column 'Related Reservations" in the LocationMAnager table.

[Ksenia1]

Thank you @Suvrutt_Gurjar 

My VC Related Reservations in 1. already existed with the reference to the slice:

Once I add the expression 

[Manager]<>[_THISROW].[Manager],

to my Slice row filter condition, the related Slice is no longer being shown in my LocationManager_detail view:

 

[Suvrutt_Gurjar]

Thank you for the details. Could you update what is the inline view type ( meaning is its table, card , gallery or deck view?) of "Related Reservations" . The view seems to show only "View" option and no "Add" option, even though the slice "Filtered Slot" seems to have  "Add, Update, Delete" option.

[Ksenia1]

The shown Filtered Slot view is of type card view. (Reservation_Inline View is of type table).

The user shall not add a reservation to LocationManager but he shall select the correctly filtered reservation and add a related booking to the reservation.

@Suvrutt_Gurjar will it help when I share the App with you?

 

[Suvrutt_Gurjar]

A query here please. You mentioned the reservations are pre allocated. In that case how the locationmanager reference gets added to those pre allocated reservations because the following procedure does not described how the locationmanagers reference is pre allocated in the reservations that show up as related reservations in the location manager detail view.

---

@Ksenia1 wrote:

The procedure to schedule a slot is as follows: the user chooses a location -> in the location_detail view they choose the manager (through related LocationManager) -> and in the LocationManager_detail view they choose the slots through the displayed related Reservation table.

---

---

---

[Ksenia1]

@Suvrutt_Gurjar could I share the App with you?

I hope I understood you correctly. In my Reservation table I allocate LocationManagerID to the SlotID

[Suvrutt_Gurjar]

Thank you.Sorry, my question is that the process of app is - select location, select manager and select slots from reservations. But in Reservations the location and manager are already selected as you allocate locationmanager id there. So while allocating locationmanager Id to Reservations table  what prevents duplicate locationmanager Id to be allocated to the same slot? At this point , it sounds like a circular reference issue due to sequence of operations. 

Hope I was able to make my question clear.

[Ksenia1 · Accepted solution]

Thank you @Suvrutt_Gurjar again for sticking with me.

The appropriate expression has been found and extended with:

COUNT(
SELECT(Booking[ReservationID],
AND(
[_THISROW].[SlotID] = [SlotID],
[_THISROW].[StMaID].[StandortID] <> [StandortID],
[_THISROW].[Manager] = [ManagerID]
)
)
) = 0

So the whole Row filter condition looks as follows:

AND(

[SlotID].[Slot]>TODAY()+14,

COUNT([_THISROW].[Related Bookings]) < 4,

NOT(

IN(

USEREMAIL(), 

SELECT(Booking[UserEmail], [ReservationID] = [_THISROW].[ReservationID])

)

),

  COUNT(

    SELECT(Booking[ReservationID], 

    AND(

      [_THISROW].[SlotID] = [SlotID], 

        [_THISROW].[StMaID].[StandortID] <> [StandortID],

        [_THISROW].[StMaID].[ManagerID] = [ManagerID]

      )

    )

    ) = 0

)

[Suvrutt_Gurjar]

Nice to know that you arrived at a working solution.

Thank you for the update and posting the solution. This will help anyone reading through the post thread in future.