Very odd Relationship issue between FM Pro >> FM Server

[Visionjcv]

Hi everyone,

I've been scratching my head with this one for days.

I have a database that has a number of tables as follows:

Users

Clients

User Client Join

Each of these have primary keys and are related by them. Now, the problem begins when looking at the 'Clients' table - or any instance thereof.

I have set the privileges definition based on the users primary key (which is set as a startup script to a global $$userID). I then defined the privileges to say that they should only have access to view or edit a client if they have a record in User Client Join with their user ID. It looks something like this:

cClientAllocatedTo_n = $$userID

The data for Clients is then displayed in a dashboard portal in the database. Now, this works perfectly. It only shows the records each user is authorised to view.

However, it simply does NOT work on Filemaker Server. The same file, if you open on the server itself in a client works perfectly if it is not hosted, and simply will not display any data with that privilege definition when the file is hosted by Filemaker Server.

Does anyone have any ideas? One concern to me is that the field cClientAllocatedTo_n is actually an unstored calc field which pulls the value of the userID from the User Client Join table (the field cClientAllocatedTo_n is in the clients table). However, I cannot think of a way of handling this any other way, since different levels of users will each have a record, so it would be unrealistic to store this in the client table itself.

Any advice would be extremely useful! The system is currently being used by 20 people and this bug is causing me infinite grief.

Thanks in advance.

[Agnes Riley]

Have you debugged it on server? Are you sure your user id is set?

Are you using a global somewhere that you haven't stored perhaps?

[bcooney]

If each client can have several users, hence your join table, then how can ".. the field cClientAllocatedTo_n is actually an unstored calc field which pulls the value of the userID from the User Client Join table (the field cClientAllocatedTo_n is in the clients table)" work at all?

cClientAlloceatedTo_n should, if I understand your structure, be a list of UserIDs and therefore its definition should be, List (Client_ClientUserJoin::_kF_UserID).

Then, the RLA rule should be, is my user ID ($$userID--populated in an Open Script) one of the UserIDs in the list?

FilterValues ( cClientAllocatedTo_n ; $$userID)

[Visionjcv]

Thank you for the replies.

bcooney: Because users will be allocated in discreet categories (namely Senior and Junior) there are two relationships enforced by a 'flag' field 'senior' and 'junior'. Therefore in this example, the field cClientAllocatedTo_n would always return a single result.

The motive behind the join table is that whilst each client can have ONE active 'junior' and ONE active 'senior' they could in theory have multiple 'inactive' juniors and seniors from previous allocations that are disregarded at the relational level. It's therefore used for a historic trail of the client >> user allocation.

Agnes Riley: I have indeed debugged, and since $$userID is used throughout the entire database it is most definitely set. It appears to be a bug in Filemaker itself based on the way hosted files are handled on the server, vs on a client... I was just hoping someone else would have some experience finding a workaround to this.

I'd be extremely grateful for any additional insights.

Best regards,

Jason

[bcooney]

This is not a bug. I don't understand your distinction between "hosted on a server" vs "on a client." Do you mean single-user vs. multi-user?

Post a zipped copy with some data.

[Vaughan]

Get rid of the global variable in the calculation -- make it into an unsorted calculation field as well.

[Visionjcv]

Hi Bcooney,

What I mean is if I open the file with Filemaker Pro it works perfectly. If I host it on Filemaker Server and then access it with Filemaker Pro it no longer displays the results - it shows an empty set.

I can't get rid of the global variable because it gets set as the current user ID when the user logs in, I can't really think of a better way of doing this unless I created a set of relationships that uses Get(AccountName) to identify the user and from there through a relationship identifies the user ID?

[Vaughan]

I can't get rid of the global variable because it gets set as the current user ID when the user logs in, I can't really think of a better way of doing

Use a global field instead.