6 Replies Latest reply on Oct 19, 2015 9:56 AM by yiningw

    Pseudo Record

    yiningw

      I currently have a list of several sorted records that display how much the field is above and below a stated average. I'd like to create a dividing static box that shows what the average number is, in between the lowest "above the average" as well as the lowest "below the average". By doing so, I've been trying to create a pseudo record that'll automatically replace that field's number with the average, so it'll automatically fit between the two fields. However, that record is now included in the calculation of the average (which is a summary field). Is there a way to omit that one record from the summary, or is there a better way to go about this?

       

      Thanks!

        • 1. Re: Pseudo Record
          erolst

          It's usually not a good idea to introduce dummy records into your business data tables. You're potentially falsifying results (as you have just noticed), and need to clean up later.

           

          But having said that, I should mention the concept of “non business data tables”, aka Virtual Lists (search for it).

           

          Depending on your (visual) requirements, a better approach may be to use either an additional calc field that you only populate (or display, or both) if necessary, or – if you have FM14 – use one of several methods that gives you a “layout calculation object” per record (dummy global field & placeholder calc; single-segment button bar & label calc).

           

          Something like


          Case (

            theValue < statedAverage

            AND

            GetNthRecord ( theValue ; Get ( RecordNumber ) - 1 ) > statedAverage ;

            statedAverage

          )


          for a trailing display, or the reverse for a leading one, would give you a result only for the record directly above or below the threshold.

          (Speaking of which: is that average “stated”, as you … well, stated – or is it calculated?)

           

          For output, you can makes the body part taller, put the result “object” there and set it to slide; this gives you an additional header section. In Browse, though, it won't look that good …

          • 2. Re: Pseudo Record
            yiningw

            Thanks! The average is a calculation field depending on what categories are being selected. If I'd like to create a dynamic list for that, is there a more general formula to use rather than stating the exact average?

            • 3. Re: Pseudo Record
              erolst

              yiningw wrote:

              If I'd like to create a dynamic list for that, is there a more general formula to use rather than stating the exact average?

               

              Let me answer with “most likely” (since you can code just about everything in FM), but since I have no idea what exactly you mean, that's all I can say … Care to elaborate?

              • 4. Re: Pseudo Record
                yiningw

                I'm making sorting by "year" "duration" "genre", etc. The records are the same, but say I want to sort by 2011 the average of that would be different from the sort from 2012. Or if I want to do a sort by hour longs vs 30 minutes the average would be different too. Eventually I'd like to sort by combined fields (eg, year and duration). Sorry I don't know how to be too specific/technical with this, I'm quite new to Filemaker.

                • 5. Re: Pseudo Record
                  erolst

                  yiningw wrote:

                  The records are the same, but say I want to sort by 2011 the average of that would be different from the sort from 2012.

                   

                  If you mean that you want to sort and group your records by year, then compare a record's value against the average of its group, look into GetSummary().


                  It returns a summary field's value for a sorted group; you can compare that value against individual record values; e.g.

                   

                  Unsorted:

                  year ... value

                  2012 ... 5

                  2011 ... 11

                  2011 ... 6

                  2012 ... 3

                  2011 ... 4

                  2012 ... 7

                  summary field sAvg (Average of: value): 6


                  Sorted by year:

                  Screen Shot 2015-10-16 at 15.55.05.png

                  • 6. Re: Pseudo Record
                    yiningw

                    I ended up using a Case calculation for "Above Avg" "Below Avg" and "Avg" to separate the categories instead.

                     

                    Thanks!!