Training - Only allow individuals trained on documents to train others on the same documents

[Dario]

Hi everyone, I’m trying to work on one of the more complicated expressions I’ve ever attempted in Appsheet. The purpose is to look for a table of training records where a single cell contains a enumlist of document records spread across multiple rows (for example one row can have ‘Dave’ trained on document list ‘Forklift Inspection, Truck Inspection’, while another row can have ‘Steve’ trained on just ‘Forklift Inspection’. This information is in the ‘Trainingref’ table.

I want to then take that information, and cross reference it with the ‘trainer’ column in the active spreadsheet (the ‘Training’ table), to ensure that for Steve to train on ‘Forklift Inspection’, he himself already has a training record on said document, and no documents where he/she are not trained on will appear. The ‘Training’ table is a parent table, and the ‘Trainingref’ table is a child table that is set to REF / is a part of. All training documentation referenced by these tables are in a master document table (QMS Slice Super), where the document names are stored as unique, single text entries (not grouped lists), e.g ‘Forklift Inspection’ has its own key record, and Truck Inspection’ its own etc etc.

To try achieve this objectives I’ve started with a Select function that looks in one table slice, and cross references it with a condition from another table, with yet another condition from a third table.The current code gives me no errors, however it is not performing the function as I’ve definitely made some mistakes with the references (or lack thereof). The code is as follows:

SELECT(QMS Slice Super[Documentrev], AND(CONTAINS([Trainers], Trainingref[Trainee]), CONTAINS(Trainingref[Documentrev], QMS[Documentrev])))

This is my screencap -

I’m not sure what the best course of action is here, and any help would be greatly appreciated. You guys are great!

Thanks,
D

[Steve]

I’ve spent 10 minutes trying to understand what you’re trying to, but cannot. Can you try simplifying your problem description? With fewer implementation details and more “what are you trying to accomplish”, in plain language.

[Dario]

Hi Steve, not sure how I can simplify it further, but I’ll give it a shot.

I have a Training form, with drop down menus for employee names (trainers) and document names. This training form goes into a child record for employee names (not trainers, but trainees). My aim is to make the drop down menu for document names only show documents which (edit: Trainers) have been trained on.

Hope this makes more sense, if anyone is able to help out I’m sure you will be able to! Sorry if I made my question more convoluted than it needed to be.

[Steve]

So a trainee would choose a document, then be presented with a list of people who have already been trained on that document?

[Dario]

Sorry, only picked up the typo now, it’s a dropdown of documents the ‘Trainer’ has been trained on, not the trainee. Fixed it in the comment above. That one totally slipped by me last night, you must have been thinking wtf lol.

[Steve]

I’m still finding your situation confusing, I have not been able to clearly and fully grasp what you want, and I’m not interested in fighting to understand. You’ll need to find help from someone else. Perhaps consider an AppSheet partner.

[Dario]

I know you help out a lot here Steve, so thanks for taking the time to try. I wasn’t even aware of the partner program… thanks for the link

[Steve]

Please provide screenshots of the column lists for the following tables:

• The table that uniquely identifies each possible training document.

• The table that identifies the potential trainers.

• The table that identifies the potential trainees.

Please also identify the table and column that identifies which documents for which a given trainer is qualified to train on.

[Dario]

The ‘trainer’ would choose a document, then be presented with a list of documents that only they themselves have been trained on.

Here’s those screenshots you asked about Steve, wasn’t sure how to generate the entire column to save, is this good enough?

Documents

Trainers

Trainees

[Steve]

What is the purpose/goal of the trainer choosing a document?

[Dario]

The document they choose (documentrev column) is what identifies what document they were trained on. Since there are hundreds of documents to choose from, the enumlist of documentrev’s will then show up in the child ‘trainingref’ table with the relevant trainee name.