Skip to main content
Question

Average Rating of last 4 inspections/entries.

Forum|alt.badge.img+2

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.

This topic has been closed for replies.

Forum|alt.badge.img+19
  • DonLarson
  • Qrew Cadet
  • October 1, 2024

Here is a brute force method.     Build this

 

The field Pipeline count you should set to a Default Value of 1.   Everytime an Part Inspection record is created it will be set to one.

Make a Pipeline that fires when Part Inspections are Created

Step B is a Search of the Part Inspections Table

Your advanced query should be that Related Part = a.related_part and

Record ID not equal to a.id

Then Step C is an Update Record

Pipeline Count +1

In your Summary Report filter your inspections where Pipeline Count <=4

You 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.

 

 

 

MarkShnierYou
Forum|alt.badge.img+24

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 

 

Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+19
  • DonLarson
  • Qrew Cadet
  • October 2, 2024

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.   That is why I called mine the Brute Force method.  It is not elegant but it does not crash the application like a formula query.

Terms & ConditionsAccessibility statement