1 2 Previous Next 26 Replies Latest reply on Aug 31, 2015 8:17 AM by user9259

    execute sql compare value lists

    user9259

      2 Tables Recipes & Fruit. There is a relationship between Recipes & Fruit. Each recipe has a number of fruits.

       

      1) The user will selct different fruits via a script in List View which will be assembled into a global variable $$fruits

      2) There is a calculation field in recipes  List(frecipes::fruits)

       

      So I would like to return a list of Primary Keys from Recipes that have the selcted $$fruits.

       

      So Recipe 1 may contain

      Apples,

      Grapes,

      Lemons,

      Oranges

       

      $$fruits may contain

      Apples

      Lemons

       

      So $$fruits meets criteria. I would like to have a sql statement that assmbles the Recipe ID's across the total set of records. I was able to accomplish this with custom functions; but I didn't like the performance. Thanks so much for all the help!!!

        • 1. Re: execute sql compare value lists
          Menno

          What is your db-structure? How did you create the relationship? Maybe it looks like this?

          Schermafbeelding 2015-08-29 om 16.44.17.png

          In this schema here you can select all RecipeID's that have the selected FruitID, each time a Fruit is selected you can check as follows:

          Set-Variable ( $$recipeids ;

          ExecuteSQL (

          "SELECT RecipeID

          FROM RecipeFruit

          WHERE FruitID = ? " &

          If ( $$recipeids ≠ "" ;

          " AND RecipeID IN (" & Substitute ( $$recipeids ; [ ¶ ; "," ] ) & ")" )

          ; "" ; "" ; Fruit::ID )

          • 2. Re: execute sql compare value lists
            wimdecorte

            user9259 wrote:

             

            2) There is a calculation field in recipes  List(frecipes::fruits)

             

             

            You really should not do that... let the data live where it needs to be.

            • 3. Re: execute sql compare value lists
              wimdecorte

              Menno van Beek wrote:

               

              What is your db-structure? How did you create the relationship? Maybe it looks like this?

              Schermafbeelding 2015-08-29 om 16.44.17.png

              In this schema here you can select all RecipeID's that have the selected FruitID, each time a Fruit is selected you can check as follows:

              Set-Variable ( $$recipeids ;

              ExecuteSQL (

              "SELECT RecipeID

              FROM RecipeFruit

              WHERE FruitID = ? " &

              If ( $$recipeids ≠ "" ;

              " AND RecipeID IN (" & Substitute ( $$recipeids ; [ ¶ ; "," ] ) & ")" )

              ; "" ; "" ; Fruit::ID )

               

              Fully agreed with the need for a join table between fruit and recipe.  But given that we that then the query can be simpler (assuming that we let the user select all fruits as originally):

               

              "SELECT RecipeID

              FROM RecipeFruit

              WHERE FruitID IN (" & Substitute ( $$fruits; [ ¶ ; "," ] ) & ")"

              • 4. Re: execute sql compare value lists
                Menno

                No that's not what user9259 asked for.

                 

                He wants to select all Recipe's all of the selected fruits are used in.

                 

                Your calc selects any recipe's where any of the selected fruits are used in.

                • 5. Re: execute sql compare value lists
                  user9259

                  Here is a down and dirty demo of my data structure and what I am trying to accomplish. My selection script contains 2 custom functions.


                  Dropbox - fmdemo

                  • 6. Re: execute sql compare value lists
                    erolst

                    The need to add/remove a value to/from a list is frequent enough that you should put this functionality into a Custom Function; but you should only have one, and let the function itself worry whether it needs to add or remove, rather than add another conditional in the script.

                     

                    btw, if you want to build a list, use List(); no worry about extraneous CRs anymore.

                     

                    Finally, look into the attached modification to see the SQL that returns the recipes for the selected fruit(s).

                    • 7. Re: execute sql compare value lists
                      Menno

                      @ user9259: Change your script into just 2 steps 1) set-variable and 2) the refresh-window step.

                       

                      Set-Variable ( $result ;

                      Let ( [

                      fruitlist = ¶ & $$fruits & ¶ ;

                      fruit = ¶ & Fruits::id & ¶ ;

                      x = Substitute ( fruitlist ; [ fruit ; ¶ ] ) ;

                      n = Length ( x ) ;

                      y = Middle ( x ; 2 ; n - 2 ) ;

                      $$fruits = Case ( PatternCount ( fruitlist ; fruit ) = 0 ; List ( $$fruits ; Fruits::id ) ;

                      Fruits::id = $$fruits ; "" ;

                      Fruits::id ) ;

                      $$recipeids = If ( $$fruits = "" ; "" ;

                      ExecuteSQL (

                      "SELECT DISTINCT RecipeID

                      FROM RecipeFruits

                      WHERE FruitID = ? " &

                      If ( ValueCount ( $$fruits ) > 1 ;

                      " AND RecipeID IN (" & Substitute ( $$recipeids ; [ ¶ ; "," ] ) & ")" ) ;

                      "" ; "" ; Fruits::id )

                      )

                      ] ;

                      1

                      )

                       

                      This way you don't need a CF. If you select a fruit that already is in the selection, a new selection is started. This is due to the way the SQL is kept as simple as possible.

                      • 8. Re: execute sql compare value lists
                        user9259

                        Thanks so much for the demo! I think you misunderstood or I wasn't clear enouigh...

                         

                        So I need the recipe to meet ALL of the criteria...So in the provided image; there would be only 1 recipe that meets the criteria which is A. Both A & C have Apple & Orange; but only A has all three...Can u help with this? thank you!Capture.JPG

                        • 9. Re: execute sql compare value lists
                          erolst

                          I think this overly complex, not the least because you added the code for maintaining the selection list inline.


                          This is IMO not a good idea, because you can and will use that functionality in other places – and storing an abstracted package of discrete functionality under a name, ready to be called anywhere, is exactly what Custom Functions are there for. 

                           

                          Finally, note that you don't need to wrap your lists and elements in ¶s if you use FilterValues() instead of PatternCount().

                          • 10. Re: execute sql compare value lists
                            Menno

                            @user9259 Try the attached file, it is a mod of your own file, i think it works as you requere

                             

                            @erolst The thread-strater does not like to use a cf to solve the problem, the use of a cf is not a "must" nor does it need to be a "best practice". I think the solution can be found in many ways including cf's but that is not the main issue here. How to select records that are all linked to a specific selection of other records with an "AND" requirement instead of "OR" as in your example.

                            • 11. Re: execute sql compare value lists
                              erolst

                              Menno van Beek wrote:

                              @erolst The thread-strater does not like to use a cf to solve the problem, the use of a cf is not a "must" nor does it need to be a "best practice".

                              You should give people the solutions they actually need, not the ones they think they need.

                               

                              Using CFs is a best practice if you want to abstract stuff you need frequently (maybe I should write a CF with this reasoning and just refer to …) – and it certainly is a practice that helps avoiding jumbled code.

                               

                              I guess we have to agree to disagree here.

                               

                              Menno van Beek wrote:

                              How to select records that are all linked to a specific selection of other records with an "AND" requirement instead of "OR" as in your example.

                              Well, this little fact is something that the OP only clarified after I posted my sample, so I'm not sure what you're trying to tell me.

                              • 12. Re: execute sql compare value lists
                                user9259

                                Thanks so much..I did notice an issue when deselecting a fruit...It wants to deselect the wrong record when you have multiple selections. It is easily duplicated. Select Apple, Orange, Mango then deselect Mango. It does not deselect Mango but does deselect Apple Orange. I would only like to deselect the record I am triggering with scrip. THANK SO MUCH!

                                • 13. Re: execute sql compare value lists
                                  Menno

                                  I know that is an issue, but solving that would complicate the solution. The solution i provided only works well when selecting a few fruits after another. You start with one fruit and then look for records that have the previous fruit + the selected one.

                                   

                                  After that déselecting a fruit would result in having to select all the fruits all over again. It can be done, but then you actually should consider using a cf that does the selecting of all fruits and the corresponding recipes recursively.

                                  • 14. Re: execute sql compare value lists
                                    Menno

                                     

                                    Menno van Beek wrote:

                                    How to select records that are all linked to a specific selection of other records with an "AND" requirement instead of "OR" as in your example.

                                    Well, this little fact is something that the OP only clarified after I posted my sample, so I'm not sure what you're trying to tell me.

                                    I disagree with you there, I noted that little fact in my first reply and so you should have picked on that then. I was unsure about the schema, hence the screenshot there, but as it turned out his schema already was identical.

                                    1 2 Previous Next