We have a database that is used by about 120 students over the course of their 4 year education.
Students enter data about their patient interactions (no sensitive or patient identifiable data) into a survey form on the cloud service called Qualtrics. The way the Qualtrics survey was created, doesn't allow for the easy reporting of the data by the students. So we built this FMP solution that we've been using about 4 or 5 years now. We import this qualtrics data into a FileMaker DB, which creates a summary-like report, based on the time-frame for which the student wants a report. The student logs in and authenticates to the DB, selects a start and end date, and a PDF report is emailed to them. This works pretty well for the most part.
The summary report shows 400 values, a PDF of the report is attached. Each of these fields is a calculation field, and each uses a custom function which uses "Execute SQ." I realize this is a lot of calc fields to be evaluated all at one time.
The DB is visited by the students once or twice every six weeks. Students are required to create a report once every six weeks; but they may also create reports for shorter or longer periods of time, and prior to graduation, they are likely to create a report that shows their numbers for the entire 4 years. On a daily basis, there are 3-10 PDF's generated. The students access the DB via a WebDirect layout. When a student logs in and completes a simple form, it triggers a script. That script is a PSOS script that goes to the layout with 400 calc, populates it, and then makes and sends the PDF. we used to use a robot box to make and send the PDF's and changed it to allow the server to do that when FMS allowed for the saving and sending PDFs from the server. We did not experience a problem prior to this change to a PSOS.
I think the other piece of info that may be useful is that we have a two box set up. Students connect to a FMP server worker box (AKA tincture), which resides in a DMZ, and that box talks to our FMP server master box (AKA liquid) which lives in a server zone. This DB solution is one of about 22 DB solutions and many of these use Webdirect and thus go through these two boxes. Both the worker box and the master box meet the recommended specs for a server, RAM, etc.
At the end of every 6 week period, the entire class of about 120 need to generate reports, and this is when an issue *sometimes* occurs. A problem doesn't happen every six weeks, but when it does happen, it really is a problem. What happens is that the DB seems to stop working, and reports get generated that show all zeros instead of actual counts.
I usually become aware of the problem, when a student sends me a summary and asks why is it all zeros. I then, look at the server via the Admin console, and I'll see users connected for a long period of time, as well as the PSOS connections still connected. The numbers aren't really high- like a dozen of connections there. This ought to be below our license limits. If I restart the server, it goes back to running normally and the students get there summaries just as expected.
So, I don't know how to sort out what it is that triggers the problem to cause the system to hang, if that is what is happening. We are looking at how to rewrite the DB so that there aren't 400 calc fields on a layout. But that doesn't address why this can sometimes work just fine and then at others not. I've looked at the custom function, and it all seems good. I've used the script debugger and can't find an issue there. and the problem doesn't happen all the time, I am not able to manually replicate it. I've looked at every log I have access to, and don't see anything that points me to the problem.
I'm asking now, because I am about three weeks away from the end of the next 6 week period. And I'm wondering what I can do to/ look at before we get to that six week deadline. All input welcome.