11 Replies Latest reply on Jan 18, 2013 3:03 PM by philmodjunk

    Help to sort out navigation between records



      Help to sort out navigation between records


           I have a bit of a navigation problem in my database.

           So far I’m only navigating with the search function, and I suspect it to be a much easier solution.

           I have a lot of different garments (max 36) all with unique style numbers and names (style number is not done with auto enter serial, though every record also has an auto entered serial no). I was thinking it’s possible to have a dropdown list where I can choose each record from, and then it will take me to that record..? Every record in the dropdown list much be sorted, first by style number and then by name, is that a possility..?

           The menu needs to be dynamic so when I add or delete a record the menu will accordingly…

           Is there a chance that this is possible at all? Or do anyone have another idea to approach this…?

        • 1. Re: Help to sort out navigation between records

               At least some of it is possible. That specified sorting of a value list based on data in your table may be difficult but there are alternate approaches to consider.

               First the basics, A drop down list can be used to select what records appear in a portal or it can be set up with a script trigger to perform a find for you.

               Using the performed find option, you might use a drop down list equipped global field for selecting a value and then an onObjectSave trigger could perform the script that finds the selected record. See this thread for examples of scripted finds: Scripted Find Examples

               Now for a value list.

               When you open Manage | Value lists and start to create a new value list, one of your options is "use values from a field". This option enables FileMaker to list all unique values present in the specified field. You can also include values from a second field to make it easier to select the correct value.

               But there's a limitation here, the values listed will appear sorted in ascending order. The order can be determined by sorting on either the first or the second field, but if your second field does not contain unique values, sorting on it drops out duplicates leaving one value for each value in this second field.

               So if an ascending sort of the values works for you, then you've got this pretty much up and working. If an ascending sort does not arrange the values the way you want, we have a problem.

               One alternative approach is to use two dropdowns instead of one. You would select a style number in the first drop down and then a name in the second. And when you pull down the name drop down, you only see names listed that are correct for the selected style. This is called a conditional value list and I have tutorial links on how to set them up if you are interested.

               There are also more sophisticated approaches where you type text, select values and a list of matching records appears in a portal or list of records. Clicking a row in this list can then take you to that record. This can make "fuzzy" searches where the specified data matches to multiple records work in fairly simple fashion for the user.


          • 2. Re: Help to sort out navigation between records

                 It sounds like a good idea.

                 Basiclly what Im trying to do is make a small database for making technical descriptions of garments for a small fashion label, with between 40-50 records total.

                 each garment is identified by a style name combined with with a name - together these style name and name combined should identify each garment with a unique name. say a style name could be SH001SS13 and name could be Ryan shirt/A which together wil come to "SH001SS13 Ryan Shirt/A" which means SH = shirts, 001 = shirt 1 in shirt category... Ryan Shirt/A if there's more Ryan shirts this is number 1.

                 What's important for me is to be able to quickly find fx a shirt and then all ryan shirts would be be grouped togehter like Ryan Shirt/A, Ryan Shirt/B, Ryan Shirt/C and after that maybe a group of fiona shirts will show..?

                 I have a calculation field that combines the style number name field and name field. when I use the calculation field in a drop down, and make the value list sort it will it not sort first sort by "SH" then "001" then "SS13" and then Ryan Shirt/A so if I had another garment called "SH001SS13 Ryan Shirt/B" it will come underneath..?

                 Since I can't remeber all names or style names by heart - it's quite important for me to see their name aswell as being sorted by SH0001 part since that will keep all shirts, skirts and jackets together..?

                 will this be possible with a value list?

            • 3. Re: Help to sort out navigation between records

                   Value lists are great, but once a value list gets to be any length at all I prefer to use a portal to show all the values, and then click on the portal row that shows the record I want to see.  The downside is the portal is always on the screen occupying the space - but you can often have the portal on the Form View of the record you want to see.

                   A portal can have filters at the top (with short value lists for each) - to show only shirts, for example.  You can use a 'diminishing portal list' that progressively gets shorter as you type in more of the name (so typing  's' in the filter box will show shirts, skirts, and shoes in the list, adding an h to make 'sh' will only show shoes).  A filter could be 'Colour', so when you choose 'Blue' you see all the shirts, skirts, socks and shoes that are blue.  Or 'Size 14', whatever.

                   You have more control over how the list is sorted - you can even have 'Sort' buttons that allow you to change how the portal is sorted each time.

                   And of course in the portal you can display as much information as you want to identify the correct item to see.

              • 4. Re: Help to sort out navigation between records

                     It sounds like an alphabetic sort of your values would group them like you describe. But make sure that your calculation field is set to return text and not number or the values (or the records) won't sort as expected.

                     Value lists in FileMaker can list data in two columns, the data from the first column would come from one field in your table and the data in the second column would come from a second field. When you select a value from such a list, the value from the first field is entered.


                     SH001SS13 could be shown in the first column of values and Ryan Shirt/A could appear in the second column, but only if SH001SS13 uniquely identifies the record you are trying to find.


                     I would not use SH001SS13 as a primary key for linking records in the technical sheet table to other records. The encoded meaning and specific format mean that you may at some point need to change this format and doing so will create problems for your database as any such change requires making duplicate changes in both your Technical Sheet table and in any tables that link to this field in a relationship. Instead, I'd use an auto-entered serial number field for the primary key and include SH001SS13 as a value in a text field in the same table. I can then use this field for seaches, sorts (Including this idea of a script controlled tool for navigating through your records), but if a change is needed, I simply edit the field and all my related tables stay linked without need for matching updates.

                • 5. Re: Help to sort out navigation between records

                       all my records has an auto entered serial aswell.

                       Sofar I made global field where I attached drop down list taking it’s values from a field that combines style number and style name.

                       Im just a bit unsure how to approach the script for the button to link the value list to the button and records, without physically have to put every record name myself..?

                  • 6. Re: Help to sort out navigation between records

                         to sorbsbuster - thanks for your idea, just a bit tight on space, i'll try to go with a drop down list first if that will work out what i want...

                    • 7. Re: Help to sort out navigation between records

                           Do you mean that you do not know how to set up the value list to list all values from your table?

                           If so, go manage | Value lists and click New.

                           Then select the "use values from a field" option.

                           Select an occurrence of the table that contains this data in the left hand drop down.

                           Then click the field that contains the data you want to see in the drop down.

                      • 8. Re: Help to sort out navigation between records

                             no it's more how to approach the script for the button - to link the value list to the button and have it move to the right records..?

                        • 9. Re: Help to sort out navigation between records

                               You don't need a button, as a script trigger on the field can make selecting a value in the value list perform the script automatically.

                               But either way requires a script to perform the find. I previously posted a link to a thread that illustrates how to do this:

                          Scripted Find Examples

                               Your script simply enters find mode and uses set field to copy the selected value in the global field you formatted with this value list to the field in your table that contains this data. Then it performs the find.

                          • 10. Re: Help to sort out navigation between records

                                 awesome works wonders with a script trigger. Thanks again, not really sure how to repay you for all your help...

                            • 11. Re: Help to sort out navigation between records

                                   not really sure how to repay you for all your help...

                                   No repayment is necessary or expected, but some people have contacted me via private message and arranged creative ways to say "thank you"...