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:
- In
registrations-table, remove the lookup-settings of guest field.
- In
registrations-table create a guest_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, hide guest_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.
That's it. Looks complicated at first, but can be done with less than 20 lines of PHP code.
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".
- AppGini_Ab7AEZ6mzc.png (8.69 KiB) Viewed 5239 times
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:
- chrome_TYOPxRlBI6.png (5.23 KiB) Viewed 5239 times
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.
- chrome_GSEHZ4asAa.png (4.01 KiB) Viewed 5239 times
PS: I am going to do a short screen-recording the next minutes. Unfortunately I cannot upload an MP4 here. So I will try to share the MP4 on a different file sharing service (I have seen many community members doing so). Anyway, if this speaks against the forum rules, @Ahmed, please just delete that next post.
For completeness: List of Events, List of Guests.