7 Replies Latest reply on Oct 31, 2015 7:21 AM by gcardin

    How to find based on multiple criteria from a single table

    gcardin

      Let say I have a Database with 2 different tables in it for a pizza restaurant. The main layout will be called "Menu" and the other one will be called "ingredients" and contain all possible ingredients. The second layout will serve as a list. In the Menu layout, user will enter every type of pizza they have. My client wants a layout where he can find pizza based on two different things, type of crust and ingredients (multi choice, maximum 3 choices at the same time based on the same "ingredients" table).

       

      I am trying to have a popup window with 3 dropdown list in it where user can choose three different ingredients based on my single "ingredients table list".

       

      I can't figure how to do that. I think I would need to use some general variables?!?

        • 1. Re: How to find based on multiple criteria from a single table
          bigtom

          As just a menu it can be as simple as choosing a crust and three ingredients. You just need a field for crust and three for ingredients. Just use the same value list based on the ingredients table for all three choices.

           

          IF you are trying to turn this into an order it gets a little more complex but not too bad. maybe sub orders if you need more than one pizza on one ticket. But I would set it up with a 3 row portal with no scrolling to limit selection to 3 toppings and process the toppings as line items for each pizza. You would still use your toppings table for the value list but have another table "toppings_selected" to store each order.

           

          Globals could also work depending on how you intend to use them.

          • 2. Re: How to find based on multiple criteria from a single table
            gcardin

            Thanks for you answer!

             

            I need those 3 choices (ingredients) to be "temporary" and not permanent as I need them to do a FIND in my MENU layout.

             

            So I don't know if I need to create a new table for my searches or just create a new layout from my MENU table? Do I have to create 3 temporary variables? If yes, in wich table do I create them?

             

            I was thinking about adding 3 new fields in my MENU table (ingredient_no1, ingredient_no2, ingredient_no3) and use them as "global storage". After the user have choose is 3 ingredients, he will do the FIND based on those choices.

             

            What do you think about it? Any other idea?

            • 3. Re: How to find based on multiple criteria from a single table
              bigtom

              You can use global fields for this. It is a good practice to keep them in a separate table, but you can put them wherever. You may want to consider global variables($$ingredient1 and so on ) as they are a little easier to deal with in a script And might work well in this case.

               

              The actual use seems odd without more detail but I will trust you have this all worked out And know what you need. 

              • 4. Re: How to find based on multiple criteria from a single table
                bigtom

                jjust use a value list to set the glibal fields.

                • 5. Re: How to find based on multiple criteria from a single table
                  gcardin

                  In fact no I am not sure where I am going with this! That is what I am trying to figure out!

                   

                  I created a new table with 3 global storage fields. What is the relationship between the MENU table and that SEARCH table? Do I need a relationship between those 2 tables?

                   

                  Right now there is no relationship between those 2 tables. I have a field "ingredient1" showing values from another table (Ingredients). When I select an ingredient in INGREDIENT1, data in my SEARCH table is the right one.

                   

                  In my SEARCH layout, I added a text field with "<<$$ingredient1>>" but nothing appear. Do you know why?

                  • 6. Re: How to find based on multiple criteria from a single table
                    bigtom

                    Make a match/key field and relate it to the other table.

                     

                    You need to set variable $$ingredient1 the value of globalTable::ingredient1 after the value list selection is made.

                     

                    Run a script trigger OnObjectModify to execute the script.

                     

                    Using a variable may seem unneeded but that is my personal preference for scripts.

                    • 7. Re: How to find based on multiple criteria from a single table
                      gcardin

                      Yes thanks!!!

                       

                      I managed to make it work, pretty close to complete that "search" thing!

                       

                      Thanks again for your help!