Complex report, need to iterate through a list in each record and check a condition

[Patrick_Paul]

Allow me to give background:

I have a table of training class records.  One column in the table is "participants".  This column is of type "List" and contains the Training IDs of each person who took the course.  Our company teaches courses on behalf of a third-party vendor, and our trainers have to communicate back to the third party company who took what courses so they can get credited for them.  I have a separate table that contains the third party's records for our people that gets updated daily, and is a part of the app (read-only).  There is *not* a direct relationship between "our" training records and theirs, but their records (rows) use the same Training ID for each person, and the columns in each record correspond to the various classes they could take and their status.  I'd rather avoid creating a "ref" between the two because the app already has a lot of processing overhead on each sync, and this would just create more.

I need to build a report template that will somehow look through all the training class records and check each participant to see if they got credit for the class in the third party data.  I'm having a very hard time coming up with logic for this in an Appsheet report template.  In a typical programming language, I could simply loop on the participant list (using "for each" or something of the like) and do a search against the other table for each element (person) in the list, checking the Training ID in the list versus the corresponding class field (based on which class was taught according to "our" table) in the same Training ID's record in the third-party data.  I can't figure out how to accomplish something similar in an Appsheet report template.  Could I somehow use nested Start:(Select...) statements?

I realize this is a very complex issue, and I have not given all the details.  Please ask me questions if needed.  Any help is appreciated!

[Patrick_Paul]

Here is the template I have come up with so far.  The bot tests OK (no errors), and the automation log analyzer shows it's successful when I run it, but I never get an email (other reports coming from this app function as expected).  The "Welding" table is the third party records, the [related events] is a reverse ref into our training records as explained above.  I discovered that there was a ref between the two tables I had not considered leveraging.

<<start:Welding[i-car id]>>

ST105:

Technician

Trainer

Event Type

Location

Date

I-CAR Results

<<Start:orderby(Select([related events][event id],and([event type]=”Steel Sectioning ST105 CERT”,now()>[end],[completed]=true)), [creator])>>

<<[_thisrow-1].[Tech Name]>>

<<[creator]>>

<<[event type]>>

<<[location].[location name]>>

<<[start]>>

<<[_thisrow-1].[WTC: Steel Sectioning]>>

<<End>>

<<end>>

[WillowMobileSys]

How is your Bot defined with regards to saving the generated document and sending the email?  There are several options/possibilities.

Look at the Monitor page for the automation to see if the bot triggered at all and if so were there any errors.  

[Patrick_Paul]

It is set as a body template.  I've done this a lot, this one is just more challenging than most.  It tests fine (no errors) and when I hit "run" the log shows it has successfully executed.  The email is not in my spam or filters.  All other email reports are received without issue.  So far support is stumped as well, I've chatted them....