1 2 Previous Next 26 Replies Latest reply on Jul 13, 2011 3:19 AM by brian.curran

    Get (AccountName)



      Get (AccountName)


      I'm sure this is a pretty easy task but I simply cannot work it out!

      I want to restrict certain users from seeing all of the data on a dashboard layout. To do this, I'll be using conditional formatting to hide the relevant fields but can only do this after a script has identified who is logged in.

      To start with, I created a new table called "Current_Account" and added a field called "account_name" which is set to "Text" type.

      On the "Auto-Enter" tab, I ticked "Calculated value" and typed "Get ( AccountName )" in the box. The checkbox at the bottom of that screen relating to evaluating fields is ticked. The "Do not replace existing value of field (if any)" checkbox is also ticked.

      Under the "Options" tab, I have ticked the "Global" storage checkbox and everything else is greyed out.

      When I close the file down and log back in again, the field is blank and only updates when I click the "New Record" button. This is despite the fact that "Account Name" is set to be auto entered...

      Any suggestions?


        • 1. Re: Get (AccountName)

          This is not a good use for an auto-entered calculation. If the only purpose is to store the current user's account name, just use Get (AccountName) and you don't need any table or field for this.

          Here's a conditional format expression that hides the object's text by changing it to the layout's color if the current user has not opened the file with the Admin account:

          Get ( AccountName )  ≠ "Admin"

          (and then select a layout matchign text color for when the above is true.)

          In general, the results of a Get function should only be used in a field of type calculation where the storage options button has been used to specify that the field be unstored.

          • 2. Re: Get (AccountName)

            Ah, I understand now. Thanks very much Phil, I've wasted so much time searching the forums and Google since the weekend without any luck. I didn't realise that the "Get (AccountName) was used as part of a formula when entering the conditional formatting. I thought they were two distinct things with the formatting relying on a global account_name field stored somewhere.

            Regarding your last paragraph, should I change the Storage "Indexing" option to None for all fields using a Get function?

            I tried your expression above but I had to amend it from "Admin" to my account name in order for it to work. However this would also rule out a middle tier of security account I'm planning for my Managers. What would be the best way to write this expression for more than two levels of security. For example:

            Admin = full access (Me)
            Manager = full view, limited editing
            User = limited view, very limited editing 

            Also I note that checkbox fields are still displayed despite the font colour being changed, is there a trick to hiding these along with the field outline and other 'drawn' objects? Please see below...

            This is the "Admin" view:
            Admin View 

            This is the "User" view:
            User View 

            Thanks again

            • 3. Re: Get (AccountName)

              You've got two problems with your conditional formatting:

              1) Including other account names in the expression (Can do that).

              2) Using conditional formatting to hide an object that is not strictly text. (Can't do that).

              Here's a fairly simple expression for checking the current account name against a list of account names:

              IsEmpty ( FilterValues ( List ("Admin" ; "Manager" ) ; Get ( AccountName ) )

              expression is true if account name is not admin nor manager. This can also be rewritten as:

              IsEmpty ( FilterValues ( List ( Related Table of AccountNames::AccountName ) ; Get ( Accountname ) )

              Which avoids having to modify your conditional format expression each time you need to add/remove an account name from your system as you would edit a table listing such account names instead.

              That doesn't solve issue 2 though.

              You have three other options:

              1. Use a second, nearly identical layout where the restricted objects are hidden. Use Get ( AccountName ) in a script to take the user to the layout that is correct for their account.
              2. Put the objects inside a portal. Manipulate either the portal's filter expression or a field in the relationship to control whether a single related record is visible in the portal. Put your layout objects inside the portal.
              3. Put the objects inside the 2nd of two tabs in a tab control. Format the tab control so that it's color and borders are not visible. Give the two tabs object names. Use Go To Object to select the desired tab to make the objects inside the second tab appear or disappear.
              • 4. Re: Get (AccountName)

                Thanks again Phil, the first expression is perfect for my needs.

                Of the 3 options:
                I'm currently using the first idea (without the script) but as I'm still developing the db I have to remember to make changes to both layouts which isn't ideal so I'm looking for an alternative.

                2nd option is what I'm trying to use now but I just can't get it to work, very frustrating.

                No. 3 looks the most complicated so I'll have a go at that if I can't get number 2 to work.

                Whilst searching Google for help on the 'Portal Invisibility' trick, I came across this feature request from way back when in 2006!
                http://filemaker.463488.n4.nabble.com/Feature-request-visibility-attribute-for-layout-objects-td494879.html I can't believe that FM appear to have ignored this feature request for over 5 years! 

                • 5. Re: Get (AccountName)

                  This example only works with FileMaker 11, but a similar approch using a global field will work with older versions:

                  If you don't have a serial number field in your table, add one and use Replace Field contents to update any existing records with a serial number in this new field. We'll call it PrimaryKey in this example.

                  Open Manage | database |Relationships.

                  Find the table occurrence your layout is based on, select it and use the button with two green plus signs to create a new table occurrence with the same data source table.

                  Link them to create this relationship:

                  YourTable::PrimaryKey = YourTable 2::PrimaryKey

                  Add a one row portal to YourTable 2 on your layout.

                  Give your portal this portal filter expression: $$Show

                  Put your check box formatted field inside this portal.

                  The following script will update $$Show to reveal the field:

                  Set variable [$$Show ; value: True ]
                  Refresh Window [Flush cached join results]

                  Do the same but set it to False and the field will disappear.

                  • 6. Re: Get (AccountName)

                    Wow! This seems to work great, a few questions if I may...

                    Could I have several portals all with the $$Show filter expression?
                    Could the script run automatically using the Get (AccountName) function if "Admin" or "Manager" are logged in?


                    • 7. Re: Get (AccountName)

                      Yes you can add multiple portals all with the same filter expression.

                      Since $$Show is a global field, the value in it can be set in a script that is performed when the file opens.

                      It might also be possible to set this up with an unstored calculation such that no script is required.

                      Define an Unstored calculation, cReveal, like this:

                      IF ( NotIsEmpty ( FilterValues ( List ( "accountName1" ; "accountName2" ) ; Get ( AccountName ) ) ) ; PrimaryKey )

                      This expression should look familiar to you. It's similar to the one we were using in a conditional format expression.

                      Set up your relationship like this:

                      YourTable::cReveal = YourTable 2::PrimaryKey

                      And any portals to YourTable 2 will only display a record if the current account name is in this list.

                      • 8. Re: Get (AccountName)

                        Great stuff, I had an issue with one of the portals refusing to display but I think this was due to the fields overlapping the portal. Increasing the size of the portal by 1 pixel seems to have fixed this. I currently have two test scripts with "True" and "False" so that I can test the display by turning the visibility on and off.

                        I'm not sure where an Unstored calculation should be defined so I'll have a look at this now. Would an unstored calculation be better than a script?


                        • 9. Re: Get (AccountName)

                          The unstored calculation field might be simpler than a script, but the results in your case should be the same.

                          The unstored calcluation is cReveal in my example. We are only discussing one table here, so there is only one place you can put this calculation field.

                          • 10. Re: Get (AccountName)

                            Phil, is this bit correct from your post yesterday?
                            IF ( NotIsEmpty ( FilterValues ( List ( "accountName1" ; "accountName2" ) ; Get ( AccountName ) ) ) ; PrimaryKey ) 
                            FM says that this function cannot be found. 

                            I'm still having an issue with one of the portals not displaying, which is really weird as I've double checked and triple checked the settings which appear to be the same as the other two.

                            If I open FM and the file from scratch, no portals are displayed which is how it should be. Running the 'True' script displays the first two portals but not the third. If I run another script to perform a different action, the third script magically appears! Any suggestions on where I can look to investigate this annoying glitch?

                            • 11. Re: Get (AccountName)

                              Not and IsEmpty are two different items. A typo on my part ran them together. Insert a space and it should be OK.

                              Can't tell from here what might be the issue.

                              Are you sure the portal filter expression is exactly the same for each?

                              Is the same table occurrence specified in Portal Setup...|Show records from selected for each?

                              Is the field in the portal selected from the same table occurrence as the portal?

                              • 12. Re: Get (AccountName)

                                Finally, after lots of trial and error, logging in and out as different users I think I've got it!

                                Some of the fields I want to hide are in a table called "Accounts"
                                I added a calculation field called "c_reveal" and used "If ( IsEmpty ( FilterValues ( List ( "Managers" ; "Users" ) ; Get ( AccountName ) ) ) ; _primary_key)" I also set the calculation result to Text and ticked the Do Not Store checkbox.
                                In Relationships, Accounts::c_reveal = Accounts2::_primary_key
                                I then deleted the $$Show from the portal filter.

                                When I log in as Users or Managers the fields are invisible yet they appear when I log in as Admin Smile 

                                • 13. Re: Get (AccountName)

                                  Ah, so using "Not" before "IsEmpty" will reverse the expression yes?

                                  • 14. Re: Get (AccountName)

                                    If by "reverse" you mean it changes True to False and False to True, then yes.

                                    1 2 Previous Next