1 2 Previous Next 27 Replies Latest reply on Oct 15, 2012 10:03 PM by thirdsun

    Multiple key filters and combinations and permutations... need a calculator/value generator

    thebridge

      Hi Everyone,

       

      I was hoping someone had a calculator or Custom function that would calculate and list all the combinations of a 7 key field foreign key calculation field.

       

      I have found some calculators on the internet but none that will do exactly what I need.

       

      What I am hoping for is the ability to list the 7 fields and the blank/empty field (8 total fields) and have the calculator generate a list of all combinations that I could then copy/paste into my calculated foreign field.

       

      The combinations will maintain this field order

       

      The primary key calculated would look like this:

       

      __kp_g_FilterItemClass1& __kp_g_FilterItemType1& __kp_g_FilterColor1&__kp_g_FilterSeason1& __kp_g_FilterPerson1& __kp_g_FilterItemSize1& __kp_g_FilterGender1

       

      The calculated foreign key would start with:

       

      _kf_ItemClass & _kf_ItemType & _kf_Color &_kf_Season & _kf_Person & _kf_ItemSize & _kf_Gender

       

      Next line would include the empty value field in the first position:

       

      _kf_EmptyValueField & _kf_ItemType & _kf_Color &_kf_Season & _kf_Person & _kf_ItemSize & _kf_Gender

       

      Following this pattern, and if my calculations are correct I will end up with 128 lines of combinations.

       

      Can anyone help with this?

       

      here is a link to calculator/value generator that almost works:

      http://textmechanic.com/Permutation-Generator.html

       

      thank you

       

      Robert Bloomfield

        • 1. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
          comment

          What are you hoping to achieve with the result of such calculation?

          • 3. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
            comment

            I am afraid that's not clear to me. I am having trouble imagining a situation where such key would be required. Perhaps if you explained the purpose behind this exercise, a better method could be found.

            • 4. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
              thebridge

              Hi Michael

               

              I want to filter an inventory database using 7 global filter fields where any or all can be used to create the compound value in a relationship as the primary key.

               

               

               

              The global filter field values will combine in the calculated primary key  field and form a relationship with the unstored calculated foreign key and display those inventory records that match those combined values in the primary key.

               

               

               

              does this help? This is multi-key portal filtering 101 and I have it working with 5 filter keys, the problem is that every time I want to add in another filter key, the possible combination values double and it is getting too unwieldy to do manually.

               

               

               

              respectfully

               

              Robert Bloomfield

              • 5. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                Stephen Huston

                Hi Robert

                 

                It sounds like you should build the filter as a calculation using each of the possible filter fields as sequential tests:

                 

                ( isEmpty (filter1) or patternCount (fieldA_in_record ; filter1 ) ) and

                ( isEmpty (filter2) or patternCount (fieldB_in_record ; filter2 ) ) and (etc)

                 

                Then you can let the calc do the heavy lifting instead of having to pick something other than the filters to apply.

                 

                Stephen Huston

                • 6. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                  abridgesolution

                  Hi Stephen

                   

                   

                   

                  here are the combinations for a 5 filter Foreign key. How do you see your method working?

                   

                  understand the combinations will double with each additional filter field

                   

                  the field named __kp_filterflag serves as the potential empty value

                   

                   

                   

                   

                   

                  /*        THIS IS THE START OF 1st thru 4th  FILTER     */

                   

                  __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag  & __kp_FilterFlag  & __kp_FilterFlag & inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                   

                   

                   

                   

                  inv_ItemClass & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                   

                   

                  __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary&  inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                   

                   

                   

                   

                  inv_ItemClass & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag  & inv_DescColor_Primary& inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                  inv_ItemClass & inv_ItemType &  inv_DescColor_Primary& inv_DescSeason & __kp_FilterFlag & "¶" &

                   

                   

                   

                  /*        THIS IS THE START OF 5TH FILTER     */

                   

                   

                   

                  __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                   

                   

                  __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                   

                   

                  __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag & kfPBID_AssignedTo & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                  __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary&  inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                   

                   

                   

                   

                  inv_ItemClass & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & __kp_FilterFlag  & inv_DescColor_Primary& inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                  __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                   

                  inv_ItemClass & inv_ItemType &  inv_DescColor_Primary& inv_DescSeason & kfPBID_AssignedTo

                   

                   

                   

                  now double all those for the sixth field

                   

                  now double them again for the 7th field

                   

                  see why I want to auto generate?

                   

                  This is a fairly standard practice for portal filtering… perhaps not 7 filter fields but even 4 or 5 become a manual bear. a tool to do this would serve the community well

                   

                   

                   

                  thanx

                   

                  Robert

                  • 7. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                    abridgesolution

                    Hi Stephen

                     

                    here are the combinations for a 5 filter Foreign key. How do you see your suggestion method working?

                     

                    understand the combinations will double with each additional filter field

                     

                    the field named __kp_filterflag serves as the potential empty value

                     

                    /*        THIS IS THE START OF 1st thru 4th  FILTER     */

                     

                    __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag  & __kp_FilterFlag  & __kp_FilterFlag & inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary&  inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag  & inv_DescColor_Primary& inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    inv_ItemClass & inv_ItemType &  inv_DescColor_Primary& inv_DescSeason & __kp_FilterFlag & "¶" &

                     

                    /*        THIS IS THE START OF 5TH FILTER     */

                     

                    __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & __kp_FilterFlag & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & inv_ItemType & __kp_FilterFlag & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag & __kp_FilterFlag & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & __kp_FilterFlag & inv_DescColor_Primary&  inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & inv_ItemType & __kp_FilterFlag  & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & inv_ItemType & inv_DescColor_Primary & __kp_FilterFlag  & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & __kp_FilterFlag  & inv_DescColor_Primary& inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    __kp_FilterFlag & inv_ItemType & inv_DescColor_Primary & inv_DescSeason & kfPBID_AssignedTo & "¶" &

                     

                    inv_ItemClass & inv_ItemType &  inv_DescColor_Primary& inv_DescSeason & kfPBID_AssignedTo

                     

                    now double all those for the sixth field

                     

                    now double them again for the 7th field

                     

                    see why I want to auto generate?

                     

                    This is a fairly standard practice for portal filtering… perhaps not 7 filter fields but even 4 or 5 become a manual bear. a tool to do this would serve the community well

                     

                    thanx

                     

                    Robert

                    • 8. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                      JimmainSolutions

                      Personally for clarity, size & possibly speed, I'd make 7 separate foreign key fields, each with the empty field value:

                       

                      _kf_EmptyValue & "¶" & _kf_ItemClass

                      _kf_EmptyValue & "¶" &_kf_ItemType

                      _kf_EmptyValue & "¶" &_kf_Color

                      _kf_EmptyValue & "¶" &_kf_Season

                      _kf_EmptyValue & "¶" &_kf_Person

                      _kf_EmptyValue & "¶" &_kf_ItemSize

                      _kf_EmptyValue & "¶" &_kf_Gender

                       

                      and corresponding 7 primary keys, each with either the filter value (if exists) or the empty value:

                       

                      _kp_FilterClass = Case (IsEmpty (_g_FilterClass), _kf_EmptyValue, _g_FilterClass)

                      _kp_FilterType = Case (IsEmpty (_g_FilterType), _kf_EmptyValue, _g_FilterType)

                      _kp_FilterColor = Case (IsEmpty (_g_FilterColor), _kf_EmptyValue, _g_FilterColor)

                      _kp_FilterSeason = Case (IsEmpty (_g_FilterSeason), _kf_EmptyValue, _g_FilterSeason)

                      _kp_FilterPerson = Case (IsEmpty (_g_FilterPerson), _kf_EmptyValue, _g_FilterPerson)

                      _kp_FilterSize = Case (IsEmpty (_g_FilterSize), _kf_EmptyValue, _g_FilterSize)

                      _kp_FilterGender = Case (IsEmpty (_g_FilterGender), _kf_EmptyValue, _g_FilterGender)

                      • 9. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                        thebridge

                        Hi MainSolutions,

                        thank you for your reply

                         

                        are you saying to:

                        create 7 calculated foriegn and combine them into one?

                         

                        create 7 calculated primary and combine them into one?

                         

                        and then relate the two combined primary and foreign key fields?

                         

                        two other questions:

                         

                        why the carriage return in the foriegn examples?

                         

                        i was under the impression that with case statements, as sone as one case was met, the following would not be evaluated and thus the "any combination thereof" woudl not be fully evaluated...

                        is this not so?

                         

                        Thank you

                        robert

                        • 10. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                          comment

                          thebridge wrote:

                           

                          This is multi-key portal filtering 101

                           

                          I am afraid I must have missed that class... Actually, this doesn't seem to be "portal filtering" at all (as introduced in version 11) - only an issue of building an AND relationship while allowing some fields to be empty. If so, I would use calculated keys on both sides of the relationship: on the "parent" side, use =

                           

                          Case ( IsEmpty ( gFilterField ) ; "Any" ; gFilterField )

                           

                          On the "data" side, use =

                           

                          List ( KeyFiled ; "Any" )

                          • 11. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                            JimmainSolutions

                            As Michael noted if you're running FMP11 you don't need a special relationship for portal filtering. However it also sounds like you're using the pre-FMP7 technique of building a relationship with just a single field-pairing. Assuming FMP7 or later, instead make a separate field-paring in the portal relationship for each of the 7 key fields.

                             

                            _kp_FilterClass = _kf_ItemClass

                            _kp_FilterType = _kf_ItemType

                            _kp_FilterColor = _kf_ItemColor

                            _kp_FilterSeason = _kf_ItemSeason

                            _kp_FilterPerson = _kf_ItemPerson

                            _kp_FilterItemSize = _kf_ItemItemSize

                            _kp_FilterGender = _kf_ItemGender

                            • 12. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                              abridgesolution

                              Hi Michael,

                               

                              You are correct about the “portal filtering” as introduced in FMP11. I apologize for the confusion in terms.

                               

                               

                               

                              However, this technique has been used for years in FMP (pre FMP 7) using multiple key fields to allow a user to filter records in a portal.

                               

                               

                               

                              the user can place values in any of the key fields, in any combination and it will filter records in the inventory database that match those value combinations. (drill down)

                               

                               

                               

                              If you were to describe this such as I am trying to do, how would you have described it? (honest question)

                               

                               

                               

                              Thanks for your help

                               

                               

                               

                              Robert

                              • 13. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                                abridgesolution

                                You are right in the assessment that I have been using this technique since pre-FMP7 and this will be implemented within a FMP 11 solution.

                                 

                                 

                                 

                                Let me ponder what you both have written and I respond later… please feel free to respond prior

                                 

                                 

                                 

                                in response to your example below, how do I factor in that any of those fields could be empty.

                                 

                                 

                                 

                                you do understand that what actually filters the records is the combination value of those seven primary fields whether a value is within any ,all or any combination thereof, correct?

                                 

                                 

                                 

                                thanks so much to both of you for your time… I am beginning to see the light post FMP 6 with this technique

                                 

                                 

                                 

                                Robert

                                • 14. Re: Multiple key filters and combinations and permutations... need a calculator/value generator
                                  comment

                                  abridgesolution wrote:

                                   

                                  If you were to describe this such as I am trying to do, how would you have described it? (honest question)

                                   

                                  As I already did: building an AND relationship (or If you prefer a more technical definition, a conjunction of predicates), while allowing some predicates to be empty.

                                  1 2 Previous Next