Use field value in custom lookup

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 741
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Use field value in custom lookup

Post by onoehring » 2021-01-13 07:11

Hi,

I described a question/problem here (viewtopic.php?p=15988#p15988) which results in this request:
It would be great, if we could use placeholders in custom lookup queries which are filled at runtime. These placeholders could (also) be fieldnames.
Example:
I am working on a record which has a field "fieldname_group".
In a lookup for that record, with for example

Code: Select all

SELECT * FROM tableX
I want to use the contents of that fiels as placeholder: If I am working on "group A", the lookup should only show records that have a connection to that group - like so

Code: Select all

SELECT * FROM tableX WHERE group='group A'
(actually instead of group A of course the value from that field, probably the ID of group A should be inserted into the query.
If I am working on a record of group B, the SQL should look like

Code: Select all

SELECT * FROM tableX WHERE group='group B'
.
Thus, I can imagine setting up the custom query something like this

Code: Select all

SELECT * FROM tableX WHERE group='{fieldname_group}'
Each time the query is being executed, the {fieldname_group} is replaced by the value of the field fieldname_group

makes sense? Hope so.
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

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

Re: Use field value in custom lookup

Post by jsetzer » 2021-01-13 08:03

@Olaf: I have seen the other post you are refering to. If I got it right, in UI (DV, clientside) you'd like to use values of the currently displayed record in the WHERE-clause of your custom query (PHP, serverside) behind a lookup.

Please note that currently the custom SQL query works on serverside SQL, which means it can only use db-persistent values. The value displayed in UI may be different from the value stored in DB. It can even be different per user per browser page, because every user, who has opened the DV of that specific record, can change any value at any time, then open the lookup.

To fulfill your feature request, on opening up the lookup, the browser has to POST current values from the client to the server, then on serverside replace the placeholders in the SQL-template by the actual (posted) UI values, then execute the SQL and return the options-list.

This is quite a complex task: The values in UI will be more complex than just strings or numbers. For example for lookups there is a primary key AND a text which can contain special characters. For date/datetime values there are different locale formats. For numeric values, there are different locale settings. What about richtext fields, image-fields, embedded youtube videos, embedded google maps, multi-select options etc.. They all have different "value" specifics.

This means the placeholders have to become more complex than just {{fieldname_NAME}}. For example if we want ui-values of a lookup inside the dynamic SQL, at least we need something like {{ui.FIELDNAME.id}} and {{ui.FIELDNAME.text}}.

So this feature request may become a huge one in programming.
Kind regards,
<js />

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

Re: Use field value in custom lookup

Post by jsetzer » 2021-01-13 08:19

Just a different approach which may be more flexible for all of us:

What about a hook-based solution like this (draft):

Code: Select all

// file: hooks/TABLENAME.php
function TABLENAME_FIELDNAME_lookup($selectedID, $ui_data, $memberInfo, &$args) {

	// you can use data from $ui_data associative array which has been posted
	
	// do your SQL query here
	$sql = "..."; 	
	$lookup_items = ...
	
	// return a list of key-value-pairs having primary key and text, for example:
	// [ 4711 => "first option", 4712 => "second option", ...]
	return $lookup_items;
}

AppGini modifications

If PHP gets the lookup-items-request for this FIELDNAME in TABLENAME, check if a function named like this exists. If it exists, execute it and return the items to the client, then populate the lookup options. If that function does not exists, use the modeled SQL query or the default query.

Challenges
  • Posting the values from client to server
  • Search string (which can be entered in lookup)
    Could be contained in $ui_data array
  • Limit (show only for first n entries)
Benefits

This would allow so much more, for example long awaited "select a member" lookup and, literally speaking, any kind of custom lookup we can imagine.

Also, this is more flexible because we can modify the SQL query at runtime without re-generating and re-deploying the application each time.
Kind regards,
<js />

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 741
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Use field value in custom lookup

Post by onoehring » 2021-01-13 10:56

Hi Jan,

I actually did mean something else (even though taking from client and using this for server request is nice too).
The table holds in a field some value. That field may eben be hidden from the user.
Gui: Lookup A pulls pulls data from table A - but that data should be filtered on the hidden field.

I am looking for a way to change the source "on the fly" .... and a hook for that would be super great!

If we have a hook for field-contents, this could open up a lot of possibilities to influence the data.
I do see the problem, that the value saved into the lookup field most likely is the primary key of another table. So in table view of course the regular lookup to that table needs to be done.
Reading your challenges - I do not really see them. When the page is rendered in the browser, the lookup could be filled by the hook. The search would then of course only find the prefiltered items. But what if the hook returns the source query (SQL) for the lookup to the lookup (instead of values as you described)?
Then, the lookup-source is set before being displayed in the browser (same thing as right now) but we have full control at runtime. It's like the custom query right now - but that the query is created on the fly and thus could be fully changed every time (if the developer wishes).

How this could be transformed to AG? Maybe in AG GUI: Use either default lookup, use custom or use SQL from hooks-file.

Your hook suggestion is marvelous.

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

Post Reply