7 Replies Latest reply on Sep 20, 2011 8:23 AM by philmodjunk

    Auto-enter info in a field depending on an other field

    ChloeF

      Title

      Auto-enter info in a field depending on an other field

      Post

      Here is part of the form from the "Vessel" table with my issue: form

      And here is part of the relationship table: Relationship

      The "Captain" field on the form is a pop up menu showing the values from the Crew table.

      The phone, email and Fax are entirely part of the Vessel table. 

      I want those 3 fields to copy the information from the captain's information on the crew table if selected, or be blank if nothing is. I really want it to COPY, not just refer to another table, and be able to edit it anytime.

      I would greatly appreciate if you could help me out! Thanks!

        • 1. Re: Auto-enter info in a field depending on an other field
          philmodjunk

          You can set Looked up value, auto-enter field options on these fields to copy data from the matching fields in the Vessels table.

          But you need a relationship from Vessels::Captain to Crew:: Crew member # (assuming Crew member # uniquely identifies each record in Crew) in order for this to work.

          • 2. Re: Auto-enter info in a field depending on an other field
            ChloeF

            I just did that and still it doesn't seem to work, here are the different screenshots. 

            captain

            relationship

            phone field

             

            Can it be because it cant find the crew record since the pop up menu is set to Crew::Full Name" and it tries to find an ID# corresponding?

            • 3. Re: Auto-enter info in a field depending on an other field
              philmodjunk

              Your value list should specify Crew Member # as field 1 and Full Name as field 2. Then you can select a captain by name, but enter the needed number into the field. To display the captain's name, add the full name field from Crew or use the pop up menu option in combination with "show values only from second field".

              Please note that with the full name field specified as field 2, it should be validated as a unique values field. This can only be done with a text field, not a calculation field. You may want to replace the calculation field with a text field that uses and auto-entered calculation to produce the full name. (IF you have separate first and last name fields). You may also want to modify this calculation include more info than just the name to help the user tell the difference between two different records in the Crew table that both list "John Smith" in the full name field.

              • 4. Re: Auto-enter info in a field depending on an other field
                ChloeF

                I did a pop up menu and made the changes you mentionned but for some reason when I choose the captain, nothing appears, all the fields stay blank, including the captain field. I'm not sure what's happening.

                • 5. Re: Auto-enter info in a field depending on an other field
                  philmodjunk

                  The relationship you are using matches by vessel and by Crew member number. Make sure that matching fields are of the same data type, not one a number and the other text. If crew::Crew member number is an auto-entered serial number so that it uniquely identifies each record in Crew, you do not need to include the Vessel fields. If you keep the vessel fields as part of the relationship, you will need matching values in these fields also before any data appears in the looked up value fields.

                  • 6. Re: Auto-enter info in a field depending on an other field
                    ChloeF

                    On the crew layout I use a drop down menu to choose the vessel, with a value list from the vessel table, which is why I needed the Crew::Vessel to match the Vessels::Vessel Name in the first time. 

                    And while one vessel will only have one captain from the crew records, many crew records will have the same vessel. 

                    How can I solve this?

                    • 7. Re: Auto-enter info in a field depending on an other field
                      philmodjunk

                      None of that requires that the same relationship match by both Vessel and Captain. As long as the crew member # field uniquely identifies each record in Crew, don't include the vessel fields in the relationship.

                      You can create a second, separate relationship that matches these two tables by vessel. (And this should use a serial number, not the vessel name. Vessel names are not unique and even though some think it bad luck, owners do change vessel names on occaision and both such cases will mess up your relationships when they are based on a name instead of a unique, never to be changed ID number.)

                      To create a second, separate relationship between two tables, select one of them in Manage | Database | relationships and click the duplicate button. (The button with 2 plus signs) This makes a new "occurrence" of that table, but does not duplicate the actual data table. It just gives you a new 'label' for that table that you can use to define a new relationship.

                      If "table occurrence" is a new term, you may want to read this tutorial on the subject:  Tutorial: What are Table Occurrences?