dependent dropdown in a subform

[johan]

Steven
It's more complicated. Every row has a date - start - end - prison - section - room.
There are only a few sections in every prison. But for every reservation I have to be sure that there are not to much persons in a room and not to much persons in a section.
I need date -start - end - section in my query when populating select or lookup.
Johan

[steven]

johan,

Can you show a picture of the whole Edit Form?

How do you record the people and what room they are in?


Steven

[johan]

Steven

We want to be able to reservate rooms with this.
The form contains a number of parameters such as first date, last date, ...
With the help of Kev1n I have a procedure to have the rooms filled in the subform. Where there is a conflict (too many people in a section or no free room) it leaves section and room empty in the subform so we can manually add a room.
Below is the query for section in the subform. In this query I need Datum - start -eind from the row of the subform

Code: Select all

SELECT distinct secties.sec_id, secties.sec_name FROM secties 
inner join gevangenis  on gevangenis.gev_id = secties.sec_gevangenis
where  secties.sec_gevangenis = '#act_gevangenis#' 
AND (coalesce((select sum(act_deelnemers)
    from activiteiten
    inner join reservaties on activiteiten.act_id = reservaties.res_act_id
    where reservaties.res_datum ='#res_datum#' AND  '#res_uur_eind#' > res_uur_start   AND  '#res_uur_start#' < res_uur_eind and res_sectie = '#res_sectie#' ),0)  + '#act_deelnemers#') <= secties.sec_max

Johan

[steven]

johan

I'm guessing all the sections are in the sectie table and linked to a record in the gevangenis table.

What is the total number of records in the sectie table?

It would be far easier to create a lookup that has these 2 tables linked,
click on the magnifying glass,
type in the gevangenis (or part of it),
and pick the sectie from there.

You would only need to have a sectie ID stored in the subform table because it is already linked to the gevangenis table.

Like this...

prison.png

Steven

Steven

[johan]

Steven
I have 12 sections and I can use #act_gevangenis# (from editform) to link to. 1 activity will never take place in 2 different prisons.

But in an earlier post you wrote

Use a Lookup instead of a Select. This will allow the use of Hash Cookies but only from the main Edit Form (you may need to populate a hidden field before you launch the Lookup.

So even with al lookup I still can't use res_datum, res_uur_start, res_uur_eind to check if the room is available and total persons in section.

Johan

[steven]

johan,

Your original question was

How can I create a dependent dropdown in a subform?
sectie depents on gevangenis.

It seems different from calculating room availability.

I was trying to answer the first question, so I don't really know how to help when you say.

So even with al lookup I still can't use res_datum, res_uur_start, res_uur_eind to check if the room is available and total persons in section.

Steven

[johan]

Steven
My first question was indeed how to create depending select because I hoped I could use the other objects of the subform in my SQL.
I understand that what I want is not possible. Maybe a suggestion for a future release?

Johan



Johan

[kev1n]

Hi Johan,

Cascaded select objects are not supported in subforms.
You could try fiddling with some JavaScript: Add a where clause in the 2nd select using a hash cookie like WHERE something = '#VALUE_SELECT1";
When the value in the first select is changed, set the hashcookie with the value of the first select with nuSetProperty('VALUE_SELECT1', nuGetValue('SELECT_1_OBJECT_ID"))
and call nuRefreshSelectObject(...) to refresh the 2nd select.

[johan]

Kevin

With

Code: Select all

nuSubformValue(this, 'res_gevangenis');

I get the value of the first select in console.log

But when I add

Code: Select all

nuSetProperty('VALUE_SELECT1',nuSubformValue(this, 'res_gevangenis'));

It seems '#VALUE_SELECT1#' is empty.
Any idea?
Johan

[kev1n]

How did you see that it's empty?