7 Replies Latest reply on Feb 15, 2016 11:44 PM by kylerjphillips

    Find if two fields are the same

      Is there a find method I can use to see when two fields in a FileMaker table are the same?

        • 1. Re: Find if two fields are the same
          beverly

          Two different fields in the same record? or the same field in two records?

           

          If the first, the answer is not directly. You could create a calculated field

           

          = Exact ( Field1 ; Field 2 )

           

               https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.231.html#1075159

           

          The field would be "1" if the two fields match exactly (including case).

           

          beverly

          • 2. Re: Find if two fields are the same

            Three ways to do this that I can think of. Others may be able to refine or suggest still different ways:

             

            1. Create a simple "=" relationship in the relationship graph tab.

             

            First, create a new Table Occurrence (TO) in the database relationship graph for the same table you want to check equal fields for. Then, drag a line between the two fields you're looking for being equal. Then, create a new layout that shows records from the new TO (table occurrence) you created in the relationship graph.

             

            But, here's the trick, add fields from the original layout to this new layout. Then, when you open the new layout based on the new table occurrence, you'll only see the matching record(s). Now, what happens to me is that I see all the records regardless, they're just blank. The only non-blank record is the matching one you want.

             

            So, to only have "matching" records show up, just do a Find and put "*" in for the Find criteria. Then, you'll only see records where the two fields match. You could automate that find on the "On Layout Enter" trigger or whatever, if needed.

             

            2. Create a simple SQL statement.

             

            I have a "Pets" play database. I have one field called "Owner" and another called "Pet Type". If I make both these fields = "Fred" for a sample record, and issue this SQL: select * from pets where Owner = "Pet Type"


            I will only see the matching record.

             

            Unfortunately, FileMaker doesn't give you a direct way, that I know of, to use SQL like you'd use a Find to directly filter records in a layout. But, you can use SQL in either the data viewer or you could even use it to create a calculated field (see 3., below) you could then use to join to a field in another table in the relationship graph (sort of a combination of ideas 1 and 2 here) to then filter records.

             

            Or, using the Data Viewer:

             

            So, if you're just trying to "see" the matching records, the SQL approach in the data viewer is probably all you need, at least as a viable searching option.

            ----

             

            3. Creating a calculated field based on SQL, then use that field to join another table to show matching record.


            In the relationship graph below, I have matched a calculated field based on a SQL statement to yet another table occurrence of the Pets database. This approach combines approaches 1, and 2, above.

             

            In any case, these are my initial ideas.

             

            Others here will no doubt suggest refinements to what I've written <smile> or suggest even better ways still to do what you want.

             

            What you display and what your exact requirements are for the "match" would dictate how you might use these sample ideas.

             

            HTH

             

            - m

            • 3. Re: Find if two fields are the same

              I didn't know about that function.

               

              Cool, thanks Bev!!

               

               

              - m

              • 4. Re: Find if two fields are the same

                I like Bev's answer better than mine.

                 

                But your application would dictate which might work best in a given situation.

                 

                It's cool to have all these approach options....one of the great things about FM.

                 

                Good luck.

                 

                - m

                • 5. Re: Find if two fields are the same

                  Thanks beverly

                   

                  Is there anything you don't know?

                   

                  This sort of works, how would I achieve a similar result if I have the following field instances:

                   

                  A request can only be accepted once thus only having one agency per _pkRequestId

                   

                  Accepted Requests::Agency Name

                   

                  However, declined requests can have multiple agencies that are associated with _pkRequestId

                   

                   

                  Declined Requests::Agency Name

                   

                  The below example might give you a better idea on what I'd like to achieve, so I want a field to appear with perhaps conditional formatting or a calculation whenever the Accepted By Agency appears in the Declined Requests portal table.

                   

                  Portal Row.PNG

                  • 6. Re: Find if two fields are the same
                    user19752

                    Do you want to hide Carewatch line in portal according to "Accepted By" field?

                    It is "filtering records in portal".

                     

                    But reading your explanation, there are two requests from Carewatch both declined and accepted. I can't get meaning of hiding in portal...

                    • 7. Re: Find if two fields are the same

                      Hi user19752

                       

                      Basically, I want to create a field whenever an agency that appears in the Accepted field also appears in the portal row somewhere?

                       

                      Kyler