How to list all values that repeat twice in a column?

[Henrique_Vieir2]

I have a list of possible booking times. These times can be booked twice only. I wanted to generate a list of all times that are booked twice in a date. With the list, I can subtract these times from the list of possible times. I am not able to formulate an expression that does this. Would anyone know how to build this expression? Any tips?

[Rafael_ANEIC-PY]

Hello @Henrique_Vieira, i suggest you add an extra hidden column for your “booked times” table, so that it counts how many times each “time” has been booked the moment it is saved, so then you can use this expression in your valid_if() in your booking form on the [Times] column:

bookingList[Times]-SELECT(bookings[Times], [bookingCount]=2)

I suppose there might be some date conditions for booking times, those conditions should be added to the SELECT() as well.

[Henrique_Vieir2]

Hi, Rafael! Thanks for you answer. Could you elaborate more on how to make this [bookingCount] column? Is it just a COUNT of the column [time]? And would SELECT filter out all the values that the count is equal to 2, thus generating the list? I will test it today.

[Rafael_ANEIC-PY]

Henrique_Vieira:

Could you elaborate more on how to make this [bookingCount] column?

Of course, if there are no other conditions, it would be just this expression as an app formula:

COUNT(SELECT(bookings[Times],[Times]=[_THISROW].[Times]))

Henrique_Vieira:

And would SELECT filter out all the values that the count is equal to 2, thus generating the list?

Exactly, let me know if it works for you.

[Henrique_Vieir2]

It worked! Thanks. There is only one detail: the user needs to select a time, as soon as he selects the list it is filtered, if he selects the unavailable time, the selection is empty to avoid the error. I think it helps a lot, but I wondered if there wasn’t a way for the list to always be filtered. I forgot to mention that there is another condition: the user selects the date too, and the times are referring to that date, I added the date condition to the count as well.

[Rafael_ANEIC-PY]

Henrique_Vieira:

It worked! Thanks. There is only one detail: the user needs to select a time, as soon as he selects the list it is filtered, if he selects the unavailable time, the selection is empty to avoid the error.

uh, that’s weird, if the expression is loaded in the column’s valid_if() it should only display avaidable times to pick from, with nothing in suggested values or anything else, just the valid_if().

Henrique_Vieira:

I forgot to mention that there is another condition: the user selects the date too, and the times are referring to that date, I added the date condition to the count as well.

Yes, i was suspecting there was a date condition, then you only have to add it to the count expression (which you already did) AND the expression for the bookings form to be placed in the [Times] valid_if() field, so it should be:

bookingList[Times]-SELECT(bookings[Times],AND([bookingCount]=2,[date]=[_THISROW].[date]))

[Henrique_Vieir2]

It’s exactly how I’m doing it.
But for some reason, when the user selects the date, it doesn’t update the valid_if list. Probably updates, but not visually. When you click, there are still times that are theoretically unavailable, when I select one of them, it simply remains empty and disappears from the list. As if it visually updates only when someone interacts with the list.

[Rafael_ANEIC-PY]

Could your share your table structure and column configuration please? I’m going to need to take a look hehe, an example of that weird behavior would help as well.

[Henrique_Vieir2]

Sure! And thanks for the help so far. My table has the following columns:
id (just the UNIQUEID() function)
email (user email)
possible_booking_times (a list with all the possible times)
time_count (virtual column)
unavailable_times

The time_count has the following expression:
COUNT(SELECT(bookings[time], AND([time]=[_THISROW].[time], [date] = [_THISROW].[date])))

The unavailable_times has the following expression:
SELECT(bookings[time], AND([time_count ]>1,[date]=[_THISROW].[date]))

The time column is just [possible_booking_times]-[unavailable_times].

The behavior is: if the 08:10 time is unavailable, the list will show 08:10, but when I select this time, the field become empty (I see the drop-down list with no selected value). When clicking on the list again to select the time, 8:10 will disappear from the list.

If I select any time that is available, the list is not filtered removing the unavailable values. So, it’s not about updating after selecting the value.