7 Replies Latest reply on Jul 29, 2014 8:56 AM by MichaelHart

    Filtering Null values from a Portal List

    MichaelHart

      Title

      Filtering Null values from a Portal List

      Post

           In FMP-13 Mac

           How do I filter out Portal records which contain null values in a specified field(s)? I know where to do this but not how since there is no simple "null"  function, that I seelisted in the Portal Setup dialogue.

           I tried "Impressions::PKT #  ≠ 0" (PKT # is the field i want filtered) but that didna work.

           Thanks for any help on this.

           Mike Hart

        • 1. Re: Filtering Null values from a Portal List
          philmodjunk

               IsEmpty ( Impressions::PKT # )

          • 2. Re: Filtering Null values from a Portal List
            MichaelHart

                 Nevermind,

                 I changed the filter to: Impressions::PKT # > 0 and the PKT# field to number format. Now it seems to be working right.

                 Yip Yip Yip!

            • 3. Re: Filtering Null values from a Portal List
              MichaelHart

                   Phil,

                   I didna' see your reply til' after posting nevermind... so nevermind that nevermind.

                   I had been trying to get "IsEmpty" to work but, it listed no records, at all, until I used "not" ( "not IsEmpty ( Impressions::PKT # )"). Then it worked similar to the result of Impressions::PKT # > 0.

                   Actually, on second look, neither of those filter-formulas really do what I wanted because, both list records that have no data in the Impressions::PKT #-field.

                   I can live with that but, was looking to not list the records with nothing in the PKT# field.

                   In fact, that's probably a better strategy anyway. That way I can see what fields require data. I can just use Sort to get the PKT # field to the top of the Portal-List. Yeah that's better anyway.

                   I'm learnin'.

                   Thanks for the help.

                    

              • 4. Re: Filtering Null values from a Portal List
                philmodjunk

                     Not IsEmpty is what I had in mind. It's exactly what you asked for-- a test for the presence of a null value in a field.

                     

                          both list records that have no data in the Impressions::PKT #-field

                     That suggests that the records where you have no data in this field that are getting past your filter do not really have a null value or there is an issue in the precise implementation of your filter expression.

                     First thing that I'd check is for invisible characters, such as spaces and returns, present in that field. Such a field is not empty but will look like it.

                • 5. Re: Filtering Null values from a Portal List
                  MichaelHart

                       Today, I'm scratching my head because none of my attempted filter-calculations seems to be affecting the records listed in the portal. The sort-order has an affect but not the filter. 

                       Could it be that the sort-order trumps the filter-calc.? 

                       I've uploaded a screen grab to show the current formula and to help demonstrate that there are no ghost characters in the designated fields, that would account for it. I checked for invisible spaces, etc. by selecting the fields and hitting the delete-key, without effect. Could ghost-chars, in other (inactive) records be the cause?

                  • 6. Re: Filtering Null values from a Portal List
                    SteveMartino

                         Although I don't know exactly what you are trying to do but :

                         not isempty(Impressions::DRWR) or not isempty (Impressions::Framed) 

                         is the correct syntax for the calculation

                    • 7. Re: Filtering Null values from a Portal List
                      MichaelHart

                           Steve -

                           That's what I was looking for. It does exactly what I wanted. For some reason, I thought I had to follow the highlighted prompts given in the Portal/Filter dialogue to get the correct syntax. 

                           Thanks