I wonder anything to do with server setting or cache to flush? Just guessing. .
You might but this calculation and even pieces of this calculation in unstored calculation fields and then check to see what values are being returned on the server.
What storage option is specified for Employee_Username?
Do you have multiple layouts with names that contain "Manpower"; "auditor" or "Timesheet"? If you don't, you don't need to use the patterncount function like this.
Thanks for the advice Phil!
Will test this calculcation on the server when i get to the client place later this week. But weird thing is that it worked when i run on my FM Pro 12 client locally. Why?
Employee_Username is normal storage (not global). Not a problem right?
Yes, i do have a few layouts of those names which is why i use PatternCount function. Will this have impact to the server performance to do the calculation? I had be surprised if it is as the database is still quite small of around 10Mb only.
One more thing. My employee table has TO of Auditor, Lead Auditor, Appraiser. Then i noticed in the calculation of user record access security, there is this 'In the context of' statement at the top of the setting dialog box, and a dropdown list of all the TO to choose from. I chose Auditor since i'm working on who can and cannot see Auditor username in certain layouts. But seriously i still didn't quite understand this part and how it will impact user access.
I wonder the fact that i use the Employee_Username directly without table name caused the problem? But it worked running locally...
Will test it with Auditor_Tasked.Employee_Username on FM server later this week at client place. Any comment or advice welcome! ; )
I don't see anything that should funciton differently when hosted from a server. If the field had had global storage, then there can be an issue where a global field is handled a bit differently on a hosted file, but this does not appear to be the case here.
When a calculation refers to a field by name without the table occurrence name, you are referring to a field defined in the current table, not a field from a related table. The table occurence context you select for you calculation will effect how it evaluates if the calculation refers to fields in related tables, but this should affect your results in identical fashion whether you hosted the file from a server or not.
Two other possible issues:
Are you by any chance using Server Side authentication? (I would guess not, but let's be sure.)
Your expression compares a field called Employee_Username to a variable named $$AccountName. Account names are set up in Manage | Security. User Names are specified in Edit | Preferences. There are no built in guarantees that a user's AccountName will be exactly the same as their UserName.
Thanks Phil once again.
i've changed the statements without using PatternCount. Again, it worked on my local FM Pro 12 but not on the FM Pro 12 Server. ; (Let([$LayoutName = Get( LayoutName);$result = If ( $LayoutName = "Auditor Tasks" or $LayoutName = "Timesheet" or Left($LayoutName;8) = "Manpower" ; If ( Employee_Username = $$AccountName ; 1 ; 0 ) ; 1 )]; $result )To answer your comment, the field is not global. I'm also not using server side authenication.I don't understand your comment that "there are no build in guarantees that user's AccountName will be exactly the same as their UserName. Coz this assurance is done 'humanly'.. for testing, I used 'tester' username on Employee_Username = $$AccountName. So, it must work right?I wonder what else i can do... ;(
That is not the case. UserNames are defined in Preferences Account names are defined in Manage | Security. Thus, you can open the file with Username = "Fred'--the setting in preferences and Account name = "Admin" the account name used in the log in dialog when entering a password.
I didn't use the UserName defined in Preferences Account name.
I did however read somewhere that certain GET functions cannot work in the Custom Record Privilege Calculation..
Coz i'm using Get(LayoutName) and it seemed to have problem evaluating it from Host as compared to client. I'm beginning to understand this.. but i still need a solution.. a way out.. a workaround.. ; (
I wonder if there's another way to know which layout the window is in for the calculation to still work.
Did you ever check to see how the calculation is evaluating? as per my very first suggestion? What results did you get?
Sorry, i don't quite understand what you saying.
And it's difficult to explain it any simpler.
Open a database and select preferences from the Edit menu. You'll find a spot there for specifying a User name. This is not an Account Name. Preferences are stored as in a file for each user account set up on your computer. Thus this name identifies the specific user account on the computer that was used to open the file. Open any FileMaker file on the computer, use any account name and password you care to use and the User Name you see in Preferences does not change and is not necessarily the same name you used as the account name to open the file.
Take that file and open Manage | Security. You'll see a list of accounts by account name. There might be an account name created here that has the same name as the current user name specified in preferences or there might not be. There will only be an account name that matches the user name if an account with exactly the same name is created here. So you might have 2 accounts created here: "admin" and "Fred". You might then go to preferences and specify "Fred" as the user name.
The next time that you open the file. FileMaker will present you with a a log in dialog. FileMaker confuses the issue here a bit, becase it will enter the User Name from preferences as the default account name in the log in dialog so you'll see "Fred" appear in the account name box of this dialog. But you can delete "fred" and enter "admin" as the account and enter the password for the admin account to open the file. In which case your User name remains Fred but you account name is Admin.
Where this frequently trips up people is that when they open the file from a different computer, the user name may change but the account name they need to enter does not change.
I don't really expect this to be the issue here, but we have to look out for all possible failure points in the process and if you open the database for testing purposes with a different account name and password when it's on the server, your system may fail simply because the user name did not change, only the account name.
Please note that Get ( LayoutName ) is not identified in that article as a function that should return a different result when the file is hosted, but Get ( Username ) can return a different value in some circumstances.
Thanks for the clear explanation. Appreciate it.
From the link i shared above, I deduced that Get(LayoutName) has problem with location when it evaluate in the security module. If it's host, the security module will have a problem evaluating it since it doesn't know which layout on client side and therefore return nothing. But client has security at the same location, so it can evaluate it. And that's why it works on my client and not on server. It is my deduction.
So I'm left still wondering what other alternative I can do.
Perhaps you can replace $LayoutName with $$LayoutName and use a script to assign the current layout's name to what is now a global variable.
I tried that but it didn't work well... coz if you open a couple of windows with different layouts it will have a problem which to give what access i.e. to allow or not to allow access to record info.
I still think that you can make that work. You may need to build a list of open layouts and use filter values to see if a particular layout name is present in the list.
But one question keeps popping up in the back of my mind: "Why include a check for the layout name at all?" In Manage | Security, you can set each an every layout to be accessible or inaccessible for a given privilege set so why does this calculation also need to check a layout name?
Yeah, I thought about it too.. But my case isn't that simple...
I have Employee table with TOs Auditor, Lead Auditor, Supervisor, etc... And in Auditor Task layout I only allow Auditor to view his own Task only but Mgmt can view everyone else. Then in Job Record layout where there is Auditor, Lead Auditor, Supervisor, I will want to allow everyone to see everything. And that is why I'm stuck with this problem.
Perhaps there is an easy solution and I'm in my own blind spot.. after staring at it for over a week... ;(