7 Replies Latest reply on May 13, 2014 12:21 AM by PieroF

    FilterValuesSUB - what about a new function ?



      FilterValuesSUB - what about a new function ?


           I think a new function FilterValuesSUB (or any other more fitting name…) would be very useful in FMP that does the following: given the 2 inputs textToFilter and filterValues, as in the existing FilterValues function, the result should however be a list that includes all textToFilter values that are NOT in the filterValues list.


           Example: textToFilter = (A¶D¶E¶C¶Z), filterValues = (A¶B¶C), FilterValuesSUB (textToFilter; filterValues) returns (D¶E¶Z)


           This would allow to evaluate keyword lists as if they were sets. The existing FilterValues operates as an AND operator, chaining two lists basically implements an OR, and the new FilterValuesSUB would complete the set of functions, and allow to implement also a NOT operator when textToFilter is the “keyword universe”.


           Why so useful ? See this example: I have the problem to evaluate in the fastest possible way the list of data base keys that are not yet used out of a finite universal set of possible keys. Keys out of this universe are randomly allocated and possibly removed, leaving holes that eventually must be reused. Using the missing function I could get what I need by simply evaluating FilterValuesSUB (keys_universe; List (database::key)), where keys_universe is a text field containing a predefined list, and database::key is the field where unique keys are stored.


           Are there other solutions ? Yes, many.

           But so far all solutions I found are at best based on related ad hoc tables, with operations that however fast they are, become slower and slower the higher the number of keys in the keys_universe. I expect that any solution based only on variables (and this is made possible only by the new function, as far as I know) would be much faster than solutions based on larger and larger tables. A possible workaround would also be to emulate this new function with a custom function built with a loop on variables and fields... but why not a neat and efficient new function in FMP ?


           Thanks for any comment on this



        • 1. Re: FilterValuesSUB - what about a new function ?

               There are custom functions that do this.

                I think your proposed function would also become slower and slower as the list of values increased.

               If you set a field in the table of IDs to 1 each time that you "used" one, the list of unused values would simply be the found or related set of all records that did not have a 1 in that field.

               Table::ConstOne ≠ IDTable|Used::UsedFlag

               would then make possible:

               List ( IDTable|Used::IDField )

               And ExecuteSQL could also be set up to list all values from  a table where a specified field was not set to a specific value.

          • 2. Re: FilterValuesSUB - what about a new function ?

                 OK. I agree there are other ways to solve my original problem. What you propose is basically what I used for many years (as a curiosity: I created the first data in this DB using Filemaker II in the eighties…! with different table structure and without relations): you suggest to keep a separate data structure or table where to mark which key is used and which is not.

                 The main problem I found with this approach is that eventually (in months or years) inconsistencies happen between the main data structure and the separate list of unused values: this is may be due to system crashes, conversion errors, DB upgrades errors, etc.. By blindly relying on the separate list then the allocation of the key might sooner or later disrupt the data base consistency; so a consistency check is required before each new key creation/deletion… and then why keep a separate structure if I cannot rely on that ?

                 Sorry for this digression. This is only to say that I appreciate your suggestion but what I’m actually looking for is a fast algorithm that does not rely on a parallel data structure. Now I use one that so far (today more than 5,000 keys) seems pretty good, but I still believe that a new function similar to what I propose might be useful also to other FMP developers and for many other purposes, since it seems to complete the set of logical operators on “¶” separated value lists.

                 Thanks a lot for your time


            • 3. Re: FilterValuesSUB - what about a new function ?

                        you suggest to keep a separate data structure or table where to mark which key is used and which is not.

                   Not quite, I suggested adding a single field for this purpose to a table that you should already have in your database. You would seem to need that in order to manage a "a finite universal set of possible keys" anyway. Though why you need such here is a bit puzzling--given the other built in tools such as auto-entered serial numbers and Get ( UUID ) with the unique values validation specified that appear to be much simpler methods for assigning unique IDs.

                   And I don't see why that would be anywhere near as fragile as you describe.

                   "system crashes, conversion errors, DB upgrades errors", etc can screw up your database, period. It won't just affect the method that I described it could very well result any any given part of your system--including the function that you want--not working correctly.

                   And please take another look at ExecuteSQL. From here, it looks like that function can already be set up to  produce the list of values that you want for this purpose.

                   PS. the first FileMaker Pro database that I created was in FileMaker Pro 2.5 on a Macintosh LC.

              • 4. Re: FilterValuesSUB - what about a new function ?

                Sorry, I hope I don’t appear to be headache… Well, FileMaker II existed before FileMaker Pro, so… I won !! (I’m obviously kidding - this just means I’m older).

                But to be serious, I’d really like to have comments and suggestions to improve my solution and I’m sure I’ll have them from you, since in the past I learned a lot from this forum.

                I’ll simply comment here your suggestions because I’m not sure this is the right place to explain my overall solution, but I’ll be glad to summarize my solution and DB structure, if you think it is useful and appropriate.

                About my “finite universal set of possible keys”. Sorry for the overblown words. I just meant a set of all possible keys of a specific Class which in theory is infinite (I should mention that keys in my DB are pairs Class-Number where Class is a mnemonic character: a=art, c=cd, v=video, etc and Number a unique value for that class). But it obviously becomes finite when I need a table to store some information about.

                You suggest either to add/use a field in a table I should already have, or to use the auto-entered serial numbers. The first solution is what I used in the past, and I was trying to abandon to avoid “duplication of information” (the information “this key is used in the DB” is already implied by the existence of a record using that key). In my opinion by avoiding duplication of information you also avoid potential inconsistencies. The second solution you suggest works perfectly with all other tables in my system, where the possible reuse of an older key is not required. Then this is the actual question for me: is key reusability still a requirement ? I’ll think of that.

                About Crashes. I agree with you: these may screw up the full data base, and in fact I regularly backup everything on my Macs. But other events or wrong use of the DB might cause discrepancies more easily avoided with my technique, I guess. 

                About ExecuteSQL function. I confess my ignorance. I just started studying it couple weeks ago and since you believe it might be useful I’ll certainly examine it in depth to solve my problem. I’ll keep you informed.

                Again thanks


                • 5. Re: FilterValuesSUB - what about a new function ?


                       I made a test using ExecuteSQL (“SELECT MIN(N.Numero) FROM Numeri as N WHERE N.Libero = 1”; “”; “”) in place of standard script steps to search and sort to get the MIN value for Numero. It works fine but I found out that it is from 20-30% slower than the standard script test, at least in my case (I made a simple benchmark repeating the same routine many times to be able to read the difference). Obviously the larger my "keys universe" is the slower the algorithm, and this is true for both script steps and Execute SQL. So I decided that I'll stick with my current solution.


                       (Note1: If you are interested in details of the solution: table Numeri is related to the main database DB Media and Numeri::Libero is a calculation field set to 1 if the corresponding key in DB Media exists. The standard script solution first searches for Numeri::Libero = 1, and this is the slowest step, then sorts results to get the min value of Numero in the first record of the set.

                       Note2: many times I had to force quit with alt-cmd-ESC my FMP 13v3 on Mac OS Mavericks while testing ExecuteSQL because it immediately spin locked for some SELECT statements even with Debugging window open... very bad behavior - still I didn't understand what kind of syntax error causes this - in any case this should not happen)

                  • 6. Re: FilterValuesSUB - what about a new function ?

                    I should mention that keys in my DB are pairs Class-Number where Class is a mnemonic character: a=art, c=cd, v=video, etc and Number a unique value for that class).

                         The ideal Primary Key should be:

                    1.           Unique
                    3.           Never Ever changed
                    5.           Devoid of any "encoded meaning"
                    7.           As simple and bullet proof as possible.

                         Your keys fail to measure up to items 3 and 4 and thus would not be used by me as primary keys. you may need such values to support legacy designs, labeling/reporting needs specified by your users, but they should not be used to match records in defined relationships except for certain specialty cases where the relationship is used for searching and/or specialty reporting basis. (You can set up a field for such a key in your parent table and refer to that field whenever you need to show this key on a layout.)


                    But other events or wrong use of the DB might cause discrepancies more easily avoided with my technique, I guess.

                    I must profoundly disagree with that. With a properly designed database, there should not be any possible "other events" or "wrong use" that can result in such discrepancies. But that's also why I've learned to set up Primary Keys that meet all 4 criteria and to avoid the use of keys that do not as much as possible. That allows a simple auto-entered serial number field to do the job and I do not have to design in any special value checking to ensure that the values are properly unique.

                    PS. Even with encoded meaning keys like you describe, a simple unique values field validation should do the job of ensuring unique values without the need for a special function to do so.


                    • 7. Re: FilterValuesSUB - what about a new function ?

                           OK. I fully agree with you about primary keys.

                           Then why I did differently ?

                           It is a legacy solution. When I first implemented this DB using dBase III many years ago my main purpose was to learn DB techniques and not all my solutions were optimal…

                           Only later I converted it into FM, and so on. Anyway at that time keys with “encoded meaning” were commonly used. I just kept that approach, but only in the main DB table. All other 8+ tables, added when the new relational model was provided by FMP, use the correct scheme that you define.

                           So I agree I should upgrade the main DB table to use a clean primary key and move the current key pair (class; number) to a separate field. I know this upgrade is a tricky job, because I did something similar earlier in other projects, but it might free me up from future problems on keys handling. Eventually I might address the point.


                           About your second comment, while in theory I agree with you on this point too, in practice I prefer to be able to first detect, and possibly also fix, inconsistencies that happen for some unwanted reasons (example: a record is deleted in a table making related tables no longer consistent). Obviously if you build bullet proof system nothing like that should ever happen. But: first I believe no SW system may be really bullet proof, even when strict SW engineering rules are applied, second and most of all my system for sure is not and will never be bullet proof… So I need to implement some consistency checks that allow me to easily find out if anything wrong happened in most critical points.


                           Well, we got into these very interesting exchange of opinions starting from my simple proposal for a new function. I understand that this new function is unlikely to be implemented, but I like to hope that eventually its advantage will be recognized.

                           BTW there are other more urgent problems to be fixed in FMP; e.g. “Image transparency not preserved in container fields” http://forums.filemaker.com/posts/041cdb204b?page=4.


                           Thanks so much for your time