3 Replies Latest reply on Apr 20, 2016 8:23 AM by erolst

    Calculation to display a user managers Fullname

    RayGerman

      I am creating a user table for our organization

       

      Users.png

      Each user get a primary key and a first and last name field.

       

      I have a calculation that concatenates the first and last name so its easier to read

      Calculation for fullName = FirstName & " " & LastName

       

      Now a user can be a manager, so I created a field User_Manager_ID which I would like to reference the PK_User key.

       

      I would like to display the first and last name of the users manager via a calculation field but I am not sure how to create a calculation to pull the values.

       

      In the table shown below first 3 users, have a manager with the Primary Key of 10 which would be Susan Williams.

       

      What would the calculation be to show the Full Name Susan Williams in a manager field of my table?

       

      Hopefully this makes sense?

       

      Thanks,

      Ray

        • 1. Re: Calculation to display a user managers Fullname
          erolst

          You don't need any calculations, just a relationship.

           

          In the Manage Database > Relationships tab, create a new table occurrence of the User table; call it e.g. User__self_Manager.

           

          Connect it to the existing User table, and define the relationship as

           

          User::User_Manager_ID = User__self_Manager::PK_user

           

          On your User layout, insert a new field; in the Specify Field dialog, select User__self_Manager from the popup (where it initially reads “Current Table”) and select the fullName field. That is the name of the current user's manager.

           

          Done. (aka “There is no step #241” …)

          • 2. Re: Calculation to display a user managers Fullname
            RayGerman

            Hi erolst,

             

            That worked for displaying the Users Manager. I did have to make a new calculation field for Manager_Fullname based off of the User_Self_Manager table occurrence to pull the Manger's Full name.

             

            This does bring up an interesting issue... A calculation value such as Manager_Fullname cant be modified since it is... well a calculation. So in order to assign a Manager. I need a way to insert a pk_UserID into the User_Manager_ID field. In real world filemaker development, do developers cheat and put a field on top of another field, so I can select a value for from a list but display the Manager_FullName calculation. Or is it better to make a global filed and use it to enter the data, then clear the global field?

            UserManager.png

            • 3. Re: Calculation to display a user managers Fullname
              erolst

              RayGerman wrote:

              I did have to make a new calculation field for Manager_Fullname based off of the User_Self_Manager table occurrence to pull the Manger's Full name.

               

              I'm not sure why that would be necessary; you can use the existing cFullName calc field since a manager is simply a one-record related set of the Users table, where that field exists …. you just need to use the field from that related TO,

               

              i.e.

              the relationship: User >-- User_Self_Manager

              (meaning a manager can have many users, but every user has exactly one manager)

              your layout: based on User TO

              the name field: comes from User_Self_Manager

               

              You will have a problem if you want to display a manager's users; that would require

              • a layout based on User_Self_Manager and a portal (remember “many users”?), or

              • a calc field (text!), evaluated from the context of User_Self_Manager and using List ( User::cFullName )

               

              RayGerman wrote:

              select a value for from a list but display the Manager_FullName calculation.

               

               

              You cannot change a calc field directly; but you can change its components, and/or the relationship(s) it uses to read values. This means: if you want to see a different manager, you need to assign that other manager's primary key to the id_manager foreign key.

               

              The easiest (not necessarily the best) way is:

              • create a value list, using fields from a (any) User TO; 1. field: id, 2. field: cFullName, show only second field

              • format the id_manager field as popup and attach that value list.

               

              Actually, if you do it this way, you wouldn't even need a relationship to see the manager name; the lookup of the second field via the first is built into the popup functionality. But then you wouldn't be able to display the user for a manager directly, without e.g. performing a Find.