1 2 Previous Next 27 Replies Latest reply on Dec 22, 2011 11:28 AM by arthursc

    Display Lastname,Firstname




      How can I display in a related record a clients name as Lastname, Firstname in a drop down box?




        • 1. Re: Display Lastname,Firstname

          You would need to create a calculation field (assuming you have individual FirstName and LastName fields) that would combine the LastName and the FirstName in the format you want.


          Then in creating the list you use the calculation field as the second one (the first being the field that relates the record), and check that it is sorted by the second (and have that only show).


          HTH, if not let me know.



          • 2. Re: Display Lastname,Firstname

            Hi Laura,


            Yes I do have individual Firstname and Lastname fields.


            Not sure how to do the calculation to combine them though.


            Happy with how to display them.




            • 3. Re: Display Lastname,Firstname

              Hi Colin: As Laura says, you would first need to create a calculated field whose formula concatenates a LastName and FirstName field into a single value. You then create a Value List at File > Manage > Value Lists, which uses the calculated field you created earlier to populate the list of values. After that you format the field into which you want to insert the selected value as a drop-down list, and then choose the value list you created to supply the values of the drop-down menu.


              I see in your profile that you're new to FMP, so this all might be a bit overwhelming. It sounds like you might benefit from going through some of our online video tutorials, these should get your feet wet with the basics. There's also a printed tutorial and sample file available for download, which will start to familiarize you with hands-on techniques. And you may want to consider taking advantage of our new TechNet member promo for when you're ready to go deeper. The promo gets you the FileMaker Training Series documentation for US$9.99 (normally US$99.00). And remember that even after you're proficient with FileMaker Pro, the Help menu is your best friend. It contains a complete script step and calculation function reference with lots of samples, and explanations of all FileMaker concepts like value lists, portals, and so on. HTH.




              • 4. Re: Display Lastname,Firstname

                HI Dave,


                Thanks for the steering. I do have the FTS and I actually printed the 740 pages of the accompanying document!!


                I know where to create the Calculation but don't know what operator to use of the syntax.


                Alittle guidance there wll get me on my way.



                • 5. Re: Display Lastname,Firstname

                  Take a look at this image.  Maybe this would help you (this is in the Calculation Options)Screen Shot 2011-12-20 at 5.48.37 PM.png

                  You want your last name field (Last_Name in my example) and then a & and then ", " and then a & and then your first name field (First_Name in my example).  The & joins text.  "" will allow you to put text that is not part of a text field already.

                  • 6. Re: Display Lastname,Firstname



                    That is exactly what I found on the FMI Site. So I did exactly as you said and it works thanks.



                    • 7. Re: Display Lastname,Firstname

                      And as for the Value List with drop down.  You need to go to Manage > Value Lists.  in the window that appears, click New, and in the new value list window, select the first option, "Use values from field".  In my example, I then selected the Last_First field I created that has the calculation from my prior post.



                      Screen Shot 2011-12-20 at 5.52.38 PM.png


                      I chose to include all values and have them resorted so they appear in the value list alphabetically by last name. 


                      The next step is, in your layout design, to assign the value list to the applicable field that you want to have pull down the Last, First value lists.  Select the field, and then from the Inspector window, change the Current Style to "Drop-Down List" and in the Values from item, select the value list you created (LastFirst in my example).


                      Screen Shot 2011-12-20 at 5.53.34 PM.png


                      You can select whether to have scroll arrow, and then when you browse, if you click on the field in your browse mode, your drop down list will appear.

                      Screen Shot 2011-12-20 at 5.53.50 PM.png


                      Hope that helps.  There are more detailed examples out there for when you want to do more advanced value lists (such as when it only displays record choices that are related to the record you are on).

                      • 8. Re: Display Lastname,Firstname

                        What Andrew said!    However, if your database may contain some records with only a first name and no last name, and you want to stretch a little more, you can make the ", " optional like so:


                        Last_Name & If( IsEmpty( Last_Name ); ""; ", " ) & First_Name


                        The If statement checks whether Last_Name is empty, and if it is, it doesn't return the comma + space. I hope you're enjoying learning FileMaker!


                        On Dec 20, 2011, at 2:51 PM, alas posted:



                        You want your last name field (Last_Name in my example) and then a & and then ", " and then a & and then your first name field (First_Name in my example).  The & joins text.  "" will allow you to put text that is not part of a text field already.





                        • 9. Re: Display Lastname,Firstname

                          Hi Dave,


                          Yes I am enjoying it. A real learning curve but one I am very happy to climd.


                          More to do with putting the time in and that way the learning curve stays with you.


                          Thanks for the tip.




                          • 10. Re: Display Lastname,Firstname

                            Yeah, what he said too...!


                            And I guess you could apply that same logic to expand the formula even more to do an "If(IsEmpty)" on the First name as well.  This way if there is no first name, it only lists the last name and no commas-space after the last name.




                            If ( IsEmpty ( Last_Name ); First_Name; If ( IsEmpty ( First_Name ); Last_Name; Last_Name & ", " & First_Name) )


                            Make sure you have open/close parenthesis all around (i.e., note the double close parenthesis at the end).

                            • 11. Re: Display Lastname,Firstname

                              Hi Colin,


                              I do not see where it is mentioned so I will bring it up ... selection of names is not a good idea because two people can have the same name.  You should instead be using a unique, FM-generated, auto-enter serial number in your Customers table.


                              Then in your related table where you want to select a customer, create a CustomerID field (same data type as your customer table) and do not set it as auto-enter but instead, attach this 'Customers' value list to it:


                              Value list upon field values and select the Customers::CustomerID for the left pane and select 'also display values from' and select the new FullName calculation in the right pane.


                              Then below, specify all values and only display values from second field.  Then I suggest that you attach a pop-up instead of a drop-down so Users are forced* to enter only valid CustomerIDs.  There are ways around it if you really insist upon drop-down but you will then need to add additional protections on the field to stop free-form text entering.




                              ADDED:  And of course there are more (even better) reasons for using an ID instead of name ... someone can change their name and if you have based all of your relationships upon this name, they will all break.  Primary keys used in relationships should be meaningless.


                              Message was edited by: LaRetta

                              • 12. Re: Display Lastname,Firstname


                                • 13. Re: Display Lastname,Firstname

                                  Hi LaRetta,


                                  Excellent Point. I did read about names and value that could change somewhere back along and you have just re-emphised this piont.


                                  I will apply your example.


                                  @alas, Thank you but I guess you will have guessed I am moving on from my simple thought to....umm now I have the name, I want to display all the details relating to this "clients" record i.e name, address etc.




                                  • 14. Re: Display Lastname,Firstname

                                    I'm not sure I understand, arthursc.  Do you mean you want to display all of the details in the drop down?  Or something else.  Let me know and I'm happy to help.

                                    1 2 Previous Next