AppGini Community Forums

Hello!

I just purchased this product the other day and I really love it, but i've run into a bit of a problem. I have a table called 'sessions' and need one of the fields to be a lookup value of the membership_user 'MemberID'. How would I go about doing this? Thanks!

Update:

I am aware that I can just change the MySQL connect statement by clicking on "Advanced", this will list the users as a dropdown, but will not save the changes I make

You can use "Automatic Value" to set member name or ID automatically insert into field

Unfortunately, this will not work as I need to be able to see the list of members and then select one from it

I think you can achieve this by creating a table inside AppGini to contain duplicate records matching your membership_user table. You can write a hook that periodically updates this AppGini table (ie SQL Update ). The AppGini table then becomes your lookup parent.

Instead of creating a new table with duplicated data you can
1) create in MYSQL a view on the membership_members table:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `members_vw`
AS select
`membership_users`.`memberID` AS `memberID`
,`membership_users`.`custom1` AS `name`
from `membership_users`

2) create in AppGini an table with the same name as the view: members_vw, with the same fields: member_id and name
and use this table as lookup.

Super! This is such a good idea. Saves the need to upkeep yet another table with redundant data.

I tried this method (below) in AppGini v5.22, but no go. The dropdown on my form shows no names at all, but it's linked to the new view set up as suggested.
Has anyone else gotten this to work?

Bertv wrote:Instead of creating a new table with duplicated data you can
1) create in MYSQL a view on the membership_members table:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `members_vw`
AS select
`membership_users`.`memberID` AS `memberID`
,`membership_users`.`custom1` AS `name`
from `membership_users`

2) create in AppGini an table with the same name as the view: members_vw, with the same fields: member_id and name
and use this table as lookup.

I did not realize me that the parent table must have a integer as pk field. I have add a id field as pk to the view:

CREATE or REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `members_vw` AS select
(1 + (select count(0) from `membership_users` `t` where (`t`.`memberID` < `t2`.`memberID`))) AS `id`
,`t2`.`memberID` AS `memberID`
,`t2`.`custom1` AS `name`
from `membership_users` `t2`

Add the new id-field as primary key to the table members_vw in AppGini.

Now you can use is as lookup. I have tested it, and it works fine.

yes, that fixed it, works for me now.

Hello, I just can't get your SQL statement to work

I recieved an error about not having super privilages, so I granted them...again...and again..and flushed and granted them again, but I still get an error message. Is there a way to do this without super privilages?

Thanks!

hello. i dont want to create new post. is in new versions stil no access to make lookup from membership_users?
only artenatyve duplicate tables?

Yes, currently you cannot use the membership_users table directly in a lookup.

Hi,

you can do this like Bertv suggested.
Create a view in your database that pulls everything you need from whatever membership table you need (like in the image: membership_users). Then, create a "table" (as seen in the image) in AppGini.
Now you can use your "table" view_membership_users in a lookup.
Alternative / If you actually can not create a view try:
create a table with some_name an the same fields as in the members table you need
Then create your lookup and set an advanced sql: PS: SQL not tested.

Olaf