I have a table with all different machines in.
table Machines
I have a table with my Rentals agenda.
Table Rental Agenda
this table contains all machines that are not available … if you can not find it in this table it means that the machine is available at that specific moment.
i want to make a search table …
with the following fields
Table Lookup
-Machine Type
-Start Date
-Stop Date
The user can make his selection of those three and then the formula looks up if there are machines available.
this search table has a virtual column where this formula is in…
Any idea on how to set this formula up ? thanks
things i have tried…
formula below gives me all machine that are not available in that time i guess when looking at the selected start & stop date compared to the existing Start and stop Rentals
select(Rental Agenda[serial number],and([Start Rental] <[_THISROW].[Start Date], [Start Rental] <[_THISROW].[Stop Date]))
select(Rental Agenda[serial number],and([Stop Rental] > [_THISROW].[Start Date], [Stop Rental] >[_THISROW].[StopDate]))
is that correct ?
i took the inverse of what i should have so these should be a list of not available machines as they come out of the agenda table
can you some how join to list without making doubles ?
This is still a list of all machine type togethers can i add this somehow in this formula so it is only giving the not available machines from a certain machine type ?
and can i than subtract those serials from the list of total machines so i get the available machines …
all help is more than welcome because i have no clue how to get there …
thanks
Solved! Go to Solution.
Like… SELECT(Machines[Serial Number],[Machine Type]=“Drill”) - SELECT(…)
How about… Machines[Serial Number] - SELECT(Rental Agenda[serial number],AND([_THISROW].[Stop Date]>=[Start Rental],[_THISROW].[Start Date]<=[Stop Rental]))
Hi Aleksi, thanks
this is already helping a lot…
any idea on how to add the machine type filter as well on it ?
Like… SELECT(Machines[Serial Number],[Machine Type]=“Drill”) - SELECT(…)
thanks, that did the job
You’re welcome
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |