7 Replies Latest reply on Mar 21, 2016 2:56 PM by AmberJeffery

    FInd help - should be simple?


      Hello all,


      I have been trying to get this to work all weekend and have had no luck, I feel like I have confused myself so much...


      When I created my database I set it up wrong, I know this and am currently working on a new one, however I do have people using my "bad" one until the other is finished.


      I need a simple search solution. I have one table ( I know I should use many with relationships..) on this table are two of the fields are "applicant1surname" and "applicant2surname".


      Often applicant 1 and applicant 2 have different surnames however are part of the same application.


      SO can I create a field to enter a name in to then when a "search" button is pressed it looks at both the surname fields to find a match and shows all records that have the name in either field?


      or do I "find" the name in one and if it no result it checks the other?


      I hope I have not confused you (I am) but how do I do this it seems so simple but I cant get it..


      All help is appreciated however telling me it is a structure problem is not helpful I just need to know if It is possible in the structure I have.


      Many many thanks


        • 1. Re: FInd help - should be simple?

          You do have a bad data structure, you should have an applicants table, with a single surname field that you can search on, and a separate indicator field indicating if it's applicant one or two.


          But, since you've already set it up, you can search in both surname fields using a third field, and a script that searches them.


          Say for instance you have a field "searchName", that you setup. The script for a button would be:

          Set Variable [ $name ; yourTable::searchName ]

          Enter Find Mode [ no pause ]

          Set Field [ yourTable::applicant1surname ; $name ]

          New Record/Request

          Set Field [ yourTable::applicant2surname ; $name ]

          Perform Find

          You can also do those steps manually by entering find mode (cmd/ctrl + F), typing in surname1, hitting "new request" (cmd/ctrl + N), typing in surname2, and then hitting enter. The above is just the automated "script" of that.


          Of course if you had just a single field in an applicants table, you could search just one field and it would be much easier.

          • 2. Re: FInd help - should be simple?

            Thanks Mike Beargie.


            I am a complete novice and still learning everyday so hopefully when I create my next one I will have it all down pat. I wont be rushing to get people to use my next one until it is all complexly right.


            SO I entered the script however mine looks different to yours..



            Mine has Value::  in the "Set Variable" and in "set field" I cant seem to add the ; $name onto mine. I'm sorry i'm such a novice, what am I doing wrong?


            Its really hard to come on here and feel so dumb, its very intimidating..

            • 3. Re: FInd help - should be simple?

              Welcome to the intimidated club! I'm a member too.


              You haven't set it up quite like Mike Beargie described.


              You set the variable $name but you didn't use it


              Set Field [Initial Enquiry::Surname; $name] and then the same for Set Filed [ Initial Enquiry::Partner Surname; $name]


              In the Set Variable dialogue choose the fields you want to set as you have already done but beside Calculated result click on specify and for the calculation type in $name


              That should do it.

              Screen Shot.png

              • 4. Re: FInd help - should be simple?

                OMG I nearly cried!!


                It worked, I got it!!  thank you thank you thank you



                I promise when I do this new one it will be set up properly at the beginning

                • 5. Re: FInd help - should be simple?

                  Have you looked at the FM training series. It will likely answer a lot of questions you might have and give you many different ideas on how to build your databases.


                  Database Skills, FileMaker Pro Training | FileMaker


                  There was another recent discussion on database design with many useful links as well


                  Help needed with DB design structure

                  • 6. Re: FInd help - should be simple?

                    Don't forget the many, many excellent FileMaker and Database learning references Beverly listed in recent threads.


                    There's also a dedicated Database (and FileMaker itself) training series on Lynda.com for various FileMaker versions, including 14.


                    If you don't get the database design right and spend the requisite amount of time there, everything that follows can be very painful. Think of the construction metaphor: lay the correct foundation FIRST.   (Have blueprints, meaning requirements, other documentation, ...., etc., before "diving in").


                    - m


                    P.S. Implementation should be the EASY part. If you're trying to figure out things as you go, slow down, step back and do some design or more analysis.

                    • 7. Re: FInd help - should be simple?

                      Thank you all, I plan on immersing myself before stepping off next time.


                      I have read on here that it is an age old problem, what started out very small and basic for just me alone ( so it worked well enough for what I wanted) has grown as others saw it and decided they needed this and then keep adding on...


                      It seems to have gotten bigger than Ben Hur LOL