Lookup Filter

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
mcvaidas
Posts: 26
Joined: 2019-04-14 17:45

Lookup Filter

Post by mcvaidas » 2022-01-16 19:15

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
Attachments
Screenshot 2022-01-16 at 19.42.31 1.jpg
Screenshot 2022-01-16 at 19.42.31 1.jpg (109.85 KiB) Viewed 3239 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Lookup Filter

Post by pbottcher » 2022-01-22 17:15

Hi,

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.

mcvaidas
Posts: 26
Joined: 2019-04-14 17:45

Re: Lookup Filter

Post by mcvaidas » 2022-01-23 18:38

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 ?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup Filter

Post by jsetzer » 2022-01-24 10:06

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:
  1. In registrations-table, remove the lookup-settings of guest field.
  2. In registrations-table create a guest_id field (integer, unsigned)
  3. Make event field required.
  4. Generate the application and apply all database changes, if necessary
  5. Let the user create a new Registration with event selected.
    In Insert-Mode of registrations table, hide guest_id.
    Remember: event is required.
  6. After user has inserted a Registration (with selected event), get the available guests in PHP. You will find the code below.
  7. 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.
  8. When (newly inserted) Registration will be reloaded (in edit mode, now), don't hide guest_id field
  9. 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
AppGini_Ab7AEZ6mzc.png (8.69 KiB) Viewed 2980 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>';
		}
	}
}
Result photo-story (I cannot upload an MP4 here):
  1. First registration for Event #1 offers all three Guests A, B, C:
    chrome_TYOPxRlBI6.png
    chrome_TYOPxRlBI6.png (5.23 KiB) Viewed 2980 times
    I select Event #1 + Guest A and save the registation record (ID:11)
  2. 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
    chrome_GSEHZ4asAa.png (4.01 KiB) Viewed 2980 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.
Attachments
chrome_P9xYJIk8Pj.png
chrome_P9xYJIk8Pj.png (5.38 KiB) Viewed 2980 times
chrome_3jbDMLmpD3.png
chrome_3jbDMLmpD3.png (5.8 KiB) Viewed 2980 times
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup Filter

Post by jsetzer » 2022-01-24 10:23

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.
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup Filter

Post by jsetzer » 2022-01-24 10:32

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:
chrome_eDwmbKZDK3.png
chrome_eDwmbKZDK3.png (6.1 KiB) Viewed 2979 times
Kind regards,
<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 readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

mcvaidas
Posts: 26
Joined: 2019-04-14 17:45

Re: Lookup Filter

Post by mcvaidas » 2022-01-24 17:15

Wow!
Herzlichen Dank

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup Filter

Post by jsetzer » 2022-01-24 17:57

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Lookup Filter

Post by xbox2007 » 2022-01-28 19:45

hello

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>';
		}
	}
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

mcvaidas
Posts: 26
Joined: 2019-04-14 17:45

Re: Lookup Filter

Post by mcvaidas » 2022-01-30 15:36

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;
}

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Lookup Filter

Post by xbox2007 » 2022-02-01 05:56

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup Filter

Post by jsetzer » 2022-02-01 06:16

mcvaidas wrote:
2022-01-30 15:36
...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;
}
Or you can create a calculated field as I did:

AppGini_DGmzS27uA4.png
AppGini_DGmzS27uA4.png (33.12 KiB) Viewed 2748 times

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Lookup Filter

Post by xbox2007 » 2022-02-01 08:33

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

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>';
		}
thanks again

Post Reply