Possible to shorten a reference list?

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?

Solved Solved
0 5 186
1 ACCEPTED SOLUTION

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

5 REPLIES 5

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.

That is helping. At this point, just doing the first partโ€“subtracting inspection dates > 30 puts all parts back on the list.

What I mean is this: My part numbers table has 2 columns Part Number, Location. As it works now, if the user selects Plant 2 as the location, only Plant 2 parts populate the list.

However, when I use the expression

Parts Table Name[Part ID] -
SELECT(Audit Table Name[Part ID], [Inspection Date]>TODAY()-30)

The Location column is ignored and the complete part list is shownโ€“all parts from all 3 plants. Perhaps I will have to modify the Select to keep the Location as a criteria? Not sure.

But your expressions have helped. I will continue to tweak it, unless you have any other insight.

Yep.

If you had been using the built-in dependent dropdown feature, adding Valid If expressions will disable that feature for those columns. Consequently, you will have to add the dependency logic to your Valid If expressions.

I see. I will have to play around to add that dependency logic.

Thanks

Steve
Participant V

For reference (see the section on list subtraction):

Top Labels in this Space