1 2 Previous Next 15 Replies Latest reply on Apr 10, 2014 2:27 PM by philmodjunk

    Filtering Portal Record Does Not Produce Expected Result

    evanscl

      Title

      Filtering Portal Record Does Not Produce Expected Result

      Post

           See attached screenshot.

           I inserted a one-row portal (red border) filtered for "Acute Serum DEN":

      Human_Testing::cIllnessPhaseSampleTypeTestPerformed = "Acute Serum DEN".

           The 8-row portal above the one-row portal shows all of the records (not filtered). The expected result of the filtered portal is the record highlighted with a green oval: SampleType = "Serum", IllnessPhase = "Acute", and TestPerformed = "DEN" (i.e., "Acute Serum DEN".

           Instead, the portal filters out the record highlighted with a red oval: SampleType = "Serum", IllnessPhase = "Convalescent", and TestPerformed = "WEE IgM" (i.e., "Convalescent Serum WEE IgM).

           Why is the filter not working?

           I created a script to filter out records with "Acute Serum DEN", but it also filters out "Convalescent Serum WEE IgM":

      S1OtherMethodDENV =  If ( not IsEmpty ( Filter ( List (Human_Testing::cIllnessPhaseSampleType ) ; "Acute Serum DEN" ) ) ; Human_Testing::cIllnessPhaseSampleType ; " " )

           My goal is to make this script work. I just created the one-row portal to troubleshoot the script. How do I get this script to work?

            

      Filtering_Portal_Record.jpg

        • 1. Re: Filtering Portal Record Does Not Produce Expected Result
          philmodjunk

               If the text in TestPerformed is "Acute Serum DEN" then

          TestPerformed = "DEN" is False as

               "DEN" ≠ "Acute Serum DEN"

               You'll need to use one of these expressions to detect when the text in TestPerformed either contains DEN or is the last word in the field:

               Contains: patternCount ( TestPerformed ; "DEN" )

               Last Word: RightWords ( TestPerformed ; 1 ) = "DEN"

          • 2. Re: Filtering Portal Record Does Not Produce Expected Result
            evanscl

                 I'm not evaluating the text in TestPerformed. I'm evaluating the text in Human_Testing::cIllnessPhaseSampleTypeTestPerformed, which is a calculation field: IllnessPhase & " " & SampleType & " " & TestPerformed. Therefore, each of my six child records shown in the screenshot above will contain the following information in the field Human_Testing::cIllnessPhaseSampleTypeTestPerformed:

                   
            1.           Acute CSF POW
            2.      
            3.           Acute Serum CHIK
            4.      
            5.           Acute Serum DEN
            6.      
            7.           Convalescent Serum DEN IgM
            8.      
            9.           Convalescent Serum WEE IgM
            10.      
            11.           Convalescent Serum DEN

                 I want to filter the child records so that I only get records where Human_Testing::cIllnessPhaseSampleTypeTestPerformed = "Acute Serum DEN". Once I find that record, I want another field to record its SampleType if SampleType is not empty, and if SampleType is empty, I want the field to = SampleType_SpecifyOther:

            If ( not IsEmpty ( Filter ( List (Human_Testing::cIllnessPhaseSampleType ) ; "Acute Serum DEN" ) ) ;

            Case (

            not IsEmpty (Human_Testing::SampleType) ; Human_Testing::SampleType ;

            Human_Testing::SampleType_SpecifyOther 

            ) ;

            " ")

                 I don't even know if the above script will work because I can't get past the first Filter step.

            • 3. Re: Filtering Portal Record Does Not Produce Expected Result
              philmodjunk

                   The first thing that I see is that you appear to be using the Filter function instead of the FilterValues function.

              • 4. Re: Filtering Portal Record Does Not Produce Expected Result
                evanscl

                     Changing the Filter function to the FilterValues function had no effect on the result.

                     It can't be just the script. As I said in my first post, filtering the portal for a record that contains "Acute Serum DEN" is showing a record that contains "Convalescent Serum WEE IgM". I can understand me messing up the script, but filter the portal is pretty straightforward.

                • 5. Re: Filtering Portal Record Does Not Produce Expected Result
                  evanscl

                       I just noticed this. The record with "Convalescent Serum WEE IgM" was the first record added. So, it seems that FileMaker is pulling the information from the first record entered rather than the record that I am trying to filter for.

                  • 6. Re: Filtering Portal Record Does Not Produce Expected Result
                    philmodjunk

                         Calculation such as what you are using in your script ignore portal filters. They'll produce the same result whether you have a portal filter in place or not.

                         Your calculation has more issues. Remember that any reference to a Human_Testing from the context of your layout's table, (Patient Info was it?), unless used in an aggregate function such as List, sum, count... etc, refers only to the first related record--the first record that would appear in an unsorted, unfiltered portal to Human_Testing on your layout.

                         So in this expression:

                    If ( not IsEmpty ( FilterValues ( List (Human_Testing::cIllnessPhaseSampleType ) ; "Acute Serum DEN" ) ) ;

                    Case (

                    not IsEmpty (Human_Testing::SampleType) ; Human_Testing::SampleType ;

                    Human_Testing::SampleType_SpecifyOther 

                    ) ;

                    " ")

                         The text in green refers only to data from the first related record--not the first record in a filtered portal, nor the specific record that makes the expression  with IsEmpty and FilterValues evaluate as true.

                    • 7. Re: Filtering Portal Record Does Not Produce Expected Result
                      evanscl

                           I don't know what to do.

                      • 8. Re: Filtering Portal Record Does Not Produce Expected Result
                        philmodjunk

                             Warning: I am deliberately including less detail in my posts to this thread. That's not laziness or a desire to discourage you, I just think that it's time for you to rely less on my explanations and detailed "fixes" and more on learning for yourself how FileMaker works. That way, you can eventually manage to do this on your own with much less support from the forum.

                             You may want to invest some time in tutorials or other training materials to build up a better overall understanding of how FileMaker works.

                             That said, I'm not totally clear on what you want to accomplish as I can read this two different ways:

                             We can produce a calculation field similar to others we have set up in other threads where the field displays specific text or data if any one of the related records meets specified criteria. Or we can set up a filtered portal that only displays records from this related table that meet specified criteria.

                             Your calculation effort seems to fall somewhere between these two goals, achieving neither one... wink

                        • 9. Re: Filtering Portal Record Does Not Produce Expected Result
                          philmodjunk

                               What results do you get with this calculation? Do you get a ? result or the wrong value?

                               The only obvious error--which might be a typo, is that you have Left ( TestPerformed , ) where it appears you need Left ( TestPerformed , 3 ).

                               If this is a typo and you are getting the wrong date, can you confirm that Serum1DateCollected is a field of type "Date" when you check it in Manage | Database?

                               And apologies, but the previous thread had me finding very small syntax errors for you such as missing parenthesis that had nothing to do with SQL, they were just wrong calculation syntax. Granted, I should have been more careful in the expressions that I posted (and I was very annoyed with myself over making so many small goofs), they just seemed to be errors that you could have found and corrected on your own in many cases if you were more familiar with FileMaker calculations--hence my suggestion to study up on FileMaker.

                          • 10. Re: Filtering Portal Record Does Not Produce Expected Result
                            evanscl

                                 I corrected the typo, but it had no effect on the result. I am getting a question mark (?) as the result. The calculation result is set to a "Date" and the Storage Options is set to "Do not store calculation results -- recalculate when needed.

                            GetValue (
                                  
                             ExecuteSQL (
                                  
                             "SELECT Serum1DateCollected FROM \"Human_Testing\"
                            WHERE Human_Testing_fKey = ? AND SampleType = 'Serum' AND IllnessPhase = 'Acute'  AND Left ( TestPerformed , 3 ) = 'DEN'
                            ORDER BY Serum1DateCollected ASC "
                            ;  "" ; ¶ ; Human_PatientInfo_pKey
                                  
                            ) //ExecuteSQL
                                  
                             ; 1  ) //GetValue
                            • 11. Re: Filtering Portal Record Does Not Produce Expected Result
                              philmodjunk

                                   Try setting the calculation result to "text" and see what you get.

                              • 12. Re: Filtering Portal Record Does Not Produce Expected Result
                                evanscl

                                     I get a question mark (?).

                                • 13. Re: Filtering Portal Record Does Not Produce Expected Result
                                  philmodjunk

                                       Here we go again. sad

                                       FileMaker's current set up for working with ExecuteSQL is so frustrating! I don't see any obvious Syntax errors, but it appears that there is one given the ? result.

                                       When I get that, I start making copies of the calculation field and then remove different parts of the expression from the copies and look to see which copies still return a ? result. That at least helps me narrow the focus down to a specific part of the SQL expression....

                                  • 14. Re: Filtering Portal Record Does Not Produce Expected Result
                                    evanscl

                                         If I remove the GetValue part of the statement, my result is 00-00-0000, which I guess is a placeholder for a date. I'll have to play with it more tomorrow.

                                    1 2 Previous Next