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!
set membership_users as lookup parent table
Re: set membership_users as lookup parent table
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
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
Re: set membership_users as lookup parent table
You can use "Automatic Value" to set member name or ID automatically insert into field
Re: set membership_users as lookup parent table
Unfortunately, this will not work as I need to be able to see the list of members and then select one from it
Re: set membership_users as lookup parent table
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.
Re: set membership_users as lookup parent table
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.
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
I am using Appgini 5.75
Re: set membership_users as lookup parent table
Super! This is such a good idea. Saves the need to upkeep yet another table with redundant data.
Re: set membership_users as lookup parent table
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?
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.
Re: set membership_users as lookup parent table
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.
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
I am using Appgini 5.75
Re: set membership_users as lookup parent table
yes, that fixed it, works for me now.
Re: set membership_users as lookup parent table
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!
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!
Re: set membership_users as lookup parent table
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?
only artenatyve duplicate tables?
Re: set membership_users as lookup parent table
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.
Re: set membership_users as lookup parent table
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
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;
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:
Code: Select all
select * from (select * from membership_users) some_name;
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view