6 Replies Latest reply on Dec 2, 2011 9:40 AM by AngusWalker

    Joining Fields together

    PeterDowns
    PeterDowns Apr 4, 2011 6:39 PM

      Title

      Joining Fields together

      Post

      Hi 

      I come from an access background so I am trying to get my head around a few differences.

      My problem is I have a table of users with for example a UserID field / First Name / Last name / location etc

      I want to create a popup menu that uses the userID as the reference field but displays the full name of the user ie First and last name in the drop down. So I have tried a value list but am only able to pick 2 fields USerID + first name and can't edit to do more. I can do it several different ways in access but i can't find anything in knowledge base how to do it in FMP.

      Thanks in advance

      Cheers

      Peter

      • 130 Views
      • Tags:
        • 1. Re: Joining Fields together
          philmodjunk
          philmodjunk Apr 5, 2011 10:33 AM (in response to PeterDowns)

          To show data from more than one field in the second column, define a calculation field in your users table that combines the data from these fields into a single block of text, then specify this field to be used in the second column of your value list.

          Having used Access myself, I can predict a certain level of frustration for you in using Filemaker. It doesn't do things near the same way so it takes some getting used to before you can "switch gears" mentally to get things going smoothly for you. And FileMaker's value lists don't offer anything close to the nice combo box options you can use with Access.

          • 2. Re: Joining Fields together
            PeterDowns
            PeterDowns Apr 5, 2011 4:41 PM (in response to PeterDowns)

            worked a treat

            Not totally frustrated yet.

            Cheers

            Peter

            • 3. Re: Joining Fields together
              PeterDowns
              PeterDowns Apr 5, 2011 4:53 PM (in response to PeterDowns)

              Follow up bit

              have made the calculation field called 'wholeName' so the pop up menu has field 1 - UserID and field 2 - wholeName. If I want to sort the popup list by last name alphabetically how do I go about that.

              Cheers

              Peter

              • 4. Re: Joining Fields together
                RickWhitelaw
                RickWhitelaw Apr 5, 2011 6:20 PM (in response to PeterDowns)
                Aha! I had the same problem. For your WholeName field, concatenate it as LastName&","&FirstName and then simply sort by second field. If you can live with this it works. You end up with entries like "Smith,John" etc. Happy coding, RW
                • 5. Re: Joining Fields together
                  PeterDowns
                  PeterDowns Apr 5, 2011 6:34 PM (in response to PeterDowns)

                  cheers

                  thanks

                  • 6. Re: Joining Fields together
                    AngusWalker
                    AngusWalker Dec 2, 2011 9:40 AM (in response to PeterDowns)

                    Hi.  As a total filemaker virgin, I am trying to merge three files that I am importing from an Excel spreadsheet.  They are text fields of 256 characters each, hence the reason why I had to use three fields in Excel.  Now, I want to concatenate them permanently into one field.  They are too long to do so in Excel and I tried to follow the thread above and do it in fielmaker.  All seemed ok but nothing shows in the additional "Total text" field after I concatenated the three field entries, Text1, Text 2 and Text 3 into the new Total tex fields.  There are some 1000 records to import and concatenate.  Then ideally, I would get rid of the original text fields.

                     

                    Thanks for any help.

                     

                    Angus