12 Replies Latest reply on Apr 3, 2012 3:40 PM by eldoonmad

    Merge Fields Data Question

    eldoonmad

      Title

      Merge Fields Data Question

      Post

      In my database I have a table called Users. The fields are NameFirst, NameLast, Phone, EMail.

      I have a second table with a form view called Assets. I have many fields in here but one is set to select a user from the Users table to apply who the Asset is assigned to. I have this user field set up as a pop up menue to grab information from a value list. The value list is set to "use values from feild" and the specified feild is Users::NameFirst and to "Also display values from second feild" and I chose Users::NameLast. This way when I look at the drop down menue I see and it places the first and last name into the users field in my Asset form. This all works just fine just the way I want it to.

      So here is the part I am having an issue with. I have a layout with just a text field for a user agreement form. With in the user agreement form I have placed merge feilds to grab certain data from the Asset table, one being the user's name. However it only puts the first name, not the last name as well. I am assuming that even though I tell the value list to show the second field, that is only for reference and is not actual data that is brought over. I am wondering what the best way to get both the user's first and last name to show up on both the pop up menu in the Assets form as well as show up in the merge field in the User Agreement Form layout. Any help would be greatly appreciated.

        • 1. Re: Merge Fields Data Question
          philmodjunk

          Users---<Assets

          This way when I look at the drop down menue I see and it places the first and last name into the users field in my Asset form.

          Not quite. This actually enters only the First name into this field on your form. Any data from the second field (NameLast) is displayed but not entered into the Assets record. If you get two users with the same first name, there will be no data in the Assets record that distinquishes one user from the other.

          Linking records by name is not usually a good idea. Names are not unqiue. People change their names and if a name is entered incorrectly, correcting the error at a later date can break the connection to records in the related table. Linking these two tables by an auto-entered serial number avoids all of those issues.

          If you link your tables by an ID number, you can add the name fields from users to a layout based on Assets and they will display the correct name for your record.

          To make such a relationship work, use a value list where the ID number from Users is listed as "field 1" and then a calcluation field that combines first and last names can be used as "field 2" (Also display values from..). Then you can select a user by name, but the value list enters the ID number.

          Note that this works for relatively small lists of Users. If you have a larger list, there are other, script enabled methods that can be used to make working with such larger lists much more user friendly.

          • 2. Re: Merge Fields Data Question
            eldoonmad

            Ok, so I did what you said and created the calculation for First and Last name to be combined into one in the Users table. I selected the autoserialized UserID feild for the specified value from feild and then the caculated field for the second. So now the full name comes up however only the first user is listed, no longer all of them. I do have "Include all values" checked in the value list properties. Also the relationship I have set for the tables is between Users::__pkUserID and Assets::User. This is what I had before and it pulled all the user names with my previous settings. Any thoughts?

            • 3. Re: Merge Fields Data Question
              philmodjunk

              Are there any values in __pkUserID?

              Auto-entered serial numbers will appear in any NEW records you create but the field will be blank for records that existed before you defined this field. You can use Replace field Contents to assign serial numbers to your existintg users records.

              • 4. Re: Merge Fields Data Question
                eldoonmad

                Yes. There are 172 users listed, each with their own ID listed in the __pkUserID field. I did import the users but I told FMP to do all autoentry items, so it generated the IDs. Also another issue I am seeing with the method is that in my first post I stated that I wanted the information to transfer over to the User Agreement Form but since it is pulling the ID I am getting the Users::__pkUserID instead of Users::FullName, which by the way is the new calculation field I created. 

                • 5. Re: Merge Fields Data Question
                  eldoonmad

                  Here are some screen shots. This is how I am specifying the User field in the "Assets" table

                  • 6. Re: Merge Fields Data Question
                    eldoonmad

                    Here is what the value list looks like

                    • 7. Re: Merge Fields Data Question
                      eldoonmad

                      Here is the relationship between Assets and Users

                      • 8. Re: Merge Fields Data Question
                        eldoonmad

                        Here is the Users table view. Only the first row will show up in the drop down menu.

                        • 9. Re: Merge Fields Data Question
                          philmodjunk

                          Make sure that FullName has "text" for a return type--not number.

                          You don't need any calculation field. Use the field tool to add the name field(s) from Users directly to your layout and it will show the user's name.

                          • 10. Re: Merge Fields Data Question
                            eldoonmad

                            Okay, so now it is pulling the full list in my "Asset" table form. I had forgot to change back something I was playing around with. So that is working well but I am still not getting the FullName in my "User Agreement form" layout, I still only see the UserID. This is the one I have set to use Merge Fields Data and have it set to pull Assets::User.

                            • 11. Re: Merge Fields Data Question
                              philmodjunk

                              Don't set it to pull Assets::User.

                              Enter layout mode.

                              Use the field tool to add a field to your layout.

                              In the drop down in specify fields, select Users.

                              Click the full name field.

                              You have now added the full name field from Users to a layout based on Assets.

                              You can also use insert merge field to insert this same field as merge text onto your layout.

                              Any field form users that you add to the layout will, courtesy of the relationship linking them, display data from the Users table on your Assets based layout.

                              • 12. Re: Merge Fields Data Question
                                eldoonmad

                                Got it!!! thank you for bearing with me and being so helpful.