Lookup field, advanced SQL edit, memberInfo
Lookup field, advanced SQL edit, memberInfo
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
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
Re: Lookup field, advanced SQL edit, memberInfo
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.
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.
Re: Lookup field, advanced SQL edit, memberInfo
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?
I wonder if it is possible to put in some sort of pattern and do preg_replace in a hook - any thoughts?
Re: Lookup field, advanced SQL edit, memberInfo
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
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
Re: Lookup field, advanced SQL edit, memberInfo
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
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.
Re: Lookup field, advanced SQL edit, memberInfo
Thank you, I look forward to that
Re: Lookup field, advanced SQL edit, memberInfo
Hi Ahmed
Have you scheduled this development into your road map yet - expected date?
Very best,
James
Have you scheduled this development into your road map yet - expected date?
Very best,
James
Re: Lookup field, advanced SQL edit, memberInfo
Yes, it's planned now for AppGini 5.80, hopefully before the end of April.
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.
Re: Lookup field, advanced SQL edit, memberInfo
Class, this will Add significantly to AppGini, which is already powerful. TIA
Re: Lookup field, advanced SQL edit, memberInfo
May I ask whether the placeholder %%memberID%% in the advanced lookup query is workable?
Is there any other type of placeholders?
Is there any other type of placeholders?
Code: Select all
SELECT ... WHERE `addresses`.`Username`=%%memberID%% ORDER BY 2
Re: Lookup field, advanced SQL edit, memberInfo
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.
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.
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.
Re: Lookup field, advanced SQL edit, memberInfo
That's ok, I appreciate the feedback.
Re: Lookup field, advanced SQL edit, memberInfo
Hi Ahmed, did you ever get around to this? It keeps coming around for me. James
Re: Lookup field, advanced SQL edit, memberInfo
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
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