Create a dropdown list on a standard data field from query

Business logic / requiremnet
There are fixed number of events that can be booked/created per hour/fixed time,
for that i have a availability table created with the date , the time and the availability corresponding to that time and date,
now on a separate form we have to choose the available date and time and save which will create the event for that date and time.

issue
now there is a child table where we can select date and time and the event will be created.
But i want to only show those date and time which are available .
so i thought of doing a get_query on that field and fetch only the available dates from the sql query ,

But query/filter does not apply on date or time field

then i thought i will make those date and time fields as link of the availability table , then on date field only fetch those dates which are available , and on time field only those times which are available for that date ,
.
so on doing get query it fetches the dates and times according to query , but does not set on selection , as its expecting only objects of the table , i.e entire table objects.
.
so i want how can i set a query on a data field (set_query doesn’t generate a drop down list as i want) , to only show those query fields.

as another objective is to only show distinctive dates .
because there might be multiple entries for same date i.e 5th dec 5pm available -5 , 5th dec 9pm available -1.

so i can select dates from table which are available , then times of that date which are available .

any help is appreciated ,

Thanks

What sort of help do you seek?

To best inform and outline your case, please for eg refer to other posts, a screenshot or code to show what you mean and your research

thanks!

I guess the details given above are too broad -
use case
Create a table of available dates and times of those dates, then on while booking need to show only those dates which are available in a list format to choose.

implementation

  1. Created an entry of the available bookable appointments for that hour on those dates,

    this creates a entry in availability table for all the dates and times and their respective current availability .

now this is the table which is referred to while making a booking , and if an appointment/booking is made for a specific time on a date then its availability is decreased.


now here is where i am stuck and need help
There is document where the appointment is actually made according to availability and then the event is created in background.

a single customer can make multiple bookings/appointments together - so i added a child table with date and time to select ,


Here i want to only show dates and times which are available i.e query from the availability table and show only those dates in a list –
i checked and its not possible to do it on the date and time field types , so i need a list type to pass the query , so i do just that make the field as link field of option availability table ,
and pass a query ,
image

and the result is perfectly what i want ,

.
yay success , but as soon as i select a date , it sets for a micro second then clears out ,
so
i think because it is expecting the entire table name and not the one column,
.
so i wanted to know how can i achieve this or how can i get such list from query on a data field

the closest to my situation and answer i got was from

but doesn’t provide a specific clear solution ,
there is even a Hack app made by @max_morais_dmm ,which displays and sets the title field , but it does not install on the latest version because of changes . (can i request a fix please?) ,

also there can only be 1 title field in a document , so its not that flexible,
i was wondering if now in v11 there is a function or a way to do this at a generic level .

Let me know your views,

Thanks

There DocTypes in the Healthcare Module that might help you. maybe check out Healthcare / Patient Appointment. It checks for the availability of the Healthcare Practitioner and other settings while blocking double booking etc.