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
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.
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.
So a trainee would choose a document, then be presented with a list of people who have already been trained on that document?
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.
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.
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
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.
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
What is the purpose/goal of the trainer choosing a document?
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.
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |