5 Replies Latest reply on Nov 23, 2009 11:10 AM by imgaryshap

    Filtering sorts problem



      Filtering sorts problem


      I'm using FMP 8 on a MAC.


      I've used the Find command in a script to filter my records by Field B.   So Field B="Text 1" and Field B="Text 2."


      I've then ran a summary report using Field A.


      So it looks something like this:


      Field A

           Record 1 - Text 1

           Record 2 - Text 1

           Record 3 - Text 2


      is there a way to - for lack of a better word - "trick" the sort?


      Field A and Field C contain identical value sets.


      I'm wondering if there's a way to say If when sorting by Field A, Field B="Text 2", bypass Field A and use the value in Field C?


      I have a development report for projects.


      Field A = possible networks to pitch to.  this is set up as a portal to another table, so that I can select multiple networks.  (i.e., "This project is right for ABC, CBS, and NBC.")


      Field B contains a value set of various stages of the project.  


      When I run a development report - I first filter Field B to include only two values, "Development" and "Pitched", and then sort the records that fall within this criteria under Field A.


      So my reports list records multiple times pending on the network, but here's what I'm getting:



      Project 1 - Development

      Project 2 - Development

      Project 3 - Pitched



      Project 1 -Development

      Project 2 - Development



      Project 4 - Development

      Project 1 -Development



      Project 1 - Pitched


      Instead of blank for the pitch, I'm trying to figure out if Field B="Pitched" to use the value from Field C (where the project was pitched) - instead of the value from Field A.



      Field C = a value set containing all the networks as well (much like Field B), except it refers to an actual place a project was pitched.


      make sense?


      any hope of this?








        • 1. Re: Filtering sorts problem

          "Make Sense?"


          It's not totally clear to me what text you want to appear in the sub summary header in your report where you currently see "blank".


          A general suggestion would be to create a calculation field that returns text that uses and If or Case function to return different text from different fields depending on which fields hold the desired text.


          If( isempty (FieldA); FieldC; FieldA)


          Is just one possible example.


          You'd place this field in your report and use it for sorting (and the subsummary part's "break" field) in place of your current fields.

          • 2. Re: Filtering sorts problem

            I see.   make the actual field have it. rather than the script.  you're the best.  thanks!





            • 3. Re: Filtering sorts problem

              i think i'm hitting a snag because both fields contain value lists.


              any thoughts?


              is it a Get Function if that's the case?  



              • 4. Re: Filtering sorts problem
                   Formatted to permit users to select multiple values such as a check box field? If so, that can create problems when you sort. Otherwise, I don't see how the value list formats will affect this issue--but keep in mind that I'm not completely clear on exactly what you are trying to do.
                • 5. Re: Filtering sorts problem

                  Your first solution was the right one. 


                  it worked perfectly.  I created a field that is:


                  If (IsEmpty(Field A) and (Field B="Text"); Field C; Field A)


                  then i sort by this new field, as opposed to Field A.


                  thanks again.