Solved: Enum Events List

YanChang · 09-29-2020 06:02 AM

How can I write a rule for “Time” so that the event is not scheduled simultaneously and on the same day if I add a new event?

Steve

You’ll need to use a Valid If expression for the Time column that constructs the list of available time slots. To do so, start with the list of all time slots, then remove the list of unavailable slots, leaving only the available slots:

(
  LIST(
    "07:00:00",
    "08:00:00",
    "09:00:00",
    "10:00:00",
    "11:00:00",
    "12:00:00"
  )
  - SELECT(
    table[Time],
    ([Date] = [_THISROW].[Date])
  )
)

Unfortunately, there’s no way for the expression to know what Enum values are defined, so you’ll have to reproduce that list of values in the expression itself as shown above. Strictly speaking, you probably then don’t even need to define the Enum values, or even use the Enum column type at all–you could just go with a Time column type.

The list of unavailable time slots is gathered using a SELECT() expression to query the table. Note that in the expression above, you need to replace table with the name of the table.

List subtraction is used to remove the unavailable slots from the list of all slots.

See also:

YanChang

Great it works, small correction
workouts last 1 hour, if workout is at 7:00 then 7:30 should also not be in the choice
how to do it?

(
LIST(
“07:00:00”,
“07:30:00”,
“08:00:00”,
“08:30:00”,
“09:00:00”,
“09:30:00”,

)

SELECT(
table[Time],
([Date] = [_THISROW].[Date])
)
)

JuneCorpuz

I’m not quite sure how others will do it, but as a workaround, I will do something like this

Create a virtual column to calculate the time with this formula.

([time] + “00:30:00”)

Apply this formula

(
LIST(
“07:00:00”,
“08:00:00”,
“09:00:00”,
“10:00:00”,
“11:00:00”,
“12:00:00”
)

SELECT(
table[Time],
([Date] = [_THISROW].[Date])
)
SELECT(
table[Time_virtual],
([Date] = [_THISROW].[Date])
)
)

YanChang

virtual column to calculate the time with this formula ([time] + “00:30:00”)
Error - Arithmetic expression ‘([Day1StartTime]+“12/30/1899 12:30:00 AM”)’ has inputs of an invalid type ‘Unknown’

JuneCorpuz

What is the base type format of your enum? Is it text or time?

JuneCorpuz

Make it “000:30:00”

YanChang

Column Name ‘VirtualTime1’ in Schema ‘Calendar_Schema’ of Column Type ‘Time’ has an invalid app formula ‘=Payment[Day1StartTime] + “000:30:00”’. Arithmetic expression ‘(Payment[Day1StartTime]+“00:30:00”)’ has inputs of an invalid type ‘Unknown’

Steve

Try:

[Day1StartTime] + “000:30:00”

YanChang

Worked!) but the problem remained, I have it busy from 7:00 to 8:00 and still have 7:30 in my choice

JuneCorpuz

Can you please share your updated formula for your enum?

YanChang

(
LIST(
“07:00:00”,
“07:30:00”,
“08:00:00”,
“08:30:00”,
“09:00:00”,
“09:30:00”,
“10:00:00”,
“10:30:00”,
“11:00:00”,
“11:30:00”,
“12:00:00”,
“12:30:00”,
“13:00:00”,
“13:30:00”,
“14:00:00”,
“14:30:00”,
“15:00:00”,
“15:30:00”,
“16:00:00”,
“16:30:00”,
“17:00:00”,
“17:30:00”,
“18:00:00”,
“18:30:00”,
“19:00:00”,
“19:30:00”,
“20:00:00”,
“20:30:00”,
“21:00:00”
)

SELECT(
Calendar[DayStartTime] ,
( [DayStartDate] = [_THISROW].[Day1StartDate] )
)
SELECT(
Calendar[VirtualColumn1] ,
( [DayStartDate] = [_THISROW].[Day1StartDate] )
)
)

JuneCorpuz

This works on my end. Are you applying this formula to Suggested Values? Please check the value of your virtual column in the Detail View, VirtualColumn1 must show you the value of 07:30:00 AM

YanChang

I need to hide the value 7:30 if it is busy at 7:00 because the event lasts 1 hour

YanChang

Are there any other options?

Steve

Since your schedule allows events every 30 minutes, but a given event may span multiple time slots, you’ll need to indicate explicitly which time slots the event covers. I can imagine two ways to approach this:

The hard way: add one event for each time slot used by a particular event. For instance, an event that lasts an hour would need two rows. If the event is cancelled, you’d have to remove all associated rows.
The easy way: add another column to the table of type EnumList of base type Time to contain the time of every time slot the event covers.

YanChang

Duration of events 1 hour
If the start of the event is busy at 7:00
EnumList selection should not have 7:00 and 7:30
If the start of the event is busy at 7:30
EnumList selection should not have 7:30 and 8:00

How can I hide this value? Maybe you can add the value + “00:30:00” to the formula?

YanChang

time

JuneCorpuz

LIST(
	“07:00:00”,
	“07:30:00”,
	“08:00:00”,
	“08:30:00”,
	“09:00:00”,
	“09:30:00”,
	“10:00:00”,
	“10:30:00”,
	“11:00:00”,
	“11:30:00”,
	“12:00:00”,
	“12:30:00”,
	“13:00:00”,
	“13:30:00”,
	“14:00:00”,
	“14:30:00”,
	“15:00:00”,
	“15:30:00”,
	“16:00:00”,
	“16:30:00”,
	“17:00:00”,
	“17:30:00”,
	“18:00:00”,
	“18:30:00”,
	“19:00:00”,
	“19:30:00”,
	“20:00:00”,
	“20:30:00”,
	“21:00:00”
)

The formula above shows all of your time slot for your events.

SELECT(
	Calendar[DayStartTime],
	[DayStartDate] = [_THISROW].[Day1StartDate]
)

The second formula shows all of the time slot for that specific date.

Ex: 07:00:00 AM

SELECT(
	Calendar[VirtualColumn1] ,
	[DayStartDate] = [_THISROW].[Day1StartDate]
)

The third formula shows all of the [DayStartTime] + “000:30:00”

Ex: 07:30:00 AM

If you subtract those three list, the result would be

 LIST(
      	“08:00:00”,
    	“08:30:00”,
    	“09:00:00”,
    	“09:30:00”,
    	“10:00:00”,
    	“10:30:00”,
    	“11:00:00”,
    	“11:30:00”,
    	“12:00:00”,
    	“12:30:00”,
    	“13:00:00”,
    	“13:30:00”,
    	“14:00:00”,
    	“14:30:00”,
    	“15:00:00”,
    	“15:30:00”,
    	“16:00:00”,
    	“16:30:00”,
    	“17:00:00”,
    	“17:30:00”,
    	“18:00:00”,
    	“18:30:00”,
    	“19:00:00”,
    	“19:30:00”,
    	“20:00:00”,
    	“20:30:00”,
    	“21:00:00”
    )

Are you putting that formula in your Suggested Values?

YanChang

Thanks for helping!
I did everything as you said, but it doesn’t work like that)
I figured it out what was wrong

(
  LIST(
    "07:00:00",
    "07:30:00",
    "08:00:00",
    "08:30:00",
    "09:00:00",
    "09:30:00",
    "10:00:00",
    "10:30:00",
    "11:00:00",
    "11:30:00",
    "12:00:00",
    "12:30:00",
    "13:00:00",
    "13:30:00",
    "14:00:00",
    "14:30:00",
    "15:00:00",
    "15:30:00",
    "16:00:00",
    "16:30:00",
    "17:00:00",
    "17:30:00",
    "18:00:00",
    "18:30:00",
    "19:00:00",
    "19:30:00",
    "20:00:00",
    "20:30:00",
    "21:00:00"
  )
  - SELECT(
    Calendar[CalendarVirtualTime1] ,
    ( [DayStartDate] = [_THISROW].[Day1StartDate] )
  )
)

This is it works!

(
  LIST(
    "07:00:00",
    "07:30:00",
    "08:00:00",
    "08:30:00",
    "09:00:00",
    "09:30:00",
    "10:00:00",
    "10:30:00",
    "11:00:00",
    "11:30:00",
    "12:00:00",
    "12:30:00",
    "13:00:00",
    "13:30:00",
    "14:00:00",
    "14:30:00",
    "15:00:00",
    "15:30:00",
    "16:00:00",
    "16:30:00",
    "17:00:00",
    "17:30:00",
    "18:00:00",
    "18:30:00",
    "19:00:00",
    "19:30:00",
    "20:00:00",
    "20:30:00",
    "21:00:00"
  )
  - SELECT(
    Calendar[DayStartTime] ,
    ( [DayStartDate] = [_THISROW].[Day1StartDate] )
  )
)

And this is it works!

(
  LIST(
    "07:00:00",
    "07:30:00",
    "08:00:00",
    "08:30:00",
    "09:00:00",
    "09:30:00",
    "10:00:00",
    "10:30:00",
    "11:00:00",
    "11:30:00",
    "12:00:00",
    "12:30:00",
    "13:00:00",
    "13:30:00",
    "14:00:00",
    "14:30:00",
    "15:00:00",
    "15:30:00",
    "16:00:00",
    "16:30:00",
    "17:00:00",
    "17:30:00",
    "18:00:00",
    "18:30:00",
    "19:00:00",
    "19:30:00",
    "20:00:00",
    "20:30:00",
    "21:00:00"
  )
  - SELECT(
    Calendar[DayStartTime] ,
    ( [DayStartDate] = [_THISROW].[Day1StartDate] )
  )
  - SELECT(
    Calendar[CalendarVirtualTime1] ,
    ( [DayStartDate] = [_THISROW].[Day1StartDate] )
  )
)

But not together(

JuneCorpuz

I just noticed. What is the difference of [DayStartDate] from [Day1StartDate]?

I’ve created a copy of this app and my formula looks like this [DayStartDate] = [_THISROW].[DayStartDate]

YanChang

Colums - Payment

Key	Client id	Service	Start of payment period	End of payment period	Day1Key	Day1StartDate	Day1EndDate	Day1StartTime	Day1EndTime	Day2Key	Day2StartDate	Day2EndDate	Day2StartTime	Day2EndTime	Day3Key	Day3StartDate	Day3EndDate	Day3StartTime	Day3EndTime	Day4Key	Day4StartDate	Day4EndDate	Day4StartTime	Day4EndTime	Day5Key	Day5StartDate	Day5EndDate	Day5StartTime	Day5EndTime	Day6Key	Day6StartDate	Day6EndDate	Day6StartTime	Day6EndTime	Day7Key	Day7StartDate	Day7EndDate	Day7StartTime	Day7EndTime	Day8Key	Day8StartDate	Day8EndDate	Day8StartTime	Day8EndTime	Day9Key	Day9StartDate	Day9EndDate	Day9StartTime	Day9EndTime	Day10Key	Day10StartDate	Day10EndDate	Day10StartTime	Day10EndTime	Day11Key	Day11StartDate	Day11EndDate	Day11StartTime	Day11EndTime	Day12Key	Day12StartDate	Day12EndDate	Day12StartTime	Day12EndTime
cabf2c5c	af57b723	Абонемент 30 дней	10/15/2020	11/14/2020	2cb9f107	10/15/2020	10/15/2020	7:00:00	8:00:00	590a531b	10/17/2020	10/17/2020	7:00:00	8:00:00	15ec5fea	10/20/2020	10/20/2020	7:00:00	8:00:00	c317abbc	10/22/2020	10/22/2020	7:00:00	8:00:00	444ca91f	10/24/2020	10/24/2020	7:00:00	8:00:00	d2a2b3f6	10/27/2020	10/27/2020	7:00:00	8:00:00	9f96d2ac	10/29/2020	10/29/2020	7:00:00	8:00:00	4c11facd	10/31/2020	10/31/2020	7:00:00	8:00:00	e804b5c0	11/3/2020	11/3/2020	7:00:00	8:00:00	59e0335b	11/5/2020	11/5/2020	7:00:00	8:00:00	538ccc11	11/7/2020	11/7/2020	7:00:00	8:00:00	b699d486	11/10/2020	11/10/2020	7:00:00	8:00:00
3a7bfe3b	22a7b7af	Абонемент 30 дней	10/17/2020	11/16/2020	c557f25d	10/17/2020	10/17/2020	8:00:00	9:00:00	13031a92	10/20/2020	10/20/2020	8:00:00	9:00:00	f6d0709b	10/22/2020	10/22/2020	8:00:00	9:00:00	2cbc70cc	10/24/2020	10/24/2020	8:00:00	9:00:00	7b5745ce	10/27/2020	10/27/2020	8:00:00	9:00:00	0d74708d	10/29/2020	10/29/2020	8:00:00	9:00:00	0feafbc5	10/31/2020	10/31/2020	8:00:00	9:00:00	77353446	11/3/2020	11/3/2020	8:00:00	9:00:00	6464be72	11/5/2020	11/5/2020	8:00:00	9:00:00	e52624a4	11/7/2020	11/7/2020	8:00:00	9:00:00	732869ef	11/10/2020	11/10/2020	8:00:00	9:00:00	db4b4986	11/12/2020	11/12/2020	8:00:00	9:00:00
7f7e9f5d	a997c474	Абонемент 30 дней	10/19/2020	11/18/2020	d90b24bd	10/19/2020	10/19/2020	8:00:00	9:00:00	ef5013e4	10/21/2020	10/21/2020	8:00:00	9:00:00	474c4174	10/23/2020	10/23/2020	8:00:00	9:00:00	de7eedea	10/26/2020	10/26/2020	8:00:00	9:00:00	994560b8	10/28/2020	10/28/2020	8:00:00	9:00:00	aabbf1ce	10/30/2020	10/30/2020	8:00:00	9:00:00	24b717a9	11/2/2020	11/2/2020	8:00:00	9:00:00	0a79d41f	11/4/2020	11/4/2020	8:00:00	9:00:00	3202249e	11/6/2020	11/6/2020	8:00:00	9:00:00	603309d8	11/9/2020	11/9/2020	8:00:00	9:00:00	c2d5bee5	11/11/2020	11/11/2020	8:00:00	9:00:00	39658045	11/13/2020	11/13/2020	8:00:00	9:00:00
69f82ce0	af57b723	Абонемент 30 дней	10/21/2020	11/20/2020	7bc93aec	10/21/2020	10/21/2020	7:00:00	8:00:00	12d3bde4	10/23/2020	10/23/2020	7:00:00	8:00:00	43987312	10/26/2020	10/26/2020	7:00:00	8:00:00	e93d57f8	10/28/2020	10/28/2020	7:00:00	8:00:00	3c0cc8ce	10/30/2020	10/30/2020	7:00:00	8:00:00	6d54f3db	11/2/2020	11/2/2020	7:00:00	8:00:00	d146cfbe	11/4/2020	11/4/2020	7:00:00	8:00:00	4cdea7e4	11/6/2020	11/6/2020	7:00:00	8:00:00	3b62b81d	11/9/2020	11/9/2020	7:00:00	8:00:00	466724d5	11/11/2020	11/11/2020	7:00:00	8:00:00	c041f460	11/13/2020	11/13/2020	7:00:00	8:00:00	6218fa60	11/16/2020	11/16/2020	7:00:00	8:00:00
4dc09143	af57b723	Абонемент 30 дней	10/21/2020	11/20/2020	723c3c47	10/21/2020	10/21/2020	9:00:00	10:00:00	d483cffd	10/23/2020	10/23/2020	9:00:00	10:00:00	ec9db552	10/26/2020	10/26/2020	9:00:00	10:00:00	232abe08	10/28/2020	10/28/2020	9:00:00	10:00:00	0d685064	10/30/2020	10/30/2020	9:00:00	10:00:00	0d724b06	11/2/2020	11/2/2020	9:00:00	10:00:00	1da90236	11/4/2020	11/4/2020	9:00:00	10:00:00	a0c9dc42	11/6/2020	11/6/2020	9:00:00	10:00:00	02e1b176	11/9/2020	11/9/2020	9:00:00	10:00:00	bca937ab	11/11/2020	11/11/2020	9:00:00	10:00:00	ef64c1b7	11/13/2020	11/13/2020	9:00:00	10:00:00	5372bbab	11/16/2020	11/16/2020	9:00:00	10:00:00

And Columns - Calendar

Key	Client id	Service	DayKey	DayStartDate	DayEndDate	DayStartTime	DayEndTime
e6ff1dd2	af57b723	Абонемент 30 дней	2cb9f107	10/15/2020	10/15/2020	7:00:00	8:00:00
e46afbc2	af57b723	Абонемент 30 дней	590a531b	10/17/2020	10/17/2020	7:00:00	8:00:00
a560fd7f	af57b723	Абонемент 30 дней	15ec5fea	10/20/2020	10/20/2020	7:00:00	8:00:00
4cace40c	af57b723	Абонемент 30 дней	c317abbc	10/22/2020	10/22/2020	7:00:00	8:00:00
c399d238	af57b723	Абонемент 30 дней	444ca91f	10/24/2020	10/24/2020	7:00:00	8:00:00
46dabbe4	af57b723	Абонемент 30 дней	d2a2b3f6	10/27/2020	10/27/2020	7:00:00	8:00:00
60f7684b	af57b723	Абонемент 30 дней	9f96d2ac	10/29/2020	10/29/2020	7:00:00	8:00:00
f11b5a65	af57b723	Абонемент 30 дней	4c11facd	10/31/2020	10/31/2020	7:00:00	8:00:00
7dfe3a74	af57b723	Абонемент 30 дней	e804b5c0	11/3/2020	11/3/2020	7:00:00	8:00:00
e82fd64e	af57b723	Абонемент 30 дней	59e0335b	11/5/2020	11/5/2020	7:00:00	8:00:00
471c3b6c	af57b723	Абонемент 30 дней	538ccc11	11/7/2020	11/7/2020	7:00:00	8:00:00
97d65479	af57b723	Абонемент 30 дней	b699d486	11/10/2020	11/10/2020	7:00:00	8:00:00
58aadf0e	22a7b7af	Абонемент 30 дней	c557f25d	10/17/2020	10/17/2020	8:00:00	9:00:00
710ecccd	22a7b7af	Абонемент 30 дней	13031a92	10/20/2020	10/20/2020	8:00:00	9:00:00
5f60e05c	22a7b7af	Абонемент 30 дней	f6d0709b	10/22/2020	10/22/2020	8:00:00	9:00:00
12e14a1e	22a7b7af	Абонемент 30 дней	2cbc70cc	10/24/2020	10/24/2020	8:00:00	9:00:00
b0732330	22a7b7af	Абонемент 30 дней	7b5745ce	10/27/2020	10/27/2020	8:00:00	9:00:00
14089898	22a7b7af	Абонемент 30 дней	0d74708d	10/29/2020	10/29/2020	8:00:00	9:00:00
b82dcd28	22a7b7af	Абонемент 30 дней	0feafbc5	10/31/2020	10/31/2020	8:00:00	9:00:00
076a2da0	22a7b7af	Абонемент 30 дней	77353446	11/3/2020	11/3/2020	8:00:00	9:00:00
95941f21	22a7b7af	Абонемент 30 дней	6464be72	11/5/2020	11/5/2020	8:00:00	9:00:00
268a837d	22a7b7af	Абонемент 30 дней	e52624a4	11/7/2020	11/7/2020	8:00:00	9:00:00
3241258d	22a7b7af	Абонемент 30 дней	732869ef	11/10/2020	11/10/2020	8:00:00	9:00:00
f896e388	22a7b7af	Абонемент 30 дней	db4b4986	11/12/2020	11/12/2020	8:00:00	9:00:00
111cdfa5	a997c474	Абонемент 30 дней	d90b24bd	10/19/2020	10/19/2020	8:00:00	9:00:00
6bae426e	a997c474	Абонемент 30 дней	ef5013e4	10/21/2020	10/21/2020	8:00:00	9:00:00
597bae72	a997c474	Абонемент 30 дней	474c4174	10/23/2020	10/23/2020	8:00:00	9:00:00
55451ef3	a997c474	Абонемент 30 дней	de7eedea	10/26/2020	10/26/2020	8:00:00	9:00:00
ed12b2fe	a997c474	Абонемент 30 дней	994560b8	10/28/2020	10/28/2020	8:00:00	9:00:00
eaf372ea	a997c474	Абонемент 30 дней	aabbf1ce	10/30/2020	10/30/2020	8:00:00	9:00:00
c9dacfa5	a997c474	Абонемент 30 дней	24b717a9	11/2/2020	11/2/2020	8:00:00	9:00:00
00b228ec	a997c474	Абонемент 30 дней	0a79d41f	11/4/2020	11/4/2020	8:00:00	9:00:00
d2837b95	a997c474	Абонемент 30 дней	3202249e	11/6/2020	11/6/2020	8:00:00	9:00:00
0f91402a	a997c474	Абонемент 30 дней	603309d8	11/9/2020	11/9/2020	8:00:00	9:00:00
9772fe38	a997c474	Абонемент 30 дней	c2d5bee5	11/11/2020	11/11/2020	8:00:00	9:00:00
b3278460	a997c474	Абонемент 30 дней	39658045	11/13/2020	11/13/2020	8:00:00	9:00:00
97f5ca6c	af57b723	Абонемент 30 дней	7bc93aec	10/21/2020	10/21/2020	7:00:00	8:00:00
6d2f8397	af57b723	Абонемент 30 дней	12d3bde4	10/23/2020	10/23/2020	7:00:00	8:00:00
b574ebfb	af57b723	Абонемент 30 дней	43987312	10/26/2020	10/26/2020	7:00:00	8:00:00
cd205051	af57b723	Абонемент 30 дней	e93d57f8	10/28/2020	10/28/2020	7:00:00	8:00:00
1495bfa5	af57b723	Абонемент 30 дней	3c0cc8ce	10/30/2020	10/30/2020	7:00:00	8:00:00
0a8f1852	af57b723	Абонемент 30 дней	6d54f3db	11/2/2020	11/2/2020	7:00:00	8:00:00
71e852d0	af57b723	Абонемент 30 дней	d146cfbe	11/4/2020	11/4/2020	7:00:00	8:00:00
cc9e480b	af57b723	Абонемент 30 дней	4cdea7e4	11/6/2020	11/6/2020	7:00:00	8:00:00
ff52b8cd	af57b723	Абонемент 30 дней	3b62b81d	11/9/2020	11/9/2020	7:00:00	8:00:00
dd711e5c	af57b723	Абонемент 30 дней	466724d5	11/11/2020	11/11/2020	7:00:00	8:00:00
393af92a	af57b723	Абонемент 30 дней	c041f460	11/13/2020	11/13/2020	7:00:00	8:00:00
c0baef59	af57b723	Абонемент 30 дней	6218fa60	11/16/2020	11/16/2020	7:00:00	8:00:00
61544cf6	af57b723	Абонемент 30 дней	723c3c47	10/21/2020	10/21/2020	9:00:00	10:00:00
61031b82	af57b723	Абонемент 30 дней	d483cffd	10/23/2020	10/23/2020	9:00:00	10:00:00
26fbda78	af57b723	Абонемент 30 дней	ec9db552	10/26/2020	10/26/2020	9:00:00	10:00:00
b504a247	af57b723	Абонемент 30 дней	232abe08	10/28/2020	10/28/2020	9:00:00	10:00:00
52aeadcd	af57b723	Абонемент 30 дней	0d685064	10/30/2020	10/30/2020	9:00:00	10:00:00
1b985a69	af57b723	Абонемент 30 дней	0d724b06	11/2/2020	11/2/2020	9:00:00	10:00:00
82da6acc	af57b723	Абонемент 30 дней	1da90236	11/4/2020	11/4/2020	9:00:00	10:00:00
0b05ca72	af57b723	Абонемент 30 дней	a0c9dc42	11/6/2020	11/6/2020	9:00:00	10:00:00
9002c52b	af57b723	Абонемент 30 дней	02e1b176	11/9/2020	11/9/2020	9:00:00	10:00:00
3a15125b	af57b723	Абонемент 30 дней	bca937ab	11/11/2020	11/11/2020	9:00:00	10:00:00
292caad6	af57b723	Абонемент 30 дней	ef64c1b7	11/13/2020	11/13/2020	9:00:00	10:00:00
a7893887	af57b723	Абонемент 30 дней	5372bbab	11/16/2020	11/16/2020	9:00:00	10:00:00

From the “Payment” column, the data is rewritten into the vertical column “Calendar” in order for the data to be displayed correctly in the calendar

JuneCorpuz

Can you please post a sample Detail View that contains the CalendarVirtualTime1? I’m having a hard time understanding the structure of the data.

YanChang

JuneCorpuz

Your virtual column is correct. Are you applying this formula to column Day1StartDate or to different column such as Day2StartDate, Day3StartDate?