Average Rating of last 4 inspections/entries.

Question

Is there a formula that can be used in a summary report to show the average rating of only the last 4 entries of a given part number?

Current table is simple with 2 fields. A part number is selected through a relationship dropdown, then the selected part number is then given an inspection rating of 1, 2, or 3 based on inspection criteria, then saved.

Background:

I am attempting to setup the % of parts to inspect based on the average rating of the last 4 inspections. My issue lies in the fact that not all parts are delivered and inspected on the same schedule (i.e. weekly, biweekly, monthly, quarterly or biannually) so I am unable to use a time period to capture an equal rating across all parts. Without a formula that only captures the last 4 entries of a part number, I fear I will need to add and manage a dropdown of "delivery schedule" on 1000+ parts to allow filtering to capture the last 4 inspections completed. (or keep using the 15 spreadsheets that I currently track this information on)

Any suggestions or work arounds would be appreciated. Thank You.

donlarson · Answer

Here is a brute force method.&nbsp; &nbsp; &nbsp;Build this&nbsp;The field Pipeline count you should set to a Default Value of 1.&nbsp; &nbsp;Everytime an Part Inspection record is created it will be set to one.Make a Pipeline that fires when Part Inspections are CreatedStep B is a Search of the Part Inspections TableYour advanced query should be that Related Part = a.related_part andRecord ID not equal to a.idThen Step C is an Update RecordPipeline Count +1In your Summary Report filter your inspections where Pipeline Count &lt;=4You will need to clean up your old data but going forward only the four most recent Part Inspections will be used in the Summary Report.&nbsp;&nbsp;&nbsp;

markshnier__you · Answer

As long as you don't have too much data, this relativly simple formula which uses a Formula Query will work to have each record flagged as to if it's in the most recent 4.

So it can be used as a filter in your summary report.

var text QUERY = "{81.EX.'" & [Part Number] & "'}"
& " AND " & "{3.GTE." & [Record ID#] & "}";

Size(
GetFieldValues(
GetRecords($QUERY),3)) <= 4

// replace the 81 with the field ID of the [Part Number} field

donlarson · Answer

I almost always get the dreaded message"Whoa that query will take way too long to execute"Every conventional software language or framework would do this with a query.&nbsp; &nbsp;That is why I called mine the Brute Force method.&nbsp; It is not elegant but it does not crash the application like a formula query.

Forum Discussion

Average Rating of last 4 inspections/entries.