9 Replies Latest reply on Jan 8, 2010 1:06 AM by comment_1

    Displaying a Drop Down List in Record Order

    benpwallis

      Title

      Displaying a Drop Down List in Record Order

      Post

      FM PRO ADV 10.0V3

      Hi,

       

      I have two tables linked together so table 1 pulls information in a drop down box layout from a field in table 2

       

      I am struggling to have this display in anything but Ascending Order

       

      What I want to do is in table two I am entering records lets say in the following format

       

      presales

      hardware

      software

      accessories

       

      but when I click on the drop down box in table one it displays the above records in ascending order

       

      I tried creating a new field named ID and then placed in the ID Field numerical numbers ie 1 2 3 4

       

      So I have the first field in the table two as ID with the number 1 as the first record the next field (challenge) holds presales

       

      however still when going back to table 1 although I change the relationship from (challenge) to ID (replicating the ID field in table 1) when I still select the drop down box it ignores the ID field and still sorts ascending on the 'challenge' field.

       

      Finally I have tried right clicking the 'challenge' field and sorting by value list ID (created by specifying field in table 2) and still nothing

       

      I guess I am missing something dead simple but now looped round so much I probably confused myself.

       

      Any suggestions would be appreciated and apologies for the newby question!

       

      Regards

       

      Ben

        • 1. Re: Displaying a Drop Down List in Record Order
          comment_1
             Value lists based on a field are always sorted in ascending order of the values. You can change the value list to use the ID field, also showing the description field. In most cases you'd want to select the ID anyway, so the link doesn't break if the value is renamed.
          • 2. Re: Displaying a Drop Down List in Record Order
            benpwallis
              

            Thanks,

             

            Could you clarify one thing? I create the ID Record and tell the value list to look at this field then look at the description this then correctly sorts the list 1-4 but it displays 1-4 at the beginning of the Description and when I select either 1-4 then this is all that is displayed in the field

             

            If I tell the value list to show results from 2nd field only then it sorts automatically by that field and I am back to square one

             

            Sorry....

             

             

            • 3. Re: Displaying a Drop Down List in Record Order
              ninja
                

              benpwallis wrote:

               

              If I tell the value list to show results from 2nd field only then it sorts automatically by that field and I am back to square one


              Unless they changed it in FMP10, you can choose which field to sort by.  In you Manage>ValueLists window, choose the first field, click "also use values from second field", and there is a radio button set to choose which field to sort by.

               

              If you "use only values from second field", you can only sort by the second field...but if you show both, you can choose which one to sort by. 


              • 4. Re: Displaying a Drop Down List in Record Order
                benpwallis
                  

                Ninja wrote:

                 

                but if you show both, you can choose which one to sort by 

                 

                Ninja,

                 

                Thats my whole point... I do not want to show the results from the first field I just want to reference that field to sort my results in a certain order hence the 1-4

                 

                so when I use only values from the second field I can only sort by the second field which again back to square on

                 

                At the moment my results are shown as

                 

                1 Presales

                2 Hardware

                 

                etc

                 

                If I choose to show only results from 2nd field this is viewed as

                 

                Hardware

                Presales

                 

                What I want the result to look like is

                 

                Presales

                Hardware

                 

                Thanks

                • 5. Re: Displaying a Drop Down List in Record Order
                  comment_1
                    

                  benpwallis wrote:
                  when I select either 1-4 then this is all that is displayed in the field

                  If you use a popup menu, then it will display both. Alternatively, you could place the related Description field (non-enterable) on top of the selection field.

                  • 6. Re: Displaying a Drop Down List in Record Order
                    benpwallis
                      

                    Hi,

                     

                    In the end I added another field in the 2nd table named zvaluesort and then made this a calculation field to say

                     

                    substitute(10^"name of number column ie 'ID' holds 1-4 values" - 1 ; "9" ; " ")

                     

                    I then told the value list in table one to look at my description field and then 'also display values from 2nd field' being zvaluesort and sort by 2nd field selected

                     

                    As zvaluesort never has any entry (referenced through calculation) then nothing else is entered in the selection only my Description :-)

                     

                    Thanks for the help anyway

                    • 7. Re: Displaying a Drop Down List in Record Order
                      comment_1
                        

                      I hesitated whether to suggest that. Note that the method is limited to approximately 100 values - and I still think it's a mistake to select real values instead of IDs. If you are confident they'll never change, why not simply use a custom value list (and save a table as a bonus)?

                      • 8. Re: Displaying a Drop Down List in Record Order
                        benpwallis
                          

                        Argh!

                         

                        Good hesitation on your behalf cos your right it wont exceed much over 100

                         

                        I started off with a custom value list as my records wont change but then I wanted to be able to make a selection from the drop down box and have another field populate with records from another field depending on the selection made.

                         

                        I am unsure whether this is possible? because a custom value list is not a record so when I select say for example PreSales from my drop down I cannot tell FM to populate a field based on that selection as it does not have anything to cross reference that selection with

                         

                        Enlightenment would be appreciated 

                         

                        Regards 

                        • 9. Re: Displaying a Drop Down List in Record Order
                          comment_1
                            

                          Well, it is possible, but you'd have to maintain the same list in two places: a custom value list (in order to force the sort order), and a table pairing each value with another. After selecting the value from the custom value list, get the other value via a relationship to the table.

                           

                          Perhaps it would be better to let users choose from a portal or a list view in a new window - those can be sorted any way you like.