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
Linking a field to the users Table
Re: Linking a field to the users Table
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
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
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: Linking a field to the users Table
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
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:
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
-
- Posts: 16
- Joined: 2019-02-21 19:03
Re: Linking a field to the users Table
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
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
Re: Linking a field to the users Table
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
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:
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
-
- Posts: 16
- Joined: 2019-02-21 19:03
Re: Linking a field to the users Table
Thanks for the feedback
Re: Linking a field to the users Table
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).
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).
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: Linking a field to the users Table
Hi pbötcher,
I am not sure that is correct. For me it seems to work just fine: Which is defined like this: 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
I am not sure that is correct. For me it seems to work just fine: Which is defined like this: 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:
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
Re: Linking a field to the users Table
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.
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.
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.