Solved: Possible to shorten a reference list? - Page 2

Work_Goscinski · 09-15-2020 08:47 AM

I have an audit app. My users are spread across 3 plants and choose parts depending on the plant they are auditing. In a perfect world, they would be auditing random part numbers before they ship.

I have created a view where users can see which parts have been recently audited (to hopefully select a new, un-audited part), the most recent date, AND how many times that part has been audited. It also notes the number of occurrences a part has been a part of an issue within the audit. (This allows an auditor to check on the part again to ensure corrective action to prevent issue has been instituted properly.)

Would there be a way to “shorten” said part numbers if it was to meet certain criteria?

For instance, if part has been chosen recently OR if part number has been chosen more than twice AND part number has not had a recent issue, then “remove” it from the reference list.

Any suggestions how to do so?

Suvrutt_Gurjar

If I have understood your requirement, I believe you could try something like below. As with multirow expressions, the validation could be sync time expensive. In the valid_if of the [Parts ID] reference column in Audit Table , you could try an expression something like below

Parts Table Name[Part ID] -
SELECT(Audit Table Name[Part ID], [Inspection Date]>TODAY()-10) - IFS(COUNT(SELECT(Audit Table Name[Part ID], AND([Issue Record]=“Nil”, [Inspection Date]>TODAY()-5)))>2, SELECT(Audit Table Name[Part ID], AND([Issue Record]=“Nil”, [Inspection Date]>TODAY()-5)))

The first SELECT() statement subtracts [Part ID] that have been inspected in last 10 days. Second SELECT() statement substracts[Part ID] s where issues are NIL twice in last 5 days.

You may of course use combinations, variations of these expressions. But hope above helps in giving some ideas.

View solution in original post