set membership_users as lookup parent table

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
thsowers
Posts: 4
Joined: 2013-12-28 22:50

set membership_users as lookup parent table

Post by thsowers » 2013-12-28 22:53

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!

thsowers
Posts: 4
Joined: 2013-12-28 22:50

Re: set membership_users as lookup parent table

Post by thsowers » 2013-12-28 23:13

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

User avatar
dilitimor
Veteran Member
Posts: 36
Joined: 2013-01-10 02:45
Location: Jakarta, Indonesia
Contact:

Re: set membership_users as lookup parent table

Post by dilitimor » 2013-12-30 08:42

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

thsowers
Posts: 4
Joined: 2013-12-28 22:50

Re: set membership_users as lookup parent table

Post by thsowers » 2013-12-30 15:55

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

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: set membership_users as lookup parent table

Post by KSan » 2013-12-31 09:58

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.

Bertv
Veteran Member
Posts: 65
Joined: 2013-12-11 15:59

Re: set membership_users as lookup parent table

Post by Bertv » 2013-12-31 11:47

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.
Bert
I am using Appgini 5.75

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: set membership_users as lookup parent table

Post by KSan » 2013-12-31 22:34

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

artemisia
Veteran Member
Posts: 59
Joined: 2013-10-01 15:50

Re: set membership_users as lookup parent table

Post by artemisia » 2014-01-02 20:03

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.

Bertv
Veteran Member
Posts: 65
Joined: 2013-12-11 15:59

Re: set membership_users as lookup parent table

Post by Bertv » 2014-01-03 11:11

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.
Bert
I am using Appgini 5.75

artemisia
Veteran Member
Posts: 59
Joined: 2013-10-01 15:50

Re: set membership_users as lookup parent table

Post by artemisia » 2014-01-03 18:33

yes, that fixed it, works for me now.

thsowers
Posts: 4
Joined: 2013-12-28 22:50

Re: set membership_users as lookup parent table

Post by thsowers » 2014-01-09 18:52

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!

mcvaidas
Posts: 26
Joined: 2019-04-14 17:45

Re: set membership_users as lookup parent table

Post by mcvaidas » 2019-10-05 12:25

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?

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

Re: set membership_users as lookup parent table

Post by pbottcher » 2019-10-07 07:05

Yes, currently you cannot use the membership_users table directly in a lookup.
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
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: set membership_users as lookup parent table

Post by onoehring » 2019-10-10 05:46

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).

Code: Select all

CREATE VIEW view_membership_users AS select * from 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.
ec202.png
ec202.png (7.54 KiB) Viewed 72237 times
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:

Code: Select all

select * from (select * from membership_users) some_name;
PS: SQL not tested.

Olaf

Post Reply