Allocate construction employees to the trip

[Jacek_S]

Hi,

I’m building an app to help allocating employees to cars that can be used to travel at construction site. The key constraint is that a single employee can be assigned only to a single trip, and managers that will be assigning them need to know who is available and who was already assign.

Tables:
Employees
Cars
Availability planning

In the availability planning I have a EnumList with Refernece to Employees with multi selection. So, in my table Availability planning in column employees selected, I can have Employee 1, Employee 2.

This issue I have is how to search for which employee is taken for the day in that column, and also how to restrict selecting these occupied employees for new trips that day.

I would really appreciate your help!

Jacek

[Suvrutt_Gurjar]

Please take a look at Vlaid_if constraints

help.appsheet.com

Dropdown from Valid_If

How to create a dropdown that depends on a particular list of values.

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

Your valid_if expression for the Employees column in the Availability Planning tabe can be something like

Employees[Employee]- SELECT(Availability Planning[Employee], [Booking Date]=[_THISROW].[Booking Date])

1. Employees[Employee] constructs a list of all the employees

2. SELECT(Availability Planning[Employee], [Booking Date]=[_THISROW].[Booking Date]) removes the employees booked on the date where the booking date is same as that in teh current form entry.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

LIST()

New list

[Jacek_S]

Hi,

Thanks, that is actually brilliant. It works, however, only when there’s a single employee selected. The way this works is that after selecting multiple employees, their names appear after the comma in a single cell. Any idea how to deal with that?

[Suvrutt_Gurjar · Accepted solution]

Can you please try the below?

Employees[Employee]- SPLIT(SELECT(Availability Planning[Employee], [Booking Date]=[_THISROW].[Booking Date]), " , ")

[Jacek_S]

I’m impressed. It works like a charm. Many thanks!