Think of this a little bit like a registrar.
We, at New Mexico State University, conduct a year long paraprofessional certification program.
*Participants register for the program.
*Training consists of a one week introductory, in person, course, followed by six, live zoom training sessions, spread over a year.
*The program has existed for approximately 8 years now. Some of the participants have been employed for that entire time with new participants entering on a regular basis.
*Upon certification, participants are certified for two years. In order to maintain certification, over the course of those two years, they are required to retake any three of the seven courses, which means, over time, their latest training dates, for a given training, will change. Those participants that have been employed for an extended period of time now have completely different training taken dates from their original certification track.
*Just like a registrar, we maintain participants training dates, and recertification dates, in perpetuity.
Up to about three years ago, because the program was small but growing, we were able to to track this through Excel. We came to realize that as the volume of data grew, this was not a viable long term solution, thus, we entered the Smartsheet world.
Using Smartsheet(SS), we created a SS sheet from this Excel sheet. We used a different form/data collection product for the initial program registration and subsequent training module registrations manually copying the data from this collection product to SS. About a year ago, we integrated the initial program registration into SS with no hitch.
Shortly, thereafter, we started the process of integrating the training session registrations into SS. This is where the trouble began. We(I) have tried every solution we can think of to no avail.
I have been in near constant contact with SS Pro Desk for the better part of the last six months trying to solve one issue after another. This is the summary email from SS, dated November 11, 2019.
"To find a definitive answer and avoid further manual troubleshooting, I recommend a two-pronged approach:
- Community Inquiry: I recommend shifting gears and making a post on the Smartsheet Community. We can present the specific challenge regarding how the INDEX(COLLECT) and MID functions are handling your date value string. The Community often has power users who have solved these niche formula challenges.
- Internal Escalation: I will follow up with our internal Formula Resource team to see if we have overlooked a specific syntax or function behavior. I will complete this investigation and research in a separate support request from this Pro Desk request."
I have come to believe that there is some foundational flaw in the system that I have been attempting to build. Every issue that Pro Desk has helped me solve, inevitably, leads to another, equally, vexing problem. The above quote is, basically, saying that SS has thrown in the towel and is asking for community help.
I have tried cell linking. The results were disastrous. Cell linking might work if I were starting with a blank slate but with 8 years worth of historic data, this did not turn out well.
I'm in the midst of trying Data Mesh with very limited success.
My inquiry comes down to two points.
*Training registration and tracking has to be such a common use for SS that I'm surprised that a template doesn't already exist.
*Is anyone in the training/educational universe willing to assist me in determining a path forward?
I realize that this overall description of the problem lacks specifics but it provides a decent overall scope of the nature of the project. If anyone has any insights they might be willing to share, I could, then, provide specific issues and details.
Thank you very much, especially if you read all the way through this. :)
