Hello. im stuck in simple projekt.
Tables: Registration (for event)
Events
Guests
Reservation have two loockup fields:
Event name (from events)
Guest name (from guests)
How to get guest name (registration table) in lookup where is not in selected event?
How to get data for sql advanced filter from 1st field (event in registration table) to filter second guest
Lookup Filter
Lookup Filter
- Attachments
-
- Screenshot 2022-01-16 at 19.42.31 1.jpg (109.85 KiB) Viewed 3316 times
Re: Lookup Filter
Hi,
I dont think this is possible with the standard setup.
I dont think this is possible with the standard setup.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Lookup Filter
only way ist javascript to detect selected event in registration? and after filter user (to show only uniq users for event).but cascading drop downs works without spec javascript may be is includet funktion ?
Re: Lookup Filter
Gooid morning,
for getting a list of available guests of an event, we need to know the already registered guests of that event. So, in any case, we need to know the given primary key of the event. As said before, standard Advanced-SQL will not allow joining other tables than the table itself. This means we cannot use built-in lookup functionality, as the expert guys above have already noted.
But we can create a custom dropdown with our own data. It's not too hard.
All we need to do is:
This is the required model, slightly different from yours
Note that the foreign keys in my test project differ from your model, for example "event" -> "event_id".
And this is the PHP code, containig comments for readability:
Result photo-story (I cannot upload an MP4 here):
For completeness: List of Events, List of Guests.
for getting a list of available guests of an event, we need to know the already registered guests of that event. So, in any case, we need to know the given primary key of the event. As said before, standard Advanced-SQL will not allow joining other tables than the table itself. This means we cannot use built-in lookup functionality, as the expert guys above have already noted.
But we can create a custom dropdown with our own data. It's not too hard.
All we need to do is:
- In
registrations
-table, remove the lookup-settings ofguest
field. - In
registrations
-table create aguest_id
field (integer, unsigned) - Make
event
field required. - Generate the application and apply all database changes, if necessary
- Let the user create a new Registration with
event
selected.
In Insert-Mode of registrations table, hideguest_id
.
Remember:event
is required. - After user has inserted a Registration (with selected event), get the available guests in PHP. You will find the code below.
- Convert the
guest_in
-<input/>
-field into a custom dropdown holding the list of available guests in _dv-hook using .select2 javascript function. Pass the array of available users which you have evaluated just before. - When (newly inserted) Registration will be reloaded (in edit mode, now), don't hide
guest_id
field - Perhaps turn
event
-lookup into a readonly display.
This is the required model, slightly different from yours
Note that the foreign keys in my test project differ from your model, for example "event" -> "event_id".
And this is the PHP code, containig comments for readability:
Code: Select all
function registrations_dv($selectedID, $memberInfo, &$html, &$args)
{
// get data
$registration = $selectedID ? getRecord("registrations", $selectedID) : null;
$event_id = $registration ? $registration["event_id"] : null;
$guest_id = $registration ? $registration["guest_id"] : null;
if (!$event_id) {
// event not chosen, yet
// > hide guest
$html .= '<script>jQuery("#guest_id,#guest_name").closest(".form-group").hide();</script>';
} else {
// event selected.
// > Find available guests
// > create and populate select box
$avai = sqlValue("SELECT group_concat(id) FROM `guests` WHERE `guests`.`id` NOT IN (select registrations.guest_id from registrations where registrations.event_id = '{$event_id}' AND registrations.id != '{$selectedID}')");
$data = $avai ? array_merge([["id" => empty_lookup_value, "text" => "(none)"]], array_map(function ($id) {
return ["id" => $id, "text" => getRecord("guests", $id)["name"]];
}, explode(",", $avai))) : [];
$html .= '<script>
jQuery("#guest_id").select2({ minimumResultsForSearch: Infinity, width: "100%", data: ' . json_encode($data) . '});
jQuery("#s2id_guest_id").removeClass("form-control");
</script>';
}
}
}
- First registration for Event #1 offers all three Guests A, B, C: I select Event #1 + Guest A and save the registation record (ID:11)
- Now I create another registration for Event #1. This time only B and C will be offered for selected. A has already been selected in a different registration.
For completeness: List of Events, List of Guests.
- Attachments
-
- chrome_P9xYJIk8Pj.png (5.38 KiB) Viewed 3057 times
-
- chrome_3jbDMLmpD3.png (5.8 KiB) Viewed 3057 times
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup Filter
Screen-Recording (externally hosted on VIMEO):
https://vimeo.com/669352295
PS: This is the first time I try to share a too big file here in the forum. I don't know if VIMEO is a good, persistent and accepted platform for uploading and sharing MP4's. If anyone knows a better place, compliant with forum rules, I will be happy to use a different hosting service in the future.
https://vimeo.com/669352295
PS: This is the first time I try to share a too big file here in the forum. I don't know if VIMEO is a good, persistent and accepted platform for uploading and sharing MP4's. If anyone knows a better place, compliant with forum rules, I will be happy to use a different hosting service in the future.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup Filter
Forgot to say: The list of available guests is event-dependent. So, when inserting the first registration for a diferent event, obviously all guests have to be (and will be) available for that next event.
See next screenshot: Registrations for the (different) Event #2:
See next screenshot: Registrations for the (different) Event #2:
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup Filter
You are welcome
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup Filter
hello
i already see your video for this problem ,
but i try use same code
but
guest_id and guest_name not hide also guest_id not change to select2
please let me know if there are any update for this problem
i already see your video for this problem ,
but i try use same code
Code: Select all
function registrations_dv($selectedID, $memberInfo, &$html, &$args)
{
// get data
$registration = $selectedID ? getRecord("registrations", $selectedID) : null;
$event_id = $registration ? $registration["event_id"] : null;
$guest_id = $registration ? $registration["guest_id"] : null;
if (!$event_id) {
// event not chosen, yet
// > hide guest
$html .= '<script>jQuery("#guest_id,#guest_name").closest(".form-group").hide();</script>';
} else {
// event selected.
// > Find available guests
// > create and populate select box
$avai = sqlValue("SELECT group_concat(id) FROM `guests` WHERE `guests`.`id` NOT IN (select registrations.guest_id from registrations where registrations.event_id = '{$event_id}' AND registrations.id != '{$selectedID}')");
$data = $avai ? array_merge([["id" => empty_lookup_value, "text" => "(none)"]], array_map(function ($id) {
return ["id" => $id, "text" => getRecord("guests", $id)["name"]];
}, explode(",", $avai))) : [];
$html .= '<script>
jQuery("#guest_id").select2({ minimumResultsForSearch: Infinity, width: "100%", data: ' . json_encode($data) . '});
jQuery("#s2id_guest_id").removeClass("form-control");
</script>';
}
}
guest_id and guest_name not hide also guest_id not change to select2
please let me know if there are any update for this problem
Re: Lookup Filter
you can hide guest_name in Form just for table view
and put this code in befor update funkction
function registration_before_update(&$data, $memberInfo, &$args) {
$guest_name= sqlValue("select name from guests where id = '{$data['guest_id']}'");
$data['guest_name']=$guest_name;
return TRUE;
}
and put this code in befor update funkction
function registration_before_update(&$data, $memberInfo, &$args) {
$guest_name= sqlValue("select name from guests where id = '{$data['guest_id']}'");
$data['guest_name']=$guest_name;
return TRUE;
}
Re: Lookup Filter
thanks for your replay
actual i dont want hide $guest_name
i would like Convert the guest_in-<input/>-field into a custom dropdown holding the list of available guests in _dv-hook using .select2 javascript function. Pass the array of available users which you have evaluated just before.
but its not working
JavaScript code not working , i dont know what wrong
thank a lot
actual i dont want hide $guest_name
i would like Convert the guest_in-<input/>-field into a custom dropdown holding the list of available guests in _dv-hook using .select2 javascript function. Pass the array of available users which you have evaluated just before.
but its not working
JavaScript code not working , i dont know what wrong
thank a lot
Re: Lookup Filter
Or you can create a calculated field as I did:
SQL Command for calculation
Code: Select all
select guests.name
from registrations left join guests
on guests.id = registrations.guest_id
where registrations.id=%ID%
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup Filter
thanks a lot for your replay ,
i would like Convert the g input field into a custom select2 dropdown .
this code not working with me
thanks again
i would like Convert the g input field into a custom select2 dropdown .
this code not working with me
Code: Select all
$html .= '<script>
jQuery("#guest_id").select2({ minimumResultsForSearch: Infinity, width: "100%", data: ' . json_encode($data) . '});
jQuery("#s2id_guest_id").removeClass("form-control");
</script>';
}