Create a dropdown list on a standard data field from query

[Taher_Khalil]

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

[clarkej]

@Taher_Khalil

any help is appreciated ,

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!

[Taher_Khalil]

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 ,

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

Get specifics field in get_query function

I just figured this out @geekroot. This is what happened to me and I have a similar issue. You need to select the name column of your custom DocType as the first column. When selecting the item in the dropdown it’s “cleaning itself” because a validation function is run to validate the selected item. It validates the item by it’s first selected column which is expected to be the name aka the primary key of your table. So your query should look like: select name, codigo_distrito, nombre_distrit…

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

[System19]

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.