AppGini Community Forums

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

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

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

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

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

Thanks for the feedback

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

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

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.