Autamically record the date when an Inspection Fails

AppSheet AppSheet Q&A
1

I have a table where we keep track of Field Inspections, I have a column for the Date of the inspection, we allow 7 days for these failed inspections be fixed, when an inspection is fixed, the record is changed to fixed and the date changes to the day when it was fixed, I’d like to also keep date when the inspection failed. I will need need a formula to record automatically on a column when the Inspection failed.

so far I have,

IF([OVERALL STATUS]=“Failed QC Inspection”, input(text, now())=True , False)

2

You can have two columns.

  1. First inspection date:

    • Initial value : TODAY()

  2. Successful Inspection Date:

    • App Formula :

      IF( [Overall Status] = “Failed QC Inspection”,
      “”,
      IF(ISBLANK([_This]), TODAY(), [_This])
      )

Ideally though, you should have two separate tables, one for the inspected fields/items, and another for inspections. The Inspections table would have a Ref column pointing to the Items table, and will just record inspection visits with a Date column and a Status column. The main Items table will not have any Date or Status columns. In this way, you’ll be able to have as many visits as needed, each with its own status, comments, inspector and whatever visit-related information you’d like to incorporate.

3 Likes
3

Thanks!, Ill try it. I’ll let you now if it works

4

Alright, I tried it, it’s not doing what I would like it to do. When A QC inspection is performed, I have 2 columns, 1 for {QC DATE], 1 for [Failed on Date}, when failed issues are corrected, QC DATE, might and inspection to pass. I want the column [Failed on Date] to keep the date when Inspection failed.

5

What you have tried is different from what I’ve suggested. What I proposed is “First inspection date”, and a “Successful Inspection Date”. When an inspection fails, its date will be registered in the first column while the latter will be empty. When another inspection for the same location succeeds, the first column will still hold the date on which it failed, while the latter will have the date on which it has succeeded.

If you want to do it the other way round, it is possible; you’ll just have to adapt the formulas accordingly.

In any case, I strongly encourage you to implement instead the proposal in my last paragraph. This is the correct way to do it.

1 Like
6

Believe me, I tried your formula, I get an empty column when the inspection fails, [Fail on Date] stays blank.

7

The first Inspection Date could be Pass or Fail

8

If Inspection Passes on first visit, it Hold a Pass Inspection with a Date, hence your formula does not work.

9

Hey Joseph,

I do appreciate your help. In a way, I used your formula partially and worked, thank you!.

IF([Overall Status] = “Failed QC Inspection”,
[QC Date],
IF(ISBLANK([_This]), [_This], [_This])
)

This way way, even if the QC date changes or revised and marked as passing, it still Keeps the Failed on date

1 Like