8 Replies Latest reply on Oct 23, 2009 9:33 AM by philmodjunk

    If Statement with Multiple Field Queries

    nholmes2009

      Title

      If Statement with Multiple Field Queries

      Post

      Hi everybody

       

      I am new Filemaker user with some experience of MS Access and am slowly making the transition! I'm running FMP10 on a Powerbook G4.

       

      I have a question to which I suspect the answer will be in the design of my database, but I thought I'd throw it out here before attempting to reconfigure the webform.

       

      I have four fields on every record to describe what someone is looking for (it's not a dating site, but I guess the functionality here is similar!) - student, professional, part-time, and doesn't matter. All of these evaluate to a true or false (Yes/No) value. Every record (1 per person) also has a multiple choice occupation field so the person can describe their occupation.

       

      Predictably, I'm trying to get filemaker to look at the record, evaluate the four fields together, and return a set of matching records based on what's been selected.

       

      I have managed to get it to evaluate the final field (doesn't matter) and return all types of occupation. But aside from listing all of the possible combinations of all four boxes in an If statement, I don't know how to evaluate them all together to return, for instance, only students and professionals but not part-timers (if that had been selected).

       

      Any advice on the right type of query, or the database structure more generally, would be appreciated.

       

      Kind Regards

       

       

      Nick 

        • 1. Re: If Statement with Multiple Field Queries
          ninja
            

          Howdy,

           


          nholmes2009 wrote:

           

          I don't know how to evaluate them all together to return, for instance, only students and professionals but not part-timers (if that had been selected).

           


           

          For those who are students AND professionals: 

          If (Table1::Student = "Yes" & Table1:: Professional = "Yes")

           

          For those who are students OR professionals: 

          If (Table1::Student = "Yes" OR Table1:: Professional = "Yes")

           

          To FIND those who are students AND professionals:

          Enter find mode

          SetField (Table1::Student ; "Yes")

          SetField (Table1:: Professional ; "Yes")

          Perform Find

           

          To FIND those who are students OR professionals:

          Enter Find Mode

          SetField (Table1::Student ; "Yes")

          New Record/Request

          SetField (Table1:: Professional ; "Yes")

          Perform Find

           

           

          Those are pretty simple answers for an If statement or a find...I figure what you are trying to do is probably a bit more complex.  What would you be doing to/with the records if they meet such criteria?  A more thorough answer might be had...


          • 2. Re: If Statement with Multiple Field Queries
            nholmes2009
              

            Hi Ninja

             

            Thanks for the response; as an aside, wouldn't the second option (OR) create a new blank record? Or is it just creating another search option?

             

            I'm actually trying to do something a little past my limited abilities (but you don't learn if you don't try, right?!) - I'd like to create a mail bulletin that will be emailed regularly to people according to what they are looking for. So, people register, their details and their preferences are uploaded into filemaker, and then filemaker creates a mail merge email template to each person daily and includes details of the other people on the database that match their requirements. So if people A,B and C register, A gets an email with the details of B and C, B gets an email with A and C, C gets A and B (assuming they all match each other's age, profession requirements), etc etc.

             

            So, I'm assuming that I need to give Filemaker a loop, where it does the search, produces the email, sends it to the outbox, then moves on to the next person...

             

            Let me know if this isn't clear... I'm not sure I would understand it from the garbled description above!

             

            Thanks again :-)

            • 3. Re: If Statement with Multiple Field Queries
              ninja
                

              Howdy nholmes,

               

              Actually, you made it pretty clear, thanks.

               

              You would want to use the second "If" statement in your script...If (A or B).

              The looping script you're after is fairly complex (but certainly possible).

               

              I can't help much with the emailing part as I've not had cause to ever work on an email campaign...there are loads of posts on the forum about it though.

               

              Rather than brute force my way through the script, I'll take a pause and let a more elegant designer chime in.  If not for a while, I'll walk you through the looping script.  In that time, I'll bet you'll have come up with a couple variations on your own!

               


              nholmes2009 wrote: 

               wouldn't the second option (OR) create a new blank record? Or is it just creating another search option?

               


               

              In browse mode, it would be a New Record and you would be correct about the blank.  In Find mode, it's a new request...it would be treated as an "or" as in 'Find this or Find that'.
              • 4. Re: If Statement with Multiple Field Queries
                philmodjunk
                  

                Here's a rough outline of a single loop script that should do what you want.

                 

                Go To Layout [Layout referring to the table you want to search for matching records]

                Show All Records

                Go To Record [first]

                New Window //open a new window to display the results of your find so that the current found set is not affected by it

                Select Window [//select your original window]

                Loop

                  SetVariable [$Choices ; Value: YourTable::YourCheckBoxField]

                  Select Window [//Select your new window]

                  Enter Find Mode[]

                  Set Field [YourTable::YourCheckBoxField; $Choices]

                  Set Error Capture [on]

                  Perform Find []

                  Set Error Capture [off]

                  If [get (foundcount) = 0]

                     #Do whatever you need when there are no matches

                  Else

                     #Use found set to create your email

                  End If

                  Select Window [//select your original window]

                  Go To Record [Exit after last; next]

                End Loop

                 

                • 5. Re: If Statement with Multiple Field Queries
                  nholmes2009
                     Great, thanks guys. Let me take a look tonight and see how it goes - I'll report back tomorrow!
                  • 6. Re: If Statement with Multiple Field Queries
                    nholmes2009
                      

                    OK, I've had a play this morning with some mixed results.

                     

                    The loop script I tested with a find field that was working as it should be - find gender. It's easy because the "What Gender I want" field is a Male/Female/Doesn't Matter drop down (i.e. there is only one answer) and the "Gender" is a Male/Female dropdown. The loop  worked perfectly, producing results in a new window (as yet unformatted) exactly as I needed, so thanks for that one.

                     

                    However, (and apologies for this), I'm still struggling with the same question; although I think now that the answer may lie in creating variables. The problem is that "What Are You looking For?" is four distinct fields - a professional field that evaluates to YES/NO, a student field that evaluates to YES/NO and unemployed and part-time fields doing the same. The "What are you?" field, however, is a drop down Professional/Student/Part-time/Unemployed. So if I'm looking for a student or unemployed person, both of those "What are you looking for?" fields would be Yes, but professional and part time would be No and I'd need filemaker to filter the records by the "What are you?" field to show the answers "Student" or "Unemployed". 

                     

                    Apologies if I'm still not understanding, but I can't see how to apply the suggested if statement above to this search, bearing in mind that it has to evaluate the contents of four distinct fields before applying a filter/search to display the results as applied to only one field? The only way I can think of to do this is to say (in proper language obviously).

                     

                    If Student = Yes AND Professional = No AND Part-time = No AND Unemployed = No

                    Then

                    Search "What are you?" for "Student"

                    But

                    If Student = Yes AND Professional = Yes AND Part-time = No AND Unemployed = No

                    Then

                    Search "What are you?" for "Student" AND "Professional"

                     

                    And then to rewrite the if statement in every possible binary permutation of the four fields. 

                     

                    Please, please tell me I'm missing an obvious function here?!?

                     

                    Thanks for all the help! 

                     

                    • 7. Re: If Statement with Multiple Field Queries
                      ninja
                        

                      Yep,

                       

                      That's why I took a break to let someone with an elegant answer have a crack at it.  The searching part is easy, the defining your search criteria is a bit messy.

                       

                      Thinking out loud: I understand that if I am "A" and i say yes to A, B and D that the find for me should resulting all B's and D's.  A's not included because I am an A, C's not included because I did not ask for them (C=No).  This is correct?

                       

                      Perhaps the way toward this is some combination of constrain found set and/or omit records.

                      Starting with all records, I can omit my category...that leaves me with B,C & D.

                      If My::type = A, setfield A=yes & omit

                      If My::type = B, setfield B=yes & omit etc...

                      ...

                      Then look at B's and omit if B=No

                      Then look at C's and omit if C-no

                      Then with D's.

                      Wouldn't that leave me with a found set matching what you're after?

                       

                      Another way perhaps,

                      Cull my own category out then...

                      Enter find mode

                      If ME <> A and A=Yes

                        Setfield A=Yes

                      Endif

                      New Record Request

                      If ME <> B and B=Yes

                         Setfield B=yes

                      Endif

                      New Record/Request

                      etc.....

                       

                      I'm not sure if the blank record/requests would hash things up, but then I've never intentionally tried it.  This could end you up with your appropriate found set.  If you move from 4 fields to 20 fields...the script will get long/slow/ugly in a hurry.

                       

                      Phils answer provides the looping that would be required, and I think one of the two approaches here (or modifications of them) might get you the rest...I was hoping for something more elegant.

                       

                      Does that make any sense to you?

                      • 8. Re: If Statement with Multiple Field Queries
                        philmodjunk
                          

                        The details are always what make things a challenge.

                         

                        I understood you to have one text field formatted with check boxes. In your example of "what are you", you'd have one text field with the following custom values:

                         

                        Student

                        Professional

                        Part Time

                        Unemployed

                         

                        Because they're check boxes, a user can select multiple values simply by clicking more than one box. I understood that if "Student" and "Professional" were selected, you wanted all records in the table that also had both "Student" AND "Professional" selected.

                         

                        With those assumptions, the script should work.

                         

                        Now I understand that you have multiple fields with "Yes" and "No" radio buttons for each "What are you". This seems to be a needless complication that will complicate life for you in several areas. You might want to consider combining all these into a single text field formatted with check boxes to select the different categories by name.

                         

                        However, if you want my script to work using multiple fields, you simply have to add a set variable and set field step for each such field:

                         

                        SetVariable [$Professional ; Value: YourTable::YourProfessionalYesNoField]

                        SetVariable [$Student ; Value: YourTable::YourStudentYesNoField]

                        #and so forth for each such field.

                         

                        Lower down in the script:

                        Set Field [YourTable::YourProfessionalYesNoField; $Professional ]

                        Set Field [YourTable::YourStudentYesNoField; $Student ]

                        #and so forth for each such variable created earlier

                         

                        The only reason for the "new window" command is that I assumed you are using the contents of the current record to find matching values in the same table. Using a separate window to search for matching records keeps the found set of your main window unchanged so that the loop can step through each record repeating the find operation.