Solved: Out of Date Training Records - Page 2

Chris_Jeal · 09-21-2020 07:54 AM

.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

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]

You have shown the slice that gives the list of all expired records for a particular employee.
Open a record from that expired list in detail view. ( Say SOP_13)
Go to a new record using LINKTOFORM() action in that detail view ( Step 2)
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.
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"

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

View solution in original post