9 Replies Latest reply on Jan 29, 2013 12:11 PM by philmodjunk

    Value List Sorting - I need a creative solution

    BenDouglas

      Title

      Value List Sorting - I need a creative solution

      Post

           Ok, this is the situation:

           I've got a table of Projects with the project ID and a project name. I've got another table with orders and on the layout I have a drop down list field containing the project ID to link it to that project.

           This field uses a value list - ProjectNames - that is made by:

           First Field: ProjectID

           Second Field: ProjectName

           This is great, except that we have hundreds of projects, and I want to sort the field DESCENDING by ProjectID, so that the most recent projects show up first (The most recent project would be 995525 and oldest would be 993128)

           So I'm needing either:

           - A clean solution that will just let me sort the value list by descending, yet still retain the ProjectID as the value transfered to the field

           - Those great Filemaker coders to update that feature to include some solution for this problem.

            

            

           I was thinking maybe a solution would be to make the first value a text calculation of ProjectID + ProjectName and the second would be a calculation like 1000000 - ProjectID, and if I add in 0s it should sort correctly. But if I did that then the field would not receive the correct ProjectID to link it. Maybe I could cut the number out of the list using something like Left ( CalculationField ; 6 ), and having that fill in the Quote's project ID using a script trigger for on modify... But that seems really sloppy and maybe tricky to implement. Thoughts?

        • 1. Re: Value List Sorting - I need a creative solution
          philmodjunk

               What if you just used your calculation as the actual projectID? This does, however, limit your projects to an upper value and then you get negative numbers should you ever exceed that value.

               On the other hand a search portal where you click a button in the portal to select a value can be sorted in any order you need.

               This demo file has several search portals across the bottom you can examine. Plus an auto-complete value list of names that a script uses to look up an ID number:

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: Value List Sorting - I need a creative solution
            BenDouglas

                 DAMN! That's the THIRD time I've lost the whole reply that I wrote out! Something is really weird with the WYSIWYG on this forum and my computer. They don't like each other.
                 Ok I'm typing this into a text document and copy and pasting it.

                 So.
                 - I don't think that's a good solution for the project ID. It's a little messy and also as I understand it using a number instead of text as a table key makes searches and joins a bit faster.

                 - I've used search portals in other places in the database, but here I think it's 3 clicks more than necessary for the user. My client just wants to quickly/simply type in the project ID or select it from a drop down list.

                 This technique works fine for SupplierIDs, CustomerIDs, ItemIDs, etc because those are sorted ascending, but for ProjectIDs, QuoteIDs, and OrderIDs I wanted them sorted descending because that puts the most recent ones first.

                 Maybe there's some way to prefix the project name with a date? although that still wouldn't sort correctly…

                 ---
                 On a side note I have another question:
                 I like the method you used for the "Starts with" search. Do you know if using a portal filter works faster or slower than using the search field to modify the related records?

                 IE I'm using a method where the related records are found based on Text being between Text_start ("___SearchValue") and Text_end ("zzzSearchValue") in the Database Relationships panel, instead of filtering the portal...

            • 3. Re: Value List Sorting - I need a creative solution
              philmodjunk

                   I feel your pain. Lately, this bug in the forum software seems restricted to a long standing issue where you open the forum and find that you don't have to log in and can make posts to the forum. But then, while you have a thread open and are composing a message to post, the software logs you out and you find that the message did not post and you have to sign in.

                   Other cases have occurred where this was not the issue, but I haven't encountered them since the last software update, but maybe I just have been lucky.

                   It is a good idea to copy long messages to the clipboard before posting, but this also requires that you remember to copy that text before submitting...

                   I like the method you used for the "Starts with" search. Do you know if using a portal filter works faster or slower than using the search field to modify the related records?

                   It's an interesting question. How are you using them in the relationship? With inequality operators?

              • 4. Re: Value List Sorting - I need a creative solution
                BenDouglas

                     Nah, it was an issue with Firefox specifically and WYSIWYGs in general. Probably some extension/add-on that was interfering. Whenever pressing the letters 'z' or 'x' it would refresh the page or go forward or back in the history... I'm not too happy with firefox and probably will switch back to using Chrome.

                     Anyhow.

                     So I'm using a method I read about in the FileMaker Training Series tutorial. Basically you create three global fields that are titled SearchText, SearchText_start, and SearchText_end. SearchText start would be a calculation of "___" & SearchText, and _end would be "zzz" & SearchText. Then in the Relations window you modify the relation between the table of what you'll be searching from and the table of the portal relationship such that

                     Text ≤ SearchText_end

                     Text ≥ SearchText_start

                      

                     Then when you modify the search field it runs a script to commit and reposition the cursor at the end.
                      
                     So that method relies on the table relationships and not the portal filter...
                     Although since I'm dealing with a client with many many records and I have a quicksearch set up for each table, I'm very concerned with which method works faster.
                      
                      
                     As for the first issue, I'm going to roll up my sleeves and try the method I conjured up but was too lazy to implement. I'll get back to you on whether that worked or not.
                • 5. Re: Value List Sorting - I need a creative solution
                  philmodjunk

                       You may want to experiment with conditional value lists that match only to projects of a specified year. That can also prune your list of values down to a more manageable size.

                       I'll have to go look at that portion of the training series and see why they implmented that method. Generally speaking, inequalities in relationships are not real fast, but portal filters aren't either.

                       On the other hand, there's a real "old school" approach to implementing a starts with type of relationship match where you use a recursive custom function in an auto-enter calculation to produce this list of text values if "apple" is entered into a name feld:

                       a
                       ap
                       app
                       appl
                       apple

                       This field can be used with = to match to a single global text field where you would enter one or more starting characters of the name. Such an = based relationship is likely faster than either of the other two methods.

                  • 6. Re: Value List Sorting - I need a creative solution
                    BenDouglas

                         Yeah, I'm basically completely renovating a system that was created using Filemaker 5 and they used that method. I figured it really increased the size of the database, but I didn't think it would be much faster than the other methods... maybe I'll go back and see if it works better.

                    • 7. Re: Value List Sorting - I need a creative solution
                      philmodjunk

                           In FileMaker 5, if I remember correctly, we were limited as to how many levels down you could go as you didn't have the option of a recursive function for doing this.

                      • 8. Re: Value List Sorting - I need a creative solution
                        BenDouglas

                             So I tried the method I stated in the original post, which was to create a value list using a calculated field that showed ProjectID & ProjectName as one value, and then another field would be a calculated text that would determine the order of the value list. But the problem with that was you cannot sort a value list based on a hidden field... so that was a dead end :/

                             So back to drawing board.

                              

                             I'll try some speed tests with the portal search methods tomorrow. Do you know if Filemaker Pro has any built in timers to test that sort of thing? I'm not using Filemaker Pro Advanced...

                        • 9. Re: Value List Sorting - I need a creative solution
                          philmodjunk

                               You can use set field or set variable with Get (CurrentTime) to log the time at the start and end of a script....

                               You may note that I never suggested you try that calculated field. But I did suggest that you can set up a conditional value list such that you can specify a year (or have the current year be specified automatically) and then get a value list of only those projects for that year.