3 Replies Latest reply on Jun 29, 2009 9:22 AM by philmodjunk

    Auto Enter fiels value based on other field value

    Socio.t

      Title

      Auto Enter fiels value based on other field value

      Post

      Hello everyone,

       

      I'm new on FM, been working with it for a week now. I have here what I think is a slightly complicated case!

       

      I have a set of related tables to register my company projects. In the projects table I want to insert the project information as well as the client information tha are related to that projects.

       

      The function that I'm looking for at the moment is one that as an example when I choose from a drop down list the client name, automatically gives me that client code in the respective field. And vice versa, in the case that I only know the client code, choose it from a drop down list and it gives me the client name in the respective field.

       

      The Project table is related to the Client table through the Client Code.

       

      Example:

      ClientName: FileMaker

      Should auto insert in the Client Code: 0012

       

      and the other way around two.

       

      I kind of managed to do this with a script trigger on object modify that if the ClientCode on Projects is equal to ClientCode on Clients then ClientName on Projects is equal to ClientName on Projects.

       

      But it doesn't work both ways.

      I was once able to get the Client Code from the Client Name but instead of getting "0012" I got "12".

       

      And don't know why, CAN'T GET IT TO WORK BOTH SIDES.

       

      Would really appreciate it if someone could help me with this problem.

       

      Thanks a lot.

       

      Best regards,

       

      Socio

        • 1. Re: Auto Enter fiels value based on other field value
          philmodjunk
            

          "I was once able to get the Client Code from the Client Name but instead of getting "0012" I got "12"."

          Check your field types. It looks like you may have a number field on one side of the relationship and a text field on the other. If leading zeroes are significant, you will have to store the value in a text field instead of a number field. (Entering 0012 into a number field will give you the 12 that you report.)

          • 2. Re: Auto Enter fiels value based on other field value
            Socio.t
              

            Hello PhilModJunk,

             

            Thank you so much for your help. It really helped to solve part of the problem.

             

            But I still have a problem there.

             

            I'm using script triggers to get the fields to auto enter themselves when the other is modified. Don't understand why (yet), when I introduce the client serial number it completes the correct client name I'm looking for without any problem. But when I try to insert the client name it doesn't response with the correct client serial number except if I alreadyhave inserted the right client serial name.

             

            Is it posible that the scripts are blocking themselves together or are in some kind of conflict?

             

            As I told you before I have some really simple scripts working that are triggered on field modify. Example:

            IF [TAB_Projects::Client Serial = TAB_Clients::Client Serial]

            SET FIELD [TAB_Projects::Client Name; TAB_Clients::Client Name]

            END IF

             

            I feel this is kind of a primitive solution. Would you advice me to work with LOOKUP function. I'm not very familiarized with it. Would it help me to solve this challenge?

             

            Thank you very much for your help. I really appreciate it.

             

            Take care,

             

            Socio

            • 3. Re: Auto Enter fiels value based on other field value
              philmodjunk
                

              Given that there's a lot I don't know about your solution, its difficult to offer good advice. Depending on what you need and how you've structured your database, it's possible that you don't need any script triggers for this.

               

              If I read your posts correctly, you want to do two things: You want the data from a related table to appear if the user enters an ID number AND you want the same data to appear if the user enters a name in a different field.

               

              You should then have a relationship that links your two tables based on your ID number field. Using a person's name--which is the more user friendly approach is a bit stickier. Here's why: 1) It's entirely possible that two people could have the same name. 2) People change their names due to marriage and other reasons.

               

              Here's a simple approach that works for many situations:

               

              Define a value list that specifies your ID code number in column 1 and your name field in column 2. Now format your ID code field with this value list. There are options here you can play with to see what works best for you--including hiding the id code field so that only the name field is visible--though that can be a problem if you have two people with the same name. Now, when your user looks up a person, they see both the ID codes and the names in the same value list.

               

              Data from a matching record can be physically copied from the related table using the Look-up auto-enter feature, but it's usually better just to display the fields from the related table.