Lookup field, advanced SQL edit, memberInfo

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-01-06 00:33

Apologies if this has been asked, but I cannot find it
I'll pose my question, with hypothetical demo, as I'm finding it difficult to articulate, so - I have an application, as follows
- On user registration, I collect the SURNAME of new sign-up (stored in membership_users table in custom2 field AND accessible via PHP using the $memberInfo['custom'][1] reference)
- There is a table called CONTRACTS, which has a field called SURNAME
- There is a table called PROGRESS, which has a field that looks up the CONTRACTS

I want to edit the lookup query (using the advanced mySQL edit functionality), so as to only select
- records from CONTRACTS
- where SURNAME matches the custom2/$memberInfo['custom'][1] (i.e. the surname of the signed in user)

The query is not an issue - how do I reference custom2 in the query (For clarity, I can do it in hooks using PHP, i.e. $memberInfo['custom'][1])

SELECT ... WHERE `CONTRACTS`.`Surname`='???' ORDER BY 2

TIA, James

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 376
Joined: 2018-04-01 10:12

Re: Lookup field, advanced SQL edit, memberInfo

Post by pböttcher » 2019-01-06 12:56

Hi,

I don't think this is possible through the customization (advance SQL) as the memberInfo is not available at the time you need it.

User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-01-06 16:30

Thanks Pbottcher, I was afraid that might be the answer
I wonder if it is possible to put in some sort of pattern and do preg_replace in a hook - any thoughts?

User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-01-07 13:09

I have rejigged this app, in the hope of achieving the same result. In effect, I have switched the search string from the SURNAME to the MEMBERID (as it is always stored in $_SESSION['memberID], but I still have a problem that I hope may be resolvable

In the AppGing advanced mySQL editor for the field, I have entered:
SELECT ... WHERE `addresses`.`Username`="'.$_SESSION[memberID].'" ORDER BY 2
This creates a query in the AJAX_COMBO.php file
'SELECT ... WHERE `addresses`.`Username`="\'.$_SESSION[memberID].\'" ORDER BY 2'

The problem arises because the ' is escaped (\). When I manually edit the line in AJAX_COMBO.php, the script works
'SELECT ... WHERE `addresses`.`Username`="'.$_SESSION[memberID].'" ORDER BY 2'

Clearly, a manual edit in AJAX_COMBO is problematic (as it is not in a hook, etc). Does anyone know how I could write the query in the AppGini advanced mySQL editor so as I could get a working string to be created in the AJAX_COMBO.php file

Thanks in advance, James

User avatar
a.gneady
Site Admin
Posts: 992
Joined: 2012-09-27 14:46
Contact:

Re: Lookup field, advanced SQL edit, memberInfo

Post by a.gneady » 2019-01-09 14:27

The .$_SESSION[memberID]. part of the code is PHP code .. unfortunately, you can only use SQL code in the advanced query section. I'd consider implementing some kind of a placeholder to handle this case in future releases so you could for example write the query like this:

Code: Select all

 SELECT ... WHERE `addresses`.`Username`=%%memberID%% ORDER BY 2
:idea: Learn all the tips and tricks of customizing AppGini apps through our online course.

:arrow: Summary Reports plugin enables you to build powerful reports and charts inside your AppGini application through a few simple steps. See the big picture and discover trends in your data that empower you to take the right decisions confidently.

:arrow: Search Page Maker enables you to build user-friendly yet powerful search pages for your AppGini application by simply dragging and dropping the fields you want to include in search.

User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-01-09 20:38

Thank you, I look forward to that :)

User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-03-21 14:03

Hi Ahmed
Have you scheduled this development into your road map yet - expected date?
Very best,
James

User avatar
a.gneady
Site Admin
Posts: 992
Joined: 2012-09-27 14:46
Contact:

Re: Lookup field, advanced SQL edit, memberInfo

Post by a.gneady » 2019-03-22 13:58

Yes, it's planned now for AppGini 5.80, hopefully before the end of April.
:idea: Learn all the tips and tricks of customizing AppGini apps through our online course.

:arrow: Summary Reports plugin enables you to build powerful reports and charts inside your AppGini application through a few simple steps. See the big picture and discover trends in your data that empower you to take the right decisions confidently.

:arrow: Search Page Maker enables you to build user-friendly yet powerful search pages for your AppGini application by simply dragging and dropping the fields you want to include in search.

User avatar
jmcgov
Posts: 18
Joined: 2018-12-19 01:31

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2019-03-24 22:05

Class, this will Add significantly to AppGini, which is already powerful. TIA

Post Reply