How to fill a lookup using sql in a hook file

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
pt_lveen
Posts: 4
Joined: 2015-06-14 20:09
Contact:

How to fill a lookup using sql in a hook file

Post by pt_lveen » 2015-06-15 20:42

Hi all,

For a detail view I need a lookup to be filled by using a sql query in a hook file. But I don't know how to do this or where to start.

How can I achieve this?

Thanks.

User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Re: How to fill a lookup using sql in a hook file

Post by shasta59 » 2015-06-16 00:15

A few questions:

What version of AppGini are you using - this is very useful to know due to differences between some versions

What is determining what is filling in the lookup? A value in another field? Something tied to the date? Some other quantity? Something about the user?
Please supply more info and there may be posts already on this board which can assist you.

You can do this many ways but you do not give enough information to assist. You may not need to do it in a hook file. You could use a jquery in the tablename_templateDV.html file. Advantage of the jquery is it can make the fill in in real time not after pressing the save or save changes button(s).

Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -

pt_lveen
Posts: 4
Joined: 2015-06-14 20:09
Contact:

Re: How to fill a lookup using sql in a hook file

Post by pt_lveen » 2015-06-16 09:02

Hi Alan,

I'm using version 5.40 rev. 752

I have some tables:
1) tournament,
2) tournament_round
3) tournament_participant
4) participant
5) score

tournement_round is related to tournament
tournament_participant is related to tournament
tournament_participant is related to participant
score is related to participant
score is related to tournament_round

When I fill in the score I have to select a tournament round (the lookup shows tournement.name + torunement_round.name). The lookup should only be filled with the tournament_(round) where the participant (logid in user) is participating, via the tournament_participant table.

User avatar
shasta59
AppGini Super Hero
AppGini Super Hero
Posts: 231
Joined: 2013-01-08 19:40
Location: Calgary, Alberta, Canada

Re: How to fill a lookup using sql in a hook file

Post by shasta59 » 2015-06-16 15:34

That is possible but what you will have to do is have code to query the database and then tell it to only show those values which match.

This would be possible using jquery and mysql database lookups or write a function to do it which gets executed upon the field changing. This way you see the selection right away. You would have to query your database to get the tournament round the participant is in. Again not hard with a query lookup which returns the round based upon name or some other unique identifier. This value can then get put into the correct field by your function and allow you to see it is correct before saving.

Not a hard task but time is the big factor here. I have very little. Maybe someone else on this forum can assist.

Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -

pt_lveen
Posts: 4
Joined: 2015-06-14 20:09
Contact:

Re: How to fill a lookup using sql in a hook file

Post by pt_lveen » 2015-06-19 07:02

Thanks Alan, you brought me in the right direction!

I found the query that needed to be updated in the ajax_combo.php file.
I changed the query to include the membership_userrecords table and make use of \''.getLoggedMemberID().'\' to be able to filter on the memberID of the logged in member/player:

In code.

// drop-downs config
$lookups = array(

'Tournament_round' => array(
'parent_table' => 'Tournament_round',
'parent_pk_field' => 'id',
'parent_caption' => 'IF(CHAR_LENGTH(`Tournament_round`.`Golfcourse`) || CHAR_LENGTH(`Tournament_round`.`Round`), CONCAT_WS(\'\', IF( CHAR_LENGTH(`Golfcourse1`.`Name`), CONCAT_WS(\'\', `Golfcourse1`.`Name`), \'\'), \'- Round: \', `Tournament_round`.`Round`), \'\')',
'parent_from' => '`Tournament_round` LEFT JOIN `Golfcourse` as Golfcourse1 ON `Golfcourse1`.`id`=`Tournament_round`.`Golfcourse` LEFT JOIN `Tournament` as Tournament1 ON `Tournament1`.`id`=`Tournament_round`.`Tournament` ',
'filterers' => array('Golfcourse' => 'Golfcourse'),
/* Orginal SQL:
'custom_query' => 'SELECT DISTINCT Tournament_round.id, CONCAT_WS(\'\', Tournament.Name, \' - ROUND: \', Tournament_round.Round, \' - Course: \', Simulator.Shortname, \' - \', Golfcourse.Name) FROM Tournament_round LEFT JOIN Golfcourse ON Golfcourse.id=Tournament_round.Golfcourse LEFT JOIN Tournament ON Tournament.id=Tournament_round.Tournament LEFT JOIN Simulator ON Tournament.Simulator=Simulator.id INNER JOIN Tournament_participants ON Tournament.id=Tournament_participants.Tournament ORDER BY 2',
Changed to New SQL:
*/
'custom_query' => 'SELECT Tournament_round.id, CONCAT_WS(\'\', Tournament.Name, \' - ROUND: \', Tournament_round.Round, \' - Course: \', Simulator.Shortname, \' - \', Golfcourse.Name) FROM Tournament_round LEFT JOIN Golfcourse ON Golfcourse.id=Tournament_round.Golfcourse LEFT JOIN Tournament ON Tournament.id=Tournament_round.Tournament LEFT JOIN Simulator ON Tournament.Simulator=Simulator.id INNER JOIN Tournament_participants ON Tournament.id=Tournament_participants.Tournament WHERE 1 = 1 AND Tournament_participants.id IN ( SELECT pkValue FROM membership_userrecords WHERE tableName = \'Tournament_participants\' AND memberID = \''.getLoggedMemberID().'\' ) AND Tournament.Active = \'Yes\' ORDER BY 2',

'inherit_permissions' => false,
'list_type' => 0,
'not_null' => false
)

Lieuwe

User avatar
dilitimor
Veteran Member
Posts: 36
Joined: 2013-01-10 02:45
Location: Jakarta, Indonesia
Contact:

Re: How to fill a lookup using sql in a hook file

Post by dilitimor » 2017-03-18 23:15

Lieuwe,
I have the same problem, and already modified ajax_combo.php using $memberInfo['username']
It did not work, please tell me anything wrong with script below

$lookups = array(
'tabel_1' => array(
'isi_2' => array(
'parent_table' => 'tabel_2',
'parent_pk_field' => 'id_2',
'parent_caption' => 'IF(CHAR_LENGTH(`tabel_2`.`id_2`) || CHAR_LENGTH(`tabel_2`.`isi_23`), CONCAT_WS(\'\', `tabel_2`.`id_2`, \'.\', `tabel_2`.`isi_23`), \'\')',
'parent_from' => '`tabel_2` ',
'filterers' => array(),
'custom_query' => 'SELECT `tabel_2`.`id_2`, IF(CHAR_LENGTH(`tabel_2`.`id_2`) || CHAR_LENGTH(`tabel_2`.`isi_23`), CONCAT_WS(\'\', `tabel_2`.`id_2`, \'.\', `tabel_2`.`isi_23`), \'\') FROM `tabel_2` WHERE 1=1 AND `tabel_2`.`isi_23` = \'Ya\' AND `tabel_2`.`isi_nama` = \''.$memberInfo['username'].'\' ORDER BY 2',
'inherit_permissions' => false,
'list_type' => 0,
'not_null' => false
)
),

thank you in advance

dilitimor

Post Reply