Select ulink

[CharlesParker]

I hope I can ask this correctly and at teh same time promise I will use it to learn to code through your useful advice....

so heres the deal I am filtering most of my grids by "company_id"
I am using the ulink field as my company id (:whatulink)
so in my filter for grids I put
company_id =session.whatulink
this filters all grids by company id's or phone numbers - so an owner, mgr, secretary,etc all see the records (per groups permissions)


Next I realized two companies can have the same customers and I want the company customers to be able to interact with theri own account.
I want to create a way for the logged in user (or company customer) to select from a set of company_id's assigned to him or her. IN other words the same user can have two different company_id's on the idea that a custoemr can be a customer of more than one company.

This would be to allow a user to be a customer of more than one company_id
I filter users by email (userid)


I hope I have explained myself and welcome your thoughts - bear in mind I am a complete noob but willing to dedicate hours to learn to write 1 line of code and understand it....

Thanks!

[CharlesParker]

Re: Select ulink

maybe I can set this during login? Customer chooses their company_id by entering the code? I do not want customers using the app to shop other companies or even having another competitor as an option to select - only by entering a company code....

[christappan]

Re: Select ulink

Is there a reason you're setting the ulink to be the company ID rather than the customer her/himself? I'm not sure how the customer is getting assigned to the various companies, but there would be numerous ways to do this if the ulink was for the customer. A couple to start:

1) a column in the customer table with company_IDs (e.g. 34,23,43). During the login process use Xbasic to set a company_IDs session variable (e.g. session.company_IDs=34,23,43) and then your grid query remains the same but it's WHERE Company_ID IN (:company_IDs).

2) the more normalized relational db way would be to have a relationship table (e.g. cust_comp) that stores just the active relationships between User_ID and Company_ID in rows. Then in your queries you JOIN to this table and have a WHERE cust_comp.User_ID = :User_ID. This is slightly more difficult to write queries because you always have to JOIN but more fail safe for data in the long run. Read up on relational databases a bit to better understand.

[CharlesParker]

Re: Select ulink

I have not done any JOINS but would you say it is easier to work with those in a grid or a dialog?

I already created the relationship table as I saw the need during the design phase of the database, I just have not setup any database rules yet for cascades or referential integrity. Everything going on right now is a direct write to a field, because I am very much still in design phase. I figure once I determine what all of the tables are can I truly start setting up rules. so yeah that relationship table exists, its just not referenced by any forms or rules- thanks for your input on it I like your idea

[christappan]

Re: Select ulink

Using Joins is really just part of displaying things from various tables in one grid or dialog. Using a basic example, even though an invoice is tied to a customer, you don't want to save the customer's name in the invoice table (because she might get married and change her name) so you save her customer ID (from the customers table) in the table. But you also want to look at an invoice and see her name, not her ID. Hence joins as a fairly common, normal part of working with databases.

1. Under Grid > Query, select SQL Select Statement and click Edit SQL in Query Builder
2. Click Add Table, select a table (e.g. invoices) from your database and click OK. Alpha will put "SELECT * FROM invoices" in the query window
3. Click Add Table again, select a table (e.g. customers) and click Next. Under Linking Fields, select the fields that the relationship is built on (e.g. the customer ID from the customers table and the customer ID from the invoices table). You can read about and experiment with different Join types by Googling or here in this dialog in Alpha Five, but click Finish. Alpha will now have put "SELECT * FROM invoices INNER JOIN customers ON customers.customer_ID = invoices.customer_ID.
4. Click OK.
5. If the grid is read-only, you can check the Read Only box. If not, click on Select Primary Key(s). You'll want to select the Primary Key for each table, but if a table is read-only in a grid, select read-only. So in our example, assuming that you just want to display the customers' names on the invoices grid but you're not actually editing the data in the customers table in this grid, you could mark it read-only.

I think that should get you going. When I started with Alpha Five a few years ago I didn't know the first thing about any of it, so be encouraged that with the genies and this forum and Google, things like SQL joins will begin to seem straightforward and natural in time.

[Steve Wood]

Re: Select ulink

Hate to toss this in, but you should discontinue using ulink as part of your login/security model. It will not be supported down the line (per Alpha) as they get closer to IIS integration. And if you do continue to use ulink, the value definitely should not have any association with the Company. The ulink is a value that identifies the logged in user, not the company they belong to. Look on this forum for some of the posts by JR Noe who is using my Framework when it supported ulink. He posts about how to capture the company id (coid) per logged in user, and then filter records by coid. Also search for "pUser.userid" and "web_users" (as in a table name) for posts on the preferred method for identifying the logged in user, not using ulink.

My older Framework uses ulink (which is still perfectly viable for now) but the latest one does not use unlink.

[CharlesParker]

Re: Select ulink

Actually in my case the ulink does id the company_id,
but
, I do hate that you just tossed that in...I thought I was doing so well.

All I really need to replace my current system though is to assign a session variable (globally) to the company_id field in my security tables.
I would really love some thoughts on best way to do that.
I assigned the ulink variable to "whatulink" so all I am doing is filtering all my tables by session.whatulink
since I do not know how to do the former - the latter works everytime

I was wondering about the posts concerning a filter failing to show all records but I cannot see how my system fails because either the ulink value is correct or it is not and does NOT show anything. Maybe one day you could look at what I am doing and give me some tips on improving the thing....

[christappan]

Re: Select ulink

Steve are you saying not to use the "Enable external user identifier field" property in the Security settings? I'm fine to rename and set session variables through XBasic redirect pages but I don't know how I'd do it if it didn't start there.

[Steve Wood]

Re: Select ulink

Re: ulink. Not using ulink is only a recommendation going forward. Using ulink is "discouraged" (says that somewhere) in V12. But it still works fine in v12, so I am only saying "recommended". The other name for ulink is "Enable external user identifier field".

The preferred method v11 and forward is:

dim pUser as p
dim pUser.userid as c
a5ws_logged_in_user_values(pUser) 'gets logged in user data to pointer 'pUser'.
if variable_exists("pUser.userid") '
...
end if

Then to get the company id by userid (if applicable):
session.coid = sql_lookup("::name::conn","web_users","userid='"+pUser.userid+"'","companyid")

Or their full name by userid (SQL code):
session.fullname = sql_lookup("::name::conn","web_users","userid='"+pUser.userid+"'","concatenate(alltrim(fname),' ',lname)")

The reason it is not recommended has to do with IIS security architecture. So the ability to use ulink may go away as Alpha moves towards IIS. Don't know other than Alpha has specifically stated that ulink is not recommended V11 forward.

[christappan]

Re: Select ulink

Thanks Steve.

On an side note, wouldn't your "if variable_exists("pUser.userid")" always evaluate to true since you dimmed it above?

[Steve Wood]

Re: Select ulink

yep, validate not necessary.

[CharlesParker]

Re: Select ulink

Thanks Steve for weighing in...I am without recourse as I am a point and click developer. I just do not have much exp with variables and how to set this up otherwise. I stared at my own app trying to remember how in the world I got my ulink to have snot to do with my company-id but its actually very simple.
I set the companyid field to hidden and make its default value session.whatulink when a user enters a record.
userid is also hidden and set to a5ws.getcurrentuser() and I have that set as email.
seemed like a great idea to me, lol

then I just filter everything by the ulink variable "session.whatulink" with whatulink being the external userid field in the security settings.
I hope this will help others either do what I am doing or learn a better way....

I woudl like your thoughts as someone who seems to understand on a much higher level what coming down the pipe and why.
any step by steps for making me more forward compliant?

Also other than me I have no users - I am in dev right now - but so far this is looking great to me! I will work to get it to be a useful and secure app.
Thanks for your input steve and chris!

[CharlesParker]

Re: Select ulink

What I meant to say was my filter is "company_id = session.whatulink" in all forms. Theres no record showing unless you have a value there, and the value gets set by a record entry which means that only soemoen with the same company_id can see the record. I can set a user, mgr, owner or customer (groups) permission from there to filter the visibility of grids and dialogs.
My problem really surface when I realized a customer could have more than one company_id...but I am working on that which is why I am really wanting to understand whats going on in your setups of security systems + you said the ULINK field may be going away one day...

[CharlesParker]

Re: Select ulink

WOW - so I actually for the first time filtered a grid without the ulink session variable!

I have more questions as usual but hopefully this can be answered by many of you as an easy one.

I am using the following code

dim pUser as p
dim pUser.userid as c
a5ws_logged_in_user_values(pUser) 
'gets logged in user data to pointer 'pUser'.

session.coid = sql_lookup("::name::Connection1","users","userid='"+pUser.userid+"'","company_id")

In the OnGridInitialize.

My current question is to ask what values are in
a5ws_logged_in_user_values() ???
does this allow me to reference any field in my users table? Or is it just the ones that are stock with Alpha5's user table?

Finally, could someone break down that sql_lookup for me ?
I see the connection and the fact that we are looking at the users table.
I also think I am saying that userid = the userid from the logged in users "a5ws_logged_in_user_values()" with one of them being the field userid

but why the comma and company_id after that? the extra apostrophe, etc?

I assume that in the end we are saying that coid = the company_id from the matching (userid) record in the users table.


am I right about all of that?
Just learning all of this is somewhat confusing even if I get it right I am still not sure of myself!
I feel like I am learning this all backwards - like asking asking how to spell a word and not learning the alphabet

[Steve Wood]

Re: Select ulink

You can get a complete list of what a5ws_logged_in_user_values returns in the Help document. It returns values from Alpha websecurity table NOT your local user table, so you cannot get the First Name, etc.

The single quotes are there because that is a character field and SQL expects the value to be quoted.

You are correct about the other things you proposed.