10 Replies Latest reply on Jul 19, 2012 8:19 AM by comment

    Find on a calculated fied that is a list

    dloughlin

      I have a calculated that is set to to a list of values in a field in a related table using the following calculation:

       

      List ( ProgramSchools::School )

       

      Performing a find on that field is causing a problem.

      If there are two realted records, and the related field has the values of AB and A.

      Using List, you get the related values separated by carriage returns.

      The calculated field would then have the value of AB¶A.

      Another record may have a calculated value of AB¶C.

      If I want to perform a find to find records that have the value of just A, I cannot do it.

      If I set the find value to =A, I find both records.

      If I set the find value to ==A, I won't find any records.

       

      I am using the API, but I get the same results when using FileMaker Pro 11

       

      I'm stumped on how to accomplish this without redesigning the database.

        • 1. Re: Find on a calculated fied that is a list
          beverly

          Please specify "the API"?

          Beverly

          • 2. Re: Find on a calculated fied that is a list
            comment

            dloughlin wrote:

             

            I have a calculated that is set to to a list of values in a field in a related table using the following calculation:

             

            List ( ProgramSchools::School )

             

            Performing a find on that field is causing a problem.

             

            Why don't you search the related field (ProgramSchools::Schoo)l directly?

            • 3. Re: Find on a calculated fied that is a list
              dloughlin

              Thanks for your responses.

              I am using the API that installs with Filemaker server advanced 11.

              I don't do the search on the related field, as that is not available when I am doing the find.

               

              I have come up with a work-around, where I created a second calculated field in which I pick the values using GetValue, and separate them with the characters 'xx', and then do the search on the new field and bracket the value I'm looking for with "xx'.

              In the example I gave in the original post, the new field would have the value of xxABxxAxx, and the second record would have the value of xxABxxCxx.

              finding xxAxx would find the first record, but not the 2nd.

              It's not pretty, but it works.

              • 4. Re: Find on a calculated fied that is a list
                comment

                dloughlin wrote:

                 

                I don't do the search on the related field, as that is not available when I am doing the find.

                Why not? For example:

                 

                Enter Find Mode []

                Set Field [ ProgramSchools::School ; "=A" ]

                Perform Find []

                 

                when performed from a layout of your parent table, would find all parent records that have at least one child record in ProgramSchools that contains the word "A".

                 

                 

                 

                Message was edited by: Michael Horak

                • 5. Re: Find on a calculated fied that is a list
                  dloughlin

                  The table I do the find on is periodically updated from a "working" database. It has no relationships. The calculation field is in the "working" database. In the find database it is a text field, that has been populated via an import.

                  • 6. Re: Find on a calculated fied that is a list
                    comment

                    dloughlin wrote:

                     

                    The table I do the find on is periodically updated from a "working" database. It has no relationships. The calculation field is in the "working" database. In the find database it is a text field, that has been populated via an import.

                     

                     

                    Well, then it's not a "calculated field" like you said in your original post - just a text field containing multiple values. In any case, searching for =A in this field, whether calculated or not, should find only records that contain the word "A". The record that contains "AB¶C" should not be found. Make sure your field is of type Text. Re-indexing the field might also help.

                    • 7. Re: Find on a calculated fied that is a list
                      dloughlin

                      Whether it was the actual calculated field or the imported value does not change the condition, It was just easier to explain that way. And you are correct, it would find the word A. I should have given in my example the values of "A B¶C" and "A B¶A". Once aagin I was trying to make it easier to explain and did a poor job of it. In this case trying to find the value A will either find both records or if you use ==A will not find either record. Said in another way, I want to find the value A, not the word A.

                      Sorry for the confusion.

                      • 8. Re: Find on a calculated fied that is a list
                        comment

                        dloughlin wrote:

                         

                        Whether it was the actual calculated field or the imported value does not change the condition,

                         

                        No, but it does change the situation. Because with a calculated field, the original values are still available for searching - as explained in my first two posts.

                         

                        I don't know why you decided to de-normalize the solution where you are doing the find, but obviously this carries a price. Though your calculation field could be probably simplified to =

                         

                        ¶ & Textfield & ¶

                         

                        which would enable you to search for "¶A¶"  -  or =

                         

                        "\"¶A¶\""

                         

                        if scripted.

                        • 9. Re: Find on a calculated fied that is a list
                          ryan

                          Have you tried doing a search using the asterik preceding and following the value "A"?

                          like this:  *A*

                           

                          This will find all records containing the value "A".

                          • 10. Re: Find on a calculated fied that is a list
                            comment

                            ryan wrote:

                             

                            Have you tried doing a search using the asterik preceding and following the value "A"?

                            like this:  *A*

                             

                            This will find all records containing the value "A".

                             

                            No, it will find all records containing the string "A". Value, in this context, means an item in a return-separated list.