9 Replies Latest reply on Aug 14, 2010 4:16 PM by LaRetta_1

    How do I filter records based on two fields contents?

    basilisk2

      Title

      How do I filter records based on two fields contents?

      Post

      I want to Find those records that have any content in one field (a text field), and that have no content in another (a text field formatted as a Checkbox with possible entries of Yes or nothing - ie, just one box). This will be part of a script, following on from the "Browse" command.

      I have tried using * to denote "any" but the find results in some fields that are blank, while I cannot search for records that "do not include" a specific entry. I also can't find records based on a cross or no cross in the checkbox.

      Where am I going wrong?

      TIA

        • 1. Re: How do I filter records based on two fields contents?
          raybaudi

          Hi

          if you need to find records with an AND search on those fields:

          Enter Find Mode [ ]
          Set Field [ YourFirstField ; "*" ] // this will find any record with YourFirstField NOT empty
          New Record/Request
          Set Field [ YourSecondField ; "=" ] // this will find any record with YourSecondField empty
          Perform Find [ ] // the combined requests will make an AND search, finding ONLY records with both criteria

          • 2. Re: How do I filter records based on two fields contents?
            LaRetta_1

            If this is a manual find, one cannot find on "=" in a checkbox so you would manually do this once entering find mode:

            1) Type * in the text field

            2) Select Requests > Add New Request

            3) Check your checkbox and then specify 'Omit'

            4) Perform Find

            • 3. Re: How do I filter records based on two fields contents?
              basilisk2

              Thanks for both of your answers, but I can't get either to work. 

              If I do a manual find, it tells me I have no records meeting the criteria, which is wrong.

              In total I have 90 records. 25 of them have an entry in Text Field "Action" and 6 of those have unfinished Actions ie do NOT have "Yes" in the "Finished" Field (Label="Done") which means it should have a null value (be empty).

              When I do the manual find as you suggested, LaRetta, (putting * in the field, not @) it tells me I have no records. So, I went back to "Show All", and ran the two parts of the manual Find separately. This gave me 9 records (there are in reality 25), one of which has nothing in at all (the find should only show those records that DO have an entry. If I ran the Find the other way, checking the "Yes" checkbox  in the "Finished" Field and the "Omit" button the search gives me 83 entries ie all those records that don't have Finished marked because they don't have any Actions to be done.

              As for the script version, Ray, do I insert that into my Report script, or somewhere else? If so, where?

              Thanks again for the help! :-)

              • 4. Re: How do I filter records based on two fields contents?
                LaRetta_1

                The technique I suggested does work so I suspect something else is going on.  Are either of these fields related fields?  Or are they in portals?  When you view the fields in layout mode, do they have :: in front of the field name?  Something isn't right somewhere.  If the manual find won't work, neither will the scripted one.

                Can you post up a link to your file for us to check it?  If you don't want to do that, can you zip and send the file? 

                I'm unsure where you got the @ ... it certainly wasn't something I ever suggested.  An asterisk is used (without quotes) to find anything in a field (not empty) and a simple = finds empty fields.  But you cannot find using = on a checkbox which is why I suggested using omit.

                • 5. Re: How do I filter records based on two fields contents?
                  basilisk2

                  Yes, the Report does use related fields. It takes the Client Name from the dClients table, and the Action To Dos from the related table. The report is based on the table, dContactEvents, and adds the Client Name from the related table dClients. I could show you the field relationships if I could upload an image, but for some reason that seems to be disabled on anything other than a first post. It's straightforward though. Clients is the main table, joined One to Many to dContactEvents.

                  • 6. Re: How do I filter records based on two fields contents?
                    LaRetta_1

                    You can upload your file to any free file sharing site such as 4Shared.com.  It is free.  Then provide a link here to it.

                    However, you haven't said specifically where the two fields you are searching reside.   Remember that the results of a search of related records will only show the number of LOCAL records which match the search request.  I suspect your finds are acting logically but need specific information.  File would be best. :^) 

                    • 7. Re: How do I filter records based on two fields contents?
                      basilisk2

                      The fields I am performing the find on are both in the "Many" table, dContactEvents; the results should show those two fields "dContactEvents.Action" and "dContactEvents.Finished", plus the dContactEvents.Date field in dContactEvents and the ::ClientName field from the "One" table, dClients.

                      • 8. Re: How do I filter records based on two fields contents?
                        basilisk2

                        Due to having live data in the db which I do not know how to duplicate or remove, I am unable to share the original db with you. So, I copied over the Tables, tried to copy the Layouts, Value lists and reports setup - and of course, it worked as intended.

                        Can you please tell me how to send you the original db without the data in?

                        Thanks.

                        • 9. Re: How do I filter records based on two fields contents?
                          LaRetta_1

                          Select File > Save a Copy As and specify clone (no records).  YOu will need to upload the file somewhere - this site doesn't accept uploads directly.  Then provide a link here to your file.  Please specify on its opening layout what layout and fields you are having problems with, and point to your specific script as well.