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
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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

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

Re: Lookup field, advanced SQL edit, memberInfo

Post by pbottcher » 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.
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.

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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: 1281
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: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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: 1281
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: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

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

sacgtdev
Veteran Member
Posts: 75
Joined: 2020-06-10 11:14

Re: Lookup field, advanced SQL edit, memberInfo

Post by sacgtdev » 2021-07-22 07:33

May I ask whether the placeholder %%memberID%% in the advanced lookup query is workable?

Is there any other type of placeholders?

Code: Select all

 SELECT ... WHERE `addresses`.`Username`=%%memberID%% ORDER BY 2

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Lookup field, advanced SQL edit, memberInfo

Post by udennison » 2021-09-15 23:01

a.gneady wrote:
2019-03-22 13:58
Yes, it's planned now for AppGini 5.80, hopefully before the end of April.
Hey Ahmed, was this feature released? Can you advise further on any posts, tweets or blogs with this?

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

Re: Lookup field, advanced SQL edit, memberInfo

Post by a.gneady » 2021-09-18 10:47

Unfortunately not :/
I know it's a very long time .. but the feature didn't make it into 5.80 at the time because while experimenting with it, it caused several issues that needed more planning to carefully implement. I've taken note of this now and will work in it in upcoming releases. Sorry for that.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

udennison
Posts: 18
Joined: 2019-10-12 17:34
Location: Trinidad & Tobago

Re: Lookup field, advanced SQL edit, memberInfo

Post by udennison » 2021-09-20 14:25

That's ok, I appreciate the feedback.

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2023-01-08 09:16

Hi Ahmed, did you ever get around to this? It keeps coming around for me. James

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2023-01-08 09:25

Hi Ahmed, did you ever get around to this? It keeps coming around for me. My current need is as follows (1) user creates an address, but may mark it inactive, and (2) subsequently, user creates an asset and may select that address for the asset, or any other address he has created.
A costom mysql statement can be used to return active addresses, OR the default mysql with 'inherit permissions' to return the users own records. I cant seem to combine both. James

User avatar
jmcgov
Veteran Member
Posts: 79
Joined: 2018-12-19 01:31
Location: Northern Ireland

Re: Lookup field, advanced SQL edit, memberInfo

Post by jmcgov » 2023-01-17 20:38

Ahmed?

Post Reply