1 2 Previous Next 20 Replies Latest reply on May 5, 2014 11:23 AM by philmodjunk

    filtering a value list



      filtering a value list


           I have a quote writing solution that uses a line item table.  I set up the quote form with a portal to line items for entering each line item on a quote. It is possible to have anywhere from one to a dozen or so line items per quote.  To help the users out I originally set the part type field in line items to autocomplete from past values.  Since we're now over 10,000 records in line items it is slowly things down now.  I had the idea to create a value list of past entries and filter it by a self join relationship in the quote that matches the user name so that everyone would only see those values that they have used in the past, not the values used by everyone.  That sped things up for all users except the most prolific quoter, also the company president, for whom it is now excruciatingly slow.  I tested it on several machines and it is not just a slow computer or network connection.

           Is there a way to speed that up?  Better to go back to the old way?  Need to archive the file and delete some records?

        • 1. Re: filtering a value list

               Could you perhaps use a conditional value list where you select a category in a category field--either outside the portal or in the portal row, and then the value list filters down to just the values that are a member of that category?

               Is it possible to "standardize" the values entered in this field such that you could set up a table of those values with no duplicates in order for your conditional value list to list values from a much smaller set of records? (Import Records can be a slick way to pull duplicate ridden data from one table and produce a duplicate free copy of the data in another table.)

          • 2. Re: filtering a value list

                 I thought that filtering them down by username would be the equivalent of the conditional value list, will have to go back and look at that solution.  Basically I'm using the username as the "category" field.

                 It would be nice to standardize the list, but there are some instances where we just use a part type, like transistor, and some where there is just a part number like 2N2222 and yet others where it is both.  It all depends on the person writing the quote and what the customer wants to see.

                 It might be interesting though to try the import records script just to see how many duplicates there are. A find on duplicate values found 4638 out of 10487.

            • 3. Re: filtering a value list

                   Yes, it is the same technique, but since it is filtering by a different value--one that might be used in combination with the user name, it might result in a more responsive layout due to matching to a smaller number of records to be used in the value list.


                        It would be nice to standardize the list, but there are some instances where we just use a part type, like transistor, and some where there is just a part number like 2N2222 and yet others where it is both.  It all depends on the person writing the quote and what the customer wants to see.

                   That sounds like a real mess and one that really should be standardized--not only to improve performance, but to reduce confusion. Part types and part numbers really shouldn't be listed as data in the same field as they really aren't the same type of data. And just because you put this data into different fields doesn't mean you can't present this info in the form that makes sense to your customers.

                   It doesn't even need to be a script as that script might have very few steps. The trick is to specify "unique values", "validate always" on the field in the target table where you want to eliminate duplicate valules. Then you use import records to import the data from your current table into this new table.

              • 4. Re: filtering a value list

                     Although the name of the field is "part type" what gets entered into it is dependent on the type of testing we are doing.  Most of the time it is part type, but other times ...

                     I would like to standardize all of the fields, but there are users that are firmly entrenched in their style of quoting and it took me a couple of years to get to this compromise.  That's what I get for offering to write a solution when they decided they wanted a central quoting system, if they had gone out and purchased one they would have been forced into standardizing the entries to fit into the program they paid thousands for.  smiley


                     I think the main problem is that the part type field is in the line items table.  Another field "additional notes" that is in the quote table is very fast using the filtered value list and it is way more "messy" than the part type field.  That note field is a large text field to "free form" any notes to the quote.  Users usually put things like:

                     Analysis to include:

                     Visual inspection

                     Radiographic inspection


                     SEM inspection

                     Written report with photodocumentation


                     As users type into that field each line of text autocompletes almost instantly narrowing the list with each keystroke, which seems like it should take longer since it is not only looking through each record, but each carriage return within each record.  All I can think of for the slow response of the lineitem::part type field is that it is in a foreign table.

                • 5. Re: filtering a value list

                       I'm not really describing all that radical a change. You can put two fields side by side, one for part numbers and one for part types. Users that want to specify a part type use the one field. Users that want to enter/select a part number use the other. And selecting a part number should automatically fill in the part type field.


                            All I can think of for the slow response of the lineitem::part type field is that it is in a foreign table.

                       "Foreign" means what exactly? The added "remove" of being in a related table could be a factor, but it's hard to tell from here.

                  • 6. Re: filtering a value list

                         I understand and you are preaching to the choir on that.  I tried and was shot down by the users, mostly the one that signs my check, so that is the way that part type field is. 

                         By foreign I meant a different table than the one the layout is based on though it is in the same file.

                         The value list is set up to use existing values in LineItems::part type  which is related to Quotes by QuoteID

                         Then I specified it to Include only related values starting from Quoter2Quoter, which is another table occurrence of Quotes with a

                         Quotes:quoter =  Quoter2Quoter::quoter relationship. 

                         (The quoter field is auto-entered user name on record creation, not modifiable)

                    • 7. Re: filtering a value list

                           Yes, if you can reduce the number of "inbetween" table occurrences so that you are not asking FileMaker to "tunnel" through them to get to the needed values, this should improve performance.

                           And I know what it's like to deal with a boss that won't agree to what is in his own best interests to do.

                      • 8. Re: filtering a value list

                             The only way I can think of to do that would be to add a quoter field to line items and have the "Include only related values starting from" match on that field.  I guess it's just a text field and wouldn't really show up anywhere, but should speed it up since it is in the same relationship that the portal is using.


                        • 9. Re: filtering a value list

                               Yes, and it can use an auto-enter option to copy the data from the parent table.

                          • 10. Re: filtering a value list

                                 OK, so I added lineitems::quoter and populated it with the auto-enter from the Quote table.  Then I related it to Quotes::quoter.

                                 When I set that up on the value list filter it is just as slow.  What am I doing wrong?

                            • 11. Re: filtering a value list

                                   I don't know that you are doing anything wrong. The size of your field's index may be the reason that an auto-complete enabled value list is slow to update as you open it and enter text. This can be a direct result of the sheer number of different values that appear in the index--which in turn is caused by the "messy" data that you've described. You may have to tell your boss (My sympathies), that if we wants a faster response, you'll need those two fields so that part type and part number data are separated. By using two fields in this fashion, you should get two fields with much smaller field indexes.

                                   You may want to take a copy of the database and try making this change to see if it improves performance. Perhaps, if the boss can see the actual difference in behavior, you'll have a better chance of convincing him to make the change.

                                   Another option might be to use a search portal instead of an auto-complete enabled value list, but I'm not at all confident that it will have any faster performance.

                                   Here's a demo file you can look at if you want to experiment with that technique to see if it helps any: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                                   Since it's in the older File format, use Open from the File menu to open and convert the file if you are using FileMaker 12 or newer.

                              • 12. Re: filtering a value list

                                     If I make the field an edit box with autocomplete from existing entries, it is faster for the boss but slower for everyone else.

                                     The drop down list with the filter is really fast for everyone else, but unusably slow for the boss.

                                     So I'm sure you are right about it being the number of messy values entered by the boss.

                                     Right now the database has quotes going back to 1/3/2011 with 10520 records in the line item table. Over half of those are from the boss, with 3,141 unique values and 2545 that are duplicated at least once.

                                     We've been thinking about trimming the database down and deleting records prior to 1/1/2013 (would get it back down to 4,000 line item records) and have already pulled a backup from the server so we have a good copy for archives if it is ever needed. 

                                • 13. Re: filtering a value list

                                       That is definitely an option to consider.

                                       Your description of this also suggests a sneaky alternative. Assuming that your boss has a different account name from anyone else, set up two layouts--one optimized for his use and one for everyone else. Use a start up script to check the account name OnFirstWindowOpen and then navigate to the layout that is optimal for that account name.

                                  • 14. Re: filtering a value list

                                         I had a similar idea, though using two layouts with an OnLayoutEnter script that sends him to his layout and the rest of us to the other.

                                         I've used the same type of script to use different layouts for Mac vs PC where formatting/printing was an issue.

                                    1 2 Previous Next