Out of Date Training Records

[Chris_Jeal]

.Hi Team,

I’m wondering how to about this, any suggestions will be helpful.

We’re recording training records which have to be updated annually.
A new record is to be submitted if the previous training record (SOP) is over 12 months old.

I would prefer if I could have a list of records that have expired per person and once renewed would be removed from the list.

I am currently slicing the data to show records that have expired, which is giving me the list, but as expected will not reduce once another record is submitted as the older record still exists.

I have names and IDs for the records with dates.

Any ideas?

Chris

[Suvrutt_Gurjar · Accepted solution]

Hi @Chris_Jeal

Presume you are adding new training records one at a time. Please explore if below helps. It is a bit lengthy approach but will set old record to expired when creating a new record. May be someone will have a much efficient approach suggestion.

Please have an additional text column called say [Training Status]

1. You have shown the slice that gives the list of all expired records for a particular employee.

2. Open a record from that expired list in detail view. ( Say SOP_13)

3. Go to a new record using LINKTOFORM() action in that detail view ( Step 2)

4. In new record, retain whatever columns from previous year’s record through LINKTOFORM() action settings, modify whatever columns necessary to modify manually and save this record. Let the [Training Status] column have predefined “Active” value set.

5. Create an event action on the same table of type ( Execute an action on a set of rows) that references the same table. Say this action is “SetOldtoExpired”

Referenced rows expression can be something like

SELECT( Table Name[Key Column], AND( [Name]=[_THISROW].[Name], [Date]<=TODAY()-365, [SOP_ID]=[_THISROW].[SOP_ID]))
Referenced action can be called 'SetStatusToExpired"

6. Referenced action “SetStatusToExpired” action can be type “Set values of Some Columns in this row” and sets [Training Status] column to “Expired”. Alternatively this can even be delete action if you would like expired records to be deleted.

7. Set event action on this new form save to “SetOldtoExpired” ( Described in Step 5)

With the above process, old records will be set to “Expired” status on new record’s form save and new will be created with “Active status” . The slice you have shown will need to have additional condition of [Training Status]=“Active” to always select active records.

More logic and thorough testing depending on your actual app configuration may be required.

[Suvrutt_Gurjar]

Alternative much easier approach could be

1)Create an action of type " set the values of columns in this row " to set [Training Status] to " Expired" .

2. Create a LINKTOFORM() action as described in earlier post.

3. Include actions in step 1 and 2 into a Group action called say “New Training Recrd”
   Have suitable conditions for this action to show (Such as record older than 1 year etc)

4. Open an expired record, click on group action to create the new record and set the old one to expired.

This one is not tested but I believe will work.
Edit : made changes to description.

[Chris_Jeal]

Great solution again @Suvrutt_Gurjar !
Worked a treat.

Thanks for your help.

[Suvrutt_Gurjar]

I may mention here that among the two approaches described above, the 7 step approach is safer because it will set the older record to “expired” status only when the new record is added.

With the 4 step approach described above, the status of the older record is set to expired before the new record is saved. There are chances that the user may come out without saving the new record for some reason. In that case, the situation will arise that the older record is already set to “Expired” without creating a new record.

[Aleksi]

One option is if you wrap two condition rules with the AND() for your slice. The existing one and a new one where you check if the record is the latest one in that category (SOP). Something like AND(…,[DATE]=MAX(SELECT(Table[DATE],[SOP_ID]=[_THISROW].[SOP_ID]))

[Chris_Jeal]

Hi @Suvrutt_Gurjar,

Many thanks for your solutions, I will adopt one of them today and report back and @Aleksi I will certainly add your suggestion into the mix where it’s required.

Thanks for your time guys.

Chris.