Limit dropdown box by conditions in a table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Limit dropdown box by conditions in a table

Post by rpierce » 2022-06-06 15:20

Hi,

This is probably simple, I have two tables, Project and Notify I want to limit a lookup drop down menu in the "Notify" table to only include "Active " projects from the "Project" table. I have a field in the project table named "active" that is set as a checkbox. When I edit the query for the "Project" lookup menu in the "Notify" table using the the ADVANCED button in the Lookup field dialog in AppGini it doesn't work. Most likely I'm using some wrong syntax but can't find what I'm doing wrong.

I'm simply adding a WHERE clause to the default query language.
appgini query.JPG
appgini query.JPG (45.78 KiB) Viewed 1235 times

The results of the above query are an error message:
no matches found.JPG
no matches found.JPG (22.4 KiB) Viewed 1235 times
When I run the very same query on my database in myphpadmin I get the desired results. I just need to know why it doesn't work on my AppGini app.
Attachments
myphpadmin.JPG
myphpadmin.JPG (75.8 KiB) Viewed 1235 times

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

Re: Limit dropdown box by conditions in a table

Post by jsetzer » 2022-06-06 16:21

Could be a security/permission issue.

I'm not 100% sure if AppGini code appends joins to membership tables (or filters out records after fetching from DB) to the advanced query SQL for removing lookup records, the current user is not allowed to see. That would make sense from security point of view. And that could explain why the pure SQL query returns different results than the modified database query being executed when fetching lookup items.

I'm not able to check the code right now. I hope someone else can do.

Things you may try:

* Did you check with admin account?
* if yes: Does admin group have View-Permissions on that table?
* in network tab, find out the name of the script being called when opening dropdown. Then check that php script.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Limit dropdown box by conditions in a table

Post by rpierce » 2022-06-06 21:11

Hi Jan,

Yes I am logged in as admin with full permissions.

I am unfamiliar with the Network tab you mentioned.

Ray

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

Re: Limit dropdown box by conditions in a table

Post by jsetzer » 2022-06-07 06:08

F12 in your browser opens developer tools. Then open tab "Network". Reload the page and you will see all files the browser loads. Open the lookup and you will see that the browser requests data from the server. That last line in the list of network tab will name the serverside php file, the payload sent to the server and the response received.

Anyway, if you are logged in as admin and if admin group has all permissions, your issue will probably not be permission related.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Limit dropdown box by conditions in a table

Post by rpierce » 2022-06-07 18:46

I have tried using this tutorial and according to it, my query should work. But nothing. It seems like something that should be really simple.

https://bigprof.com/appgini/screencasts ... -sql-query

sclifton
Posts: 8
Joined: 2020-10-21 10:31

Re: Limit dropdown box by conditions in a table

Post by sclifton » 2022-06-12 18:55

Couple of thoughts:
1. What is the datatype of the active field in the Project table? If an INT, should it not be compared with an integer 1 value not a string?
2. It seems that "active" became a reserved word in MySQL for a time - you might want to check your specific MySQL instance.

Hope that helps with fault-finding.

Post Reply