Finding By Multiple

[Martin_S]

I have a database that shows data based on user permissions. There are three levels:

• System admin - access to all
• Office admin - access to all records where the team leader is based on the same office
• User - access to all records for which the user is a team member.

So, I'm struggling with filtering the data for the users. If I was doing this in SQL, I'd use an INNER JOIN to get my list of records joined by the team table, so was thinking I'd use ExecuteSQL to get the list of IDs? But what I don't get is can I use Set Field with an array?

So a query like:

SELECT C.ClientID
FROM ClientDetails C
INNER JOIN TeamMembers T ON T.ClientID = C.ClientID
WHERE T.TeamMbr = 'JOHN.SMITH'

would returm, say, 1,5,6,9.

I then have a script as follows for Office Admin, and want something similar to the script below for Users:

Is this possible?

Thanks

Martin

[Wim Decorte]

if the SQL result contains multiple values then you need to loop through those and create one request in Find mode per value found

[Martin_S]

if the SQL result contains multiple values then you need to loop through those and create one request in Find mode per value found

Thanks...

From your reply, I'm not sure if I explained myself properly - I want all the results to be my final found set, ie clients with the ID 1, 5, 6 and 9 should be displayed.

Am I wrong in thinking each find will be unique and so will end up with only the last found set? Of do I use modify last find within each loop? I'm going from a reference book, so any pointers to the relevant script steps would be helpful.

Regards

Martin

[MartieH]

take a look at 'extend found set' - you can create separate "finds" but end up with a total found set of all using this script step.

 

Martie

[LaRetta]

I should think multiple find requests would do the trick.  Script might look something like this (and I am using a global text field  called gIDs where the 'list of ids' resides - change that part to whatever you need).  You might wish to set the ExecuteSQL() result to a script variable and access it from there:

Enter Find Mode [  ]
Loop
  Exit Loop If [ Let ( $count = $count + 1 ; $count > ValueCount ( table::gIDs ) ) ]
  New Record/Request
  Set Field [ table::id ; GetValue ( table::gIDs ; $count ) ]
End Loop
#
****  End If  <-- typeo error - remove this line ***
#
Set Error Capture [ On ]
Perform Find [  ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ "No records found" ]
... do whatever if no records are found such as return to original layout
Exit Script
End If
... do whatever you wish with the found records such as switch to report layout etc.

I modified the script - remove that End If - I can't apply strikethrough within code it seems nor can I highlight my comment so it is obvious.

Edited October 15, 2013 by LaRetta

[comment]

A few pieces don't fit the puzzle here:

 

 

INNER JOIN TeamMembers T ON T.ClientID = C.ClientID

 

So one Client to many TeamMembers, right? Then how is it possible to search for 'JOHN.SMITH' as the team member and return four ClientIDs?!

 

 

OTOH, if the relationship is many-to-many (as it seems it should be) then a simple GTRR from the TeamMember record to Clients would create the required found set without any fuss.

 

 

Finally, and most importantly:

 

I'm struggling with filtering the data for the users.

 

This "struggle" should take place in the users' privilege set. Once you have defined which records a user has access to, any find will automatically omit the "forbidden" records. Even without it, all that the user will ever see of these records is a <<No Access>> label, so the issue is purely cosmetic.

[comment]

how is it possible to search for 'JOHN.SMITH' as the team member and return four ClientIDs?!

 

A: There are 4 team members named John Smith.

[Martin_S]

A few pieces don't fit the puzzle here:

 

 

 

So one Client to many TeamMembers, right? Then how is it possible to search for 'JOHN.SMITH' as the team member and return four ClientIDs?!

 

 

OTOH, if the relationship is many-to-many (as it seems it should be) then a simple GTRR from the TeamMember record to Clients would create the required found set without any fuss.

 

 

Finally, and most importantly:

 

 

This "struggle" should take place in the users' privilege set. Once you have defined which records a user has access to, any find will automatically omit the "forbidden" records. Even without it, all that the user will ever see of these records is a <<No Access>> label, so the issue is purely cosmetic.

 

 

A: There are 4 team members named John Smith.

 

Hi

 

In answer to your question, the same john.smith can be a member of multiple teams, so the query is to find each client ID for which the current user is a member of the team. FYI I used john.smith as an example, but in my real world, employee IDs are distinct and so even if there were 4 john smiths in the same team, only the one I was searching for would be queried.

 

In the client table, I store the name of the user who created the record as well as their office location, which I then use to filter the data for Office Admin (as per my earlier script example), so wanted to follow the same logic for users. I'll look at LaRetta's example code, as that looks like where I was heading.

 

I'm obviously not understanding privilege sets as much as I should, but as teams will change, i.e. memebrs will be added and removed, the idea of using the filter in script seems, to me, logical.

 

I am currently working through the safari books online course for FileMaker Pro, so hopefully will have less obvious questions when I'm done!

 

Martin

I should think multiple find requests would do the trick.  Script might look something like this (and I am using a global text field  called gIDs where the 'list of ids' resides - change that part to whatever you need).  You might wish to set the ExecuteSQL() result to a script variable and access it from there:

Enter Find Mode [  ]
Loop
  Exit Loop If [ Let ( $count = $count + 1 ; $count > ValueCount ( table::gIDs ) ) ]
  New Record/Request
  Set Field [ table::id ; GetValue ( table::gIDs ; $count ) ]
End Loop
#
****  End If  <-- typeo error - remove this line ***
#
Set Error Capture [ On ]
Perform Find [  ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ "No records found" ]
... do whatever if no records are found such as return to original layout
Exit Script
End If
... do whatever you wish with the found records such as switch to report layout etc.

I modified the script - remove that End If - I can't apply strikethrough within code it seems nor can I highlight my comment so it is obvious.

Thanks I'll look at this, appreciate your time in the example.

Martin

[comment]

I think you are describing something like this:



With these relationships in place, you can perform GTRR from a record in Staff to Clients and establish a found set of all clients that the staff member is currently serving.

The name of the user who created the client record is not helpful here because, as you say, team members come and go. The key field (literally) is the TeamID field in Clients (which I believe you call Location).

 

I'm obviously not understanding privilege sets as much as I should, but as teams will change, i.e. memebrs will be added and removed, the idea of using the filter in script seems, to me, logical.

 

It may seem logical, but it is anything but secure. All the other clients - the clients to which the staff member should have no access - are only a find away.

 

Keep in mind that just like your script, a privilege set too can use relationships and calculations to determine the current privileges of the user.

[Martin_S]

OK, thanks. I'm reading your response and think maybe I haven't explained the setup very well, but also can't help but wonder how best to deal with security. If I explain the system in better detail, then that may help you understand what I am trying to achieve.

 

The tool is, for first release, a prototype, using FM 12 Advanced Pro to create what we are calling Client Manager.

The tool will ultimately be a multi-user system, with, as already mentioned, three different types of user (and multiples of each user type):

• System admin - access to all records
• Office admin - access to all records where the team leader is based on the same office
• User - access to all records for which the user is a team member

So the system is broken down into two distinct parts - clients and users. Clients have team members associated to them, as well as external contacts. Each client has, say, 5 independent sets of data assigned to them, which have multiple entries, like team members and external contacts. Clients also have schemes assigned to them. Each scheme has a set of data, again, multiple records, assigned to it.

When a new client is added, this will add a new record with an associated office location, i.e. London, and therefore any London office admin will immediately be able to see that record. If a user is added as a team member for that client, then they will also see that record. The filtering for office admins is based on a field which is auto-populated when a user is selected.

This system is only designed for internal usage. Users of the system ultimately could be both a team member and an office administrator, though I suspect it would be better to exclude office admins when adding team members to simplify permissions.

 

Maybe that helps explain what I'm trying to achieve and why I'm having the problems i'm having. Every time I ask a question, it makes me think I've not set this up in the best way!

 

Would a full Relationship diagram help explain where I am now?

 

I though that writing it to filter out records users were not able to see would be the best way, but from your response, maybe that isn't the case? Whilst i'm still working through the online course, it doesn't cover  security at all, which is the vital part of the system, as the team who we are writing this for want to know that users will only see the data they are expected to see.

 

Thanks again, I appreciate your time and patience!

 

Martin

[comment]

Would a full Relationship diagram help explain where I am now?

 

 

An ERD would be much better. I have already attached a sketch of my understanding of the situation; I now see that it needs to be amended to something like this:

(I have left out the contacts and schemes as I think they are irrelevant to the issue of user privileges).

 

If this is not a correct representation of your entities and their relationships, you'll need to supply your own diagram (frankly, I don't see how you could even begin without having one).

We must have some common reference before us in order to continue this discussion, because some of the things you say contradict each other and I have no way to determine which are true and which need pointing out as false .

 

[Martin_S]

An ERD would be much better. I have already attached a sketch of my understanding of the situation; I now see that it needs to amended to something like this:

teams.png

(I have left out the contacts and schemes as I think they are irrelevant to the issue of user privileges).

 

If this is not a correct representation of your entities and their relationships, you'll need to supply your own diagram (frankly, I don't see how you could even begin without having one).

We must have some common reference before us in order to continue this discussion, because some of the things you say contradict each other and I have no way to identify which are true and which need pointing out as false .

 

OK, thanks. Can I confirm that your diagram is based on user permissions? Users can be team members, and i don't think offices are optional as a location is needed for office admins.

And if I don't know how to create an ERD? This is my problem - I have been put under pressure to produce this with no prior experience of FileMaker, and in a set time. Whilst I partly understand the idea of the tool, I am working on a design specification supplied, and having to follow the logic implied. And apologies if I have contradicted myself - I am trying to provide sufficient info without going into detail, and maybe I have looked at this too long to see the wood from the trees.

I think I am going to have to ask for the company to pay for someone to come in to help me, as I am taking one step forward and three back each time I try and progress this, even with your help.

Thanks

Martin

[comment]

Can I confirm that your diagram is based on user permissions?

No. User permissions will come on top of that. The ERD tells us what entities your solution will be tracking and what are the relationships between them.

 

 

i don't think offices are optional as a location is needed for office admins

Offices are not optional - but the (direct) relationship between client and office is optional, because it is redundant: when a client is assigned to a team, they are automatically associated with the team's office via the team-to-office relationship.

 

 

And if I don't know how to create an ERD? This is my problem - I have been put under pressure to produce this with no prior experience of FileMaker, and in a set time. Whilst I partly understand the idea of the tool, I am working on a design specification supplied, and having to follow the logic implied.

An ERD is a general database design tool - it is in no way specific to Filemaker. I am not sure in what format your design specifications were supplied; I do know that without having an ERD in front of me, I too would be "taking one step forward and three back each time I try and progress"...

 

On a slightly lighter note: here's something you might find an interesting read:

http://fmforums.com/forum/topic/57970-does-anyone-takes-us-seriously/

[Martin_S]

No. User permissions will come on top of that. The ERD tells us what entities your solution will be tracking and what are the relationships between them.

 

OK. My relationship diagram shows the ClientID is the only key tracked through each table. I kind of hoped the Relationship diagram would clarify what I've built so far...and equally if it was going in the right or wrong direction.

Offices are not optional - but the (direct) relationship between client and office is optional, because it is redundant: when a client is assigned to a team, they are automatically associated with the team's office via the team-to-office relationship.

Ah, OK, that makes sense - although it's more a team is assigned to a client - does that make a difference?

An ERD is a general database design tool - it is in no way specific to Filemaker. I am not sure in what format your design specifications were supplied; I do know that without having an ERD in front of me, I too would be "taking one step forward and three back each time I try and progress"...

 

I looked up ERD and knew what it was, but hadn't cobnsidered making one - the task of setting this application up was passed to a (wait for it) summer student who set up a basic design, but after his week, I was then passed it on to continue - I found that I wanted to start from scratch rather than try and understand his thinking. I'm actually a C# developer by trade, and do work with SQL databases, but think we (as in myself and the developers I work with) are just bad at planning for development. The specifications are a requirements document with brief descriptions of the 'screen's' requirements and their contents.

 

One point i didn't mention is that there are also several Super Admin only tasks, such as importing data into tables and other super/office admin tasks to generate reports. For all screens, it should be possible for users to add/remove entries.

 

On a slightly lighter note: here's something you might find an interesting read:

http://fmforums.com/forum/topic/57970-does-anyone-takes-us-seriously/

 

I read that before my response.  I do totally agree, even though I'm not a summer intern, I still find it very true.

 

Thanks

Martin

[comment]

the ClientID is the only key tracked through each table

 

You see, that's exactly the kind of thing that contradicts almost everything else. Let me try and make it (even) easier for you: if you agree with all of the following statements:

1. An office has many teams; each team belongs to one office only.

2. A team takes care of many clients; each client is served by one team only;

3. A team has many employees as its members; an employee can be a member of several teams,

then you have essentially agreed to my ERD. However, if that is how things stand, it's not possible for the ClientID to be "the only key tracked through each table". There is no way you can accomplish this - not in Filemaker and not in any other relational database.

 

If someone feels the urge to point out an esoteric, normalization-breaking way of doing this - please resist.

 

I am attaching my ERD again, this time showing what the matching keys need to be for each relationship. As you can see, ClientID is not used in any of them (though it would be used in the relationship between Clients and their Contacts, for example). That is given by the three statements above and cannot be changed: a relationship between parent and child is always established through matching the parent ID.

 

 

 

To emphasize this point even further: there is no ClientID field in any of the depicted tables, other than Clients. A ClientID field makes sense only in a table that is a child of Clients - and none of these tables are.

Edited October 18, 2013 by comment

[Martin_S]

You see, that's exactly the kind of thing that contradicts almost everything else. Let me try and make it (even) easier for you: if you agree with all of the following statements:

1. An office has many teams Correct; each team belongs to one office only. Teams are not owned by an office, just associated with it BUT there are cases where one team member may work from another office;

2. A team takes care of many clients No - generally no two client teams are identical in members - there may be instances where they are the almost the same, or even identical, but this is not the norm. As you said below, users can be members of more than one team; each client is served by one team only; Correct

3. A team has many employees as its members Correct; an employee can be a member of several teams, Correct

then you have essentially agreed to my ERD. However, if that is how things stand, it's not possible for the ClientID to be "the only key tracked through each table". There is no way you can accomplish this - not in Filemaker and not in any other relational database.

I am attaching my ERD again, this time showing what the matching keys need to be for each relationship. As you can see, ClientID is not used in any of them (though it would be used in the relationship between Clients and their Contacts, for example). That is given by the three statements above and cannot be changed: a relationship between parent and child is always established through matching the parent ID.

 

teams.png

 

 

To emphasize this point even further: there is no ClientID field in any of the depicted tables, other than Clients. A ClientID field makes sense only in a table that is a child of Clients - and none of these tables are.

Looking again at your revised ERD, I can see where you are coming from with the relationships, I just guess my head was looking at it in a very different angle. So you are saying that a team member should be assigned to a team ID, and that team ID to the client? I had assigned team members to the client, so my ClientTeams table has a MemberID (auto generated) and a client ID, which is being populated when adding records via the associated portal. Currently the relationship between the clients and the team tables is the clientID, but I take it you are saying that this is NOT correct.

So are you then saying that the team members table should not have a client ID field, but the client table should hold the teamID associated with it?

Hopefully you're not as confused as I am (lol)

Martin

[comment]

1. An office has many teams Correct; each team belongs to one office only. Teams are not owned by an office, just associated with it BUT there are cases where one team member may work from another office; 2. A team takes care of many clients No - generally no two client teams are identical in members - there may be instances where they are the almost the same, or even identical, but this is not the norm. As you said below, users can be members of more than one team; each client is served by one team only; Correct

3. A team has many employees as its members Correct; an employee can be a member of several teams, Correct

 

 

We need to work on this part until we get it exactly right; I believe everything else will fall in place once that is done. So:

 

> Teams are not owned by an office, just associated with it

Not sure what's difference between "owned" and "associated" - the important part is that no team is related (directly) to more than one office. True?

 

> one team member may work from another office;

That makes no difference to the office-team relationship, does it? However, it raises a new question: is it important for us to track which office an employee belongs to? IOW, do we need to add a Offices --< Employees relationship?

 

> 2. A team takes care of many clients No - generally no two client teams are identical in members

I don't understand what you're saying here; the sentence "a team takes care of many clients" does not imply that teams are identical in members. What exactly does the "no" refer to?

[comment]

WAIT A MINUTE:

 

Are you saying that there are no "teams" as such? That a "team" is just a group of people who happened to be assigned to work with the same client, and it has no existence of its own?

[Martin_S]

^{We need to work on this part until we get it exactly right; I believe everything else will fall in place once that is done. So:}

 

^{> Teams are not owned by an office, just associated with it}

^{Not sure what's difference between "owned" and "associated" - the important part is that no team is related (directly) to more than one office. True?}

^{>> Sorry, yes, that's correct.}

 

^{> one team member may work from another office;}

^{That makes no difference to the office-team relationship, does it? However, it raises a new question: is it important for us to track which office an employee belongs to? IOW, do we need to add a Offices --< Employees relationship?}

^{>> Yes, as if that person is or becomes an Office administrator, it will help determine which ofice to show clients for.}

 

^{> 2. A team takes care of many clients No - generally no two client teams are identical in members}

^{I don't understand what you're saying here; the sentence "a team takes care of many clients" does not imply that teams are identical in members. What exactly does the "no" refer to?}

^{>> Each client has a client team, which is a collection of users. This collection can and does change on a regular basis, due to both people leaving/joining and conflicts of interest between other clients.}

 

 

^{WAIT A MINUTE:}

 

^{Are you saying that there are no "teams" as such? That a "team" is just a group of people who happened to be assigned to work with the same client, and it has no existence of its own?}

^{No, I wouldn't say that was true. A client team exists to service a client, but as stated above, the membership of a team can and does change. And be aware that a team member can also be any of the three user types. All employees are users, but some are office/super admins as appropriate. And again, adding and removing people to/from these roles goes on on a regular basis.}

^{For info, there is a one to one relationship between a client and a team, so do we need to have a team identifier?}

^{Does that help?}

^Thanks

^Martin

[comment]

there is a one to one relationship between a client and a team

 

Ah, then a team doesn't really exist on its own, does it? IOW, you don't "assign this client to the Blue team"; you assign each team member to the client individually. And if two clients end up having identical teams, that's just a coincidence.

 

If this is true, then the Teams table is indeed redundant - and several things that didn't make sense (to me) before would start to make sense, like having a ClientID field in the TeamMembers table:

 

 

 

Assuming it is true, I would like to focus on the following fragment of the RG and make two points which should hopefully conclude this thread:

Clients --< TeamMembers >-- Employees

1. Performing GTRR from Employees to Clients will establish a found set in Clients containing all and only clients whose team the current employee is a member of. No looping find is required for this. This should answer your original question.

2. For the second point, we need to assume there is an AccountName field in Employees, containing the employee's login name. Now, if the users' privilege set allows access to records in the Clients table only when =

not IsEmpty ( FilterValues ( Get ( AccountName ) ; List ( Employees::AccountName ) ) )

then each employee will have access only to the clients whose team he/she is a member of. This should answer the real question behind your original question.

 

[Martin_S]

Ah, then a team doesn't really exist on its own, does it? IOW, you don't "assign this client to the Blue team"; you assign each team member to the client individually. And if two clients end up having identical teams, that's just a coincidence.

>> Yes, exactly.

 

If this is true, then the Teams table is indeed redundant - and several things that didn't make sense (to me) before would start to make sense, like having a ClientID field in the TeamMembers table:

 

teams.png

 

>> I'm probably mis-understanding the syntax but I don't get why you show a one employee to many offices for the Office Manager (is this my office admin role?) link - there would generally be at least one (or maybe more) office admin in each office.

 

Assuming it is true, I would like to focus on the following fragment of the RG and make two points which should hopefully conclude this thread:

Clients --< TeamMembers >-- Employees

 

1. Performing GTRR from Employees to Clients will establish a found set in Clients containing all and only clients whose team the current employee is a member of. No looping find is required for this. This should answer your original question.

 

>> OK...so I'm possibly confused all over again!   Can I do this in the script that runs on layout load of the client selection screen, or do I need to re-think what happens when the 'aplication' opens?

 

2. For the second point, we need to assume there is an AccountName field in Employees, containing the employee's login name. Now, if the users' privilege set allows access to records in the Clients table only when =

not IsEmpty ( FilterValues ( Get ( AccountName ) ; List ( Employees::AccountName ) ) )

then each employee will have access only to the clients whose team he/she is a member of. This should answer the real question behind your original question.

 

Yes, there is indeed an account name field in the Employees table (from which team members are selected) that matches the login name.

OK, thanks. I need to investigate permission sets (sadly they are not covered by the course I'm still working my way through) as currently I have set up three identical sets based on the full access group just to prove that I get the Office admin role to work correctly. I don't know how to apply what you are stating above, but it's a start, and again, I appreciate your time and patience.

Regards

Martin

[comment]

Let me add two sub-points to my point #1 above - hopefully that will clear your current confusion:

1.a  If you know the current user's EmpID (say you have it in a $empID variable) you can have a script do:

Go to Layout [ Clients ]
Enter Find Mode []
Set Field [ TeamMembers::EmpID ; $empID ]
Perform Find []

to establish the same found set in Clients as the GTRR would have.


1.b Similarly, you can achieve the same thing by:

Go to Layout [ Clients ]
Enter Find Mode []
Set Field [ Employees::AccountName ; Get (AccountName) ]
Perform Find []

Note that both finds take place in the Clients table, but the searched fields are in a related table. But again, none of this should be necessary if the privileges are set up correctly.


 

 I don't get why you show a one employee to many offices for the Office Manager (is this my office admin role?) link - there would generally be at least one (or maybe more) office admin in each office.

 
Yes, I meant the office admin - and no, I didn't know an office could have more than one. So the cardinality needs to reversed and the matchfield needs to be OfficeID (you will need another OfficeID field in the Employees table, to be populated only when the employee is also an office admin). This is assuming that an employee cannot be office admin for more than one office - otherwise you will need to add a join table between the two.
 

[Martin_S]

Let me add two sub-points to my point #1 above - hopefully that will clear your current confusion:

1.a  If you know the current user's EmpID (say you have it in a $empID variable) you can have a script do:

Go to Layout [ Clients ]
Enter Find Mode []
Set Field [ TeamMembers::EmpID ; $empID ]
Perform Find []

to establish the same found set in Clients as the GTRR would have.

1.b Similarly, you can achieve the same thing by:

Go to Layout [ Clients ]
Enter Find Mode []
Set Field [ Employees::AccountName ; Get (AccountName) ]
Perform Find []

Note that both finds take place in the Clients table, but the searched fields are in a related table.

 

>> Thanks - that's really helpful info and makes it much much clearer in my head

 

But again, none of this should be necessary if the privileges are set up correctly.

 

>> i think that's where I'm really struggling, how to use the privelidges to control access, as my original trial caused scripts to fail for the non full admin permission sets, so think I just need to find a way to understand this that doesn't involve spending any more of your time.

 

 

Yes, I meant the office admin - and no, I didn't know an office could have more than one. So the cardinality needs to reversed and the matchfield needs to be OfficeID (you will need another OfficeID field in the Employees table, to be populated only when the employee is also an office admin). This is assuming that an employee cannot be office admin for more than one office - otherwise you will need to add a join table between the two.

 

OK, so my intention was to add office admins to the appropriate priviledge set via their account name, and use that (with my sample script shown earlier in this conversation) to filter the appropriate records for office admins. Again, this is probably another conversdation altogether once i have found a way to understand how to use priviledge sets in my application.

 

But to answer your question, that is correct, office admin is only that for one office.

 

Thanks again, as always, much appreciated!

Martin