User view maintenance - users may have multiple cases

[Paul]

Since each 'Contact Name' (or client) might have more than one case, it makes it tricky to maintain which records (cases) are viewed and 'Printed' by each user. I have that figured out by copying and editing lines from the Browse SQL below. But going back and forth between tables and columns to find the information is a pain.
To help maintain which users see which records, I have made 'Full Name' (from the Users Login record) the same value as 'Contact Name' on each record. They are one and the same 'entity'.
I did a join query to display the user_id on the EDIT form which corresponds with 'Contact Name' and 'Full Name' for each record (case). In other words, the same Contact Name may appear on more than one record (case) so I need that user_id to help modify my Browse form SQL. But that resulting user_id needs to be displayed on the Browse form as well to allow for easier maintenance of the main Browse SQL query. The browse form already shows the case number and user name/contact name, I just need to add the user ID that I got from the join query I did. How do I accomplish that?
Did I describe the situation clearly enough?

BROWSE form SQL:

Code: Select all

SELECT * FROM cases 
WHERE ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
    OR ('#USER_ID#' = '68d883538d9b830' AND pk_inv_case_no = '13')
    OR ('#USER_ID#' = '68d885c9ebe0dee' AND pk_inv_case_no = '14')
    OR ('#USER_ID#' NOT IN ('68d858650166554','68d883538d9b830','68d885c9ebe0dee'));

EDIT form SQL:

Code: Select all

SELECT
  zzzzsys_user.zzzzsys_user_id
FROM
  cases
  INNER JOIN zzzzsys_user ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE
  cases.pk_inv_case_no = '#RECORD_ID#';

[kev1n]

Have you tried something like this?

Code: Select all

SELECT
  *
FROM
  cases
  INNER JOIN zzzzsys_user 
    ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE
  ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
   OR ('#USER_ID#' = '68d883538d9b830' AND pk_inv_case_no = '13')
   OR ('#USER_ID#' = '68d885c9ebe0dee' AND pk_inv_case_no = '14')
   OR ('#USER_ID#' NOT IN ('68d858650166554','68d883538d9b830','68d885c9ebe0dee'));

[Paul]

When globeadmin is logged in with my original SQL in Browse form I get this:

sql1.PNG

When globeadmin is logged in with your SQL in Browse form I get this:

sql2.PNG

So globeadmin is not seeing all of the records.

This is what I want:

user-id-on-browse-form.png

Each record shows that user id which is the result of my inner join query. But I want that result on the browse form as well.

Record.PNG

[kev1n]

I usually add OR '#GLOBAL_ACCESS#' = '1' so that the globedmin can see everything.

[Paul]

Ok, thank you, that was helpful. Now globeadmin sees all the records while the other users see only their case(s).
Now I just need to show the userid (from my inner join) to appear on the browse form as shown above.

[kev1n]

Just include the user id in the Browse Columns?

[Paul]

The 'userid' object is a display object (this is the object that runs a join query to return the proper user_id). When I added that to the list of columns for the browse form, I get no results at all. The result of this inner join

Code: Select all

SELECT
  zzzzsys_user.zzzzsys_user_id
FROM
  cases
  INNER JOIN zzzzsys_user ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE
  cases.pk_inv_case_no = '#RECORD_ID#';

is what I want in the Browse form.

[kev1n]

Can you share a screen shot of your Form Properties where we can see your SQL, selected columns?

[Paul]

Capture.PNG

[kev1n]

I don't see the user_id in the selected columns or it's not visible on your screenshot.