4 Replies Latest reply on May 3, 2013 9:32 AM by productionQC

    Charting Issue...Omit or filter calculation...



      Charting Issue...Omit or filter calculation...



           I am trying to chart some test data in our FMP database.  We have a check box on our layout that allows users when entering test results to mark it as defective, meaning something went wrong in the test procedure and that data should be recordered but not used.  So in my chart data I am using a calculation with an IF statement to ignore records with the check box filled in.  The problem that this presents is that any record that is check leave a gap in the chart, it is not actually gone, rather just shows no value on X or Y axis.  Thus, I can have a line graph that has major gaps because the spot for the data is allocated but really there is no data.  My calculation in charts to filter out the checked records is as follows:  If ( Products::TestOmit ≠ "OMIT RECORD" ;  Products::TestResult ; "").  So I am assuming that the issue is with using "" when I want to omit that record, it's telling it to leave a blank, rather than no data at all.  Thus, it creates the gaps in my charts.  Is there an alternative to using "" or a whole other approach I should look at, like somehow using the omit function.  Any sugestions out there?


           Thanks in adavance

        • 1. Re: Charting Issue...Omit or filter calculation...

               You have two options:

               1) Omit those records from your found set. This is the simplest approach as you just need to perform a find that omits records with this value in TestOmit, but in some types of charting, this doesn't work as you may need a 'space' for the value in your x-axis even though you are not charting the value.

               2) This get's a bit hairy, but you can set up a calculation that uses the y values preceding and following this value to interpolate a "place holder" value to use as a Y value. This charts the average of the preceding and trailing y values to keep the line in place as a straight line drawn between the values you averaged. Where this get's sticky is that you might have 2 or more omitted values in a row. To handle that, I think you'd need a script that looped through your records and computed the needed interpolated values for the omitted y values. (Remember your point slope formula for computing the equation of a line between two pionts?)

          • 2. Re: Charting Issue...Omit or filter calculation...

                 Hi Phil-

                 Thanks for the response.  Is there a way to have a looping script that always "omits" checked records the minute someone fires up the database.  Therefore all "omits" are out right off the get go and not available to any other layouts, charts, etc..  That way I am not writing a script or calculation for every new instance of a portal, chart, etc., to omit the checked records.  I would not like to have them show at all in any part of the database unless I make a layout to specifically show omitted records...

                 Or a script attached to each omit checkbox on each layout that would omit the record, not reseting after "Show All" used in other scritps...


            • 3. Re: Charting Issue...Omit or filter calculation...

                   No need for a looping script.

                   Use a script like this:

                   Enter Find Mode[]
                   Set Field [Products::TestOmit ; "Omit" ]
                   Omit Records[]
                   Constrain Found Set[]

                   You can set this script to be performed by layout script triggers such as OnLayoutEnter for when the user accesses a layout and OnModeChange to automatically modify the results of a user's manual find.

                   You can also set up scripted finds for the user to use that automatically includes criteria to omit the Omitted data.

              • 4. Re: Charting Issue...Omit or filter calculation...

                     Thanks Phil, that will take care of the problem.