Linking a field to the users Table

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
jlarmarange
Posts: 15
Joined: 2019-02-21 19:03

Linking a field to the users Table

Post by jlarmarange » 2020-02-24 09:54

Hi,

I would like to link a table to the users table, like a look-up field.

I didn't find an easy way to do that except by recreating the users table into AppGini, which is clearly not good practice.

Would it be possible to be able to select the users table or the users_groups table as the parent table in a look-up field?

Best regards

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 964
Joined: 2018-04-01 10:12

Re: Linking a field to the users Table

Post by pböttcher » 2020-02-24 21:27

Hi,

you may try to create a view of the membership_users with the field you need in your database. Then create a table in AppGini with the same fields.

The important part is that you add a unique id to your view. One option could be that you use the memberID (which is not numeric) and convert it to a numeric value. You can try conv(hex(memberID),16,10).

Now you should be able to reference that table in any lookup as you want (not that you need to calculate the length of your ID field according to your usernames).

e.g.

create view v_users as select conv(hex(`membership_users`.`memberID`),16,10) as id, `memberID` as username from membership_users

Create a table in AppGini as

Tablename = v_users
Field1: id as Integer (or maybe BIG INT)
Field2: username

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 557
Joined: 2019-05-21 22:42
Contact:

Re: Linking a field to the users Table

Post by onoehring » 2020-03-23 09:56

Hi pbötcher,

as always from you a marvelous solution. Thanks for adding the numeric modifier. I wanted to add though, that as username is unique, this is not needed and the lookup can directly take the username (instead of the 'id').

@jlarmarange I agree totally with pbötcher that you simply create a view in the database. You can also add a "pseudo table" to your application after creating the view which is named as the view and has the same fields. Then you can easily add this "pseudo" table as record source for your lookup field. I have described this procedure in the docs of my field-permission extension ( viewtopic.php?f=4&t=3308 )

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

jlarmarange
Posts: 15
Joined: 2019-02-21 19:03

Re: Linking a field to the users Table

Post by jlarmarange » 2020-03-23 10:25

Thanks for the tips. They are very useful.

However, it seems that it requires a manual intervention on the SQL database. Somehow, it would have been better to rely on the generic installation/update system within Appgini in order to simplify the maintenance, in particular when there are several instances of the generated app.

Best regards

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 557
Joined: 2019-05-21 22:42
Contact:

Re: Linking a field to the users Table

Post by onoehring » 2020-03-23 10:30

Hi,

I agree. If you want to make things easier, just implement the SQL code from pbötcher into the /hooks/tablename _init function. This would allow to create the view "automatically" once the table is accessed and thus would work for multiple instances of your application without interfering directly with the database using phpmyadmin/adminer.

Note: You might want to remove the view before creating it new or - ignore the sql error if it already exists.

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

jlarmarange
Posts: 15
Joined: 2019-02-21 19:03

Re: Linking a field to the users Table

Post by jlarmarange » 2020-03-23 12:59

Thanks for the feedback

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 964
Joined: 2018-04-01 10:12

Re: Linking a field to the users Table

Post by pböttcher » 2020-03-23 19:33

Hi,

just to clarify,

you can add the code to create the view in the ini function as Olaf stated, make sure that you drop the existing table first if you created the table in AppGini for reference. Otherwise you cannot create the view. Aslo I think the ID for the membership view is needed as your lookup need an integer an cannot cope with the memberid (which indeed is unique, but not usable).

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 557
Joined: 2019-05-21 22:42
Contact:

Re: Linking a field to the users Table

Post by onoehring » 2020-03-24 07:57

Hi pbötcher,

I am not sure that is correct. For me it seems to work just fine:
ex20200324_02.png
ex20200324_02.png (4.69 KiB) Viewed 2148 times
Which is defined like this:
ex20200324_01.png
ex20200324_01.png (53.14 KiB) Viewed 2148 times
PS: I noted in the second image, left side the contraint which is set to the field ID_Usersettings: It should probably be set to CASCADE as the username might change.

Nevertheless: jlarmarange is right - it would be nice to have direct access to the internal tables (in lookup fields).

Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 964
Joined: 2018-04-01 10:12

Re: Linking a field to the users Table

Post by pböttcher » 2020-03-24 20:02

Hi Olaf,

this is correct, but then you should mention that you change the default value of integer for the lookup field. If you modify by any chance the lookup it will reset the type to integer and you need to be aware that you change it back.

Post Reply