cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vaibhavtandon87
Helper IV
Helper IV

how data should be structured for desk reservation app

Dear community,

 

I was starting to build a desk reservation app but scratching my head how should I structure my data on the Sharepoint.

 

Per day I have only 50 desks available and user can select any date for the month. I am not sure how many Sharepoint list I should create to have 50 desks available per day basis calendar selection. User should also be able to see slots available for that day.

 

Any guidance on how many list I should create or how to source all the data in 1 list to cater to above requirement?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
KvB1
Solution Specialist
Solution Specialist

First note: you should not create a custom column using ID as the name, this can cause issues later on since its also the name of the native ID column that you have no control over. 
Warren wrote a nice blog: https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/

I'm not a big fan of forms and I rarely use them, so I don't have that much experience with them.

 

What I would do is create a simple Gallery with your List1 in the items property.

 

- Stuff like floor you can just show by adding a label with ThisItem.Floor under text property

- You'd need a label to indicate if its available or not. To do this you ask the app to search your List2 to find if there is a record with the date that has been selected for that DeskID. For example, in text property of availability label use:

 

 

If(
	IsBlank(
		LookUp(
			List2,
			DeskID=ThisItem.DeskID
			And
			ReservationDate=DatePicker1.SelectedDate
			)
		),
	"available",
	"taken"
)

 

 

 

Then you can have a button that will reserve the desk, you can make it invisible in the visible property using a similar formula as above

 

With the button you create a new record in List2 like so:

 

 

Patch(
	List2,
	Defaults(List2),
	{
		DeskID: ThisItem.DeskID,
		Date: DatePicker1.SelectedDate,
		ReservedBy: User().Email
	}
)

 

 

View solution in original post

20 REPLIES 20
KvB1
Solution Specialist
Solution Specialist

I would make 2 lists:

- First list holds all 50 desks, with some meta data you want to assign to the desk (floor, etc), and an ID

- This list will be the input list for your app

- The second list is where users create records, and keeps track of which desk has been reserved on which day and by whom

- You can filter the second list by date, and if the deskID already has a record for that date, you block it from being picked from the input list based on the first list

Thanks @KvB1 for the prompt response. I created both the list , list 1 as desk information and list 2 as desk reservation.

I created an add screen with edit form in 'New' mode to create new records. I added a separate date picker label but not sure how to filter the data in the form below basis selected value in datepicker  input . Attaching screenshots of list and add screen

desk reservation.jpg

add records screen.jpg

Desk information having ids.jpg

   

KvB1
Solution Specialist
Solution Specialist

First note: you should not create a custom column using ID as the name, this can cause issues later on since its also the name of the native ID column that you have no control over. 
Warren wrote a nice blog: https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/

I'm not a big fan of forms and I rarely use them, so I don't have that much experience with them.

 

What I would do is create a simple Gallery with your List1 in the items property.

 

- Stuff like floor you can just show by adding a label with ThisItem.Floor under text property

- You'd need a label to indicate if its available or not. To do this you ask the app to search your List2 to find if there is a record with the date that has been selected for that DeskID. For example, in text property of availability label use:

 

 

If(
	IsBlank(
		LookUp(
			List2,
			DeskID=ThisItem.DeskID
			And
			ReservationDate=DatePicker1.SelectedDate
			)
		),
	"available",
	"taken"
)

 

 

 

Then you can have a button that will reserve the desk, you can make it invisible in the visible property using a similar formula as above

 

With the button you create a new record in List2 like so:

 

 

Patch(
	List2,
	Defaults(List2),
	{
		DeskID: ThisItem.DeskID,
		Date: DatePicker1.SelectedDate,
		ReservedBy: User().Email
	}
)

 

 

View solution in original post

Thanks @KvB1 , it worked!

@KvB1 , not sure if I should be raising it as a separate topic for your help. I wanted to disable the button if there is 1 entry by the current user which is 'reserved' for the SELECTED date however not able to fix that. So for a single day user should only be able to make 1 reservation.

 

I have added the below code on the displaymode but I guess its not taking the selected date into account:

 

If(CountRows(Filter(Gallery1.AllItems, Value(Label6.Text) in "Reserve" ))>0,DisplayMode.Disabled,DisplayMode.Edit)

 

Thanks!

KvB1
Solution Specialist
Solution Specialist

If(
	IsBlank(
		LookUp(
			List2,
			DeskID=ThisItem.DeskID
			And
			ReservationDate=DatePicker1.SelectedDate
			)
		),
	"available",
	"taken"
)

This is working for you to indicate in the gallery if a desk is available or taken right?

That function checks whether a record exists for that deskID in combination with the selected date.

 

Now you want to check if a record exists for that user in combination with the selected date, so change to:

If(
	IsBlank(
		LookUp(
			List2,
			ReservedBy=User().Email
			And
			ReservationDate=DatePicker1.SelectedDate
			)
		),
	DisplayMode.Edit,
	DisplayMode.Disabled
)

Thanks again @KvB1  for your prompt response. 

 

I did try the second logic you have given earlier and now but it keeps the button disabled for the dates on which I have not reserved as well. So its kind of NOT changing basis the change in date, not sure why.

Screenshot below, for the selected date I have not made any reservation but its still disabled.

 

Button disabled after changing date.jpg

Basically I guess the condition which is missing is that - if there is one record having 'reserved' as status value then it should be disabled. 

KvB1
Solution Specialist
Solution Specialist

Thats odd, can you check the condition in the If statement using a label?

Since a record in the second list will only exist for that date/person if its reserved, I dont think it will add anything to create a reserved column for it.

 

Simple set text property of a label to:

 

IsBlank(
		LookUp(
			List2,
			ReservedBy=User().Email
			And
			ReservationDate=DatePicker1.SelectedDate
			)
		)

 

(should return true if no records exist). If it resturns false, it is still good to figure out whether the record actually exists, or whether apps decided that IsBlank is not the same as doesn't exist.

 

 

 

Len(
	LookUp(
		List2,
		ReservedBy=User().Email
		And
		ReservationDate=DatePicker1.SelectedDate
		)
	).ReservedBy
)

 

If this returns 0, you can use it instead of the IsBlank in your condition 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,416)