9 Replies Latest reply on Mar 20, 2017 2:23 PM by philmodjunk

    Perform a find & checkbox

    joana

      Hi there! I have a huge problem that I hope that someone can help me with.

       

      I decided to make of my fields a checkbox with several options (different materials to be used in cleaning). Everytime I make a search where I only select ONE option in my check box (eg.soap) records where I did not mark soap on the fiel with the check box appear as well (because they contain the word soap somewhere else). I want only the records where I marked soap in that checkbox (even if it is not the only ingredient). It can not be a search using the omit option because sometimes I have more than one option marked in this check box.

       

      Can someone please help me?

       

      Thank you!

        • 1. Re: Perform a find & checkbox
          mikebeargie

          you are not searching for unique values anymore, so it's not really possible to do that.

           

          You need to make the values unique. EG instead of:

          Soap

          Laundry Soap

          Dish Soap

           

          You would need:

          Generic Soap

          Laundry Soap

          Dish Soap

           

          Then you can search on "Generic Soap" instead of just "soap".

           

          There's some better ways to do this entirely, like having a table for "products used" instead of a checkbox, then use a lookup from a "products" table to link each of the products used to a unique product. This way you could not only search uniquely by a product, but also track inventory, do more accurate reporting and even have multiple kinds of plain "soap" that are linked by keys instead of by name.

          • 2. Re: Perform a find & checkbox
            joana

            it is my first time working with filemaker and I am a bit lost. So no way I can restrict my search to that specif value for that check box? It will always look for that term in the other fields of tat layout? that sounds strange to me. Thank you!

            • 3. Re: Perform a find & checkbox
              philmodjunk

              The problem is that "soap" isn't specific to the one value you selected. It also matches to others. There are ways to do this, but which is best depends on what you know how to do and what you need to accomplish.

               

              Even using a single field formatted with check boxes could well not be the best approach here. A portal where each item of your list is entered into a row might actually work better, but I really don't know enough about your solution's design to know if that is the case or not.

               

              You have another complication too:

               

              sometimes I have more than one option marked in this check box.

              If you select two options in this check box while in find mode what result do you want? All records with option 1 AND option 2 or All records with option 1 OR option 2. Both results are possible.

               

              If you wanted to find all records with "Soap" and not

              Laundry Soap

              Dish Soap

              etc.

               

              You could use a copy of your field without the check box formatting (edit box) and enter ==Soap as your find criteria. But this will only find records where "Soap" was the only check box selected.

               

              You might use a self join relationship matching records by this check box field, but that's probably not someone that posts: "it is my first time working with filemaker and I am a bit lost..." can do with a good chance of success.

               

              You might also check out ways to have a script perform your find:

               

              Scripted Find Examples

              • 4. Re: Perform a find & checkbox
                mikebeargie

                philmodjunk wrote:

                 

                You could use a copy of your field without the check box formatting (edit box) and enter ==Soap as your find criteria. But this will only find records where "Soap" was the only check box selected.

                In some cases this will fail though, since filemaker stores checkboxes as plain text on the backend and doesn't "auto-trim" when you uncheck values.

                 

                So if someone has two items checked off, and you uncheck one, it can leave leading or trailing line breaks which mess with the "==" string search operator.

                • 5. Re: Perform a find & checkbox
                  philmodjunk

                  So if someone has two items checked off, and you uncheck one, it can leave leading or trailing line breaks which mess with the "==" string search operator.

                   

                  This is not the case. Leading/trailing line breaks are not left behind when you clear a check box. Try it and see for yourself.

                  • 6. Re: Perform a find & checkbox
                    mikebeargie

                    Check off a few values on your record. Then change your field to an edit box instead of a checkbox. See how the data is stored in the database? It’s stored as a return delimited list of values.

                     

                     

                     

                    So if you are searching for “soap”, you are searching that text field for anywhere the word soap occurs. This is why it also returns where soap is a partial match, like “laundry soap” being included as well. This is also why my first suggestion of making sure your checkbox values are unique, and searching by the unique values, would work. You just need to make sure that each value is NOT a part of another value.

                     

                    EG Not this:

                    Soap

                    Laundry Soap

                    Broom

                    Dust broom

                     

                    But rather this:

                    Generic soap

                    Laundry soap

                    Generic broom

                    Dust broom

                     

                    Each checkbox value is no longer a portion of another checkbox value, so you can search on those values to find only the records where that value is checked.

                     

                     

                     

                    Take a look at the search examples that were provided. Just remember in filemaker, checkboxes and radios are just a mask for text fields, they are both stored as plain text fields.

                     

                    If you search for “==soap”, you are searching for where the ENTIRE field matches “soap”, meaning it would not return where any other values are checked off.

                    • 7. Re: Perform a find & checkbox
                      ByteTheBullet

                      Are you by chance using the Quick Find feature, where you are using the built-in search box located in the upper right corner of your layout window? If so, I can see two options for you. One is that you can go into layout mode and specifically exclude the other fields from being used in Quick Find. I don't recommend this, since you then exclude those fields from all Quick Finds. The second option is to go into Find mode, click the checkbox for "soap" (or whatever item in the checkbox set it is you're looking for, and then hit <RETURN>. The drawback to this option is that if your checkbox set includes "Soap", "Laundry Soap", and "Dish Soap", the search will find records containing any of them, not just the ones that have "Soap" selected. This is because behind the scenes, the checkbox set is just an interface feature. The actual data stored in the field matches the text listed next to the checkbox, and FileMaker by default performs "contains" searches. So a search for "Soap" returns any records where that field contains the string of characters "soap".

                      • 8. Re: Perform a find & checkbox
                        mikebeargie

                        I believe I encountered a system at some point of time that used scripting to add or remove values to a checkbox list and left these erroneous line breaks.

                         

                        I have also encountered it in systems where value lists are based dynamically on field contents, and someone at some time manually hit return while in a field and left the blank value there.

                         

                        I had always assumed it “could” occur in plain checkbox fields as well, so I always have treated it that way.

                         

                        True the native behavior auto-trims the field contents, however there are ways where line breaks can still manually creep into the data (importing old data, manual misentry, etc..) so I’ll continue to assume that data I’m unfamiliar with may suffer.

                        • 9. Re: Perform a find & checkbox
                          philmodjunk

                          Ok, but not if you are just clicking check boxes to add/remove values. Scripted modifications are only as good as the script that does the modification and if you allow direct editing via an edit box, all bets are off.