Hi there,

I’m attempting to add another source table to the calendar, and have been having problems. I have followed the documentation, but when opening the file or navigating to the calendar I get the following error message:

There was a problem with the script: “Capture Events by Date Range- Run SQL query.” (Drag this dialog wider to read more.)
There has been an error in the SQL. Please check the $Query in the script “Cast Events as Variables”. Click OK to continue or HALT to stop (HALT is safer).

I understand this is a generic error message suggesting that the problem lies somewhere either on the new ‘Source No 3’ Layout, or in the ‘Load Source Settings at startup…’ script. Unfortunately I haven’t been able to locate the problem myself, does anyone have any suggestions?

This which I have tried:
• Re-loading a previous version of my file and attempting to add the source again following the documentation. Predictably- the same error appears.
• Checking that the ‘id’ field is text, zscEventSummaryCalc is a calc field returning text, etc. All seems ok there.
• Running ‘Upon Opening’ manually. No change.
• Removing unused fields on the ‘Source No 3’ Layout. There are a number of fields (Gantt related for example) which are by default mapped to the relevant SampleEvents fields. As I’m not using them, I have no alternative fields to map them to. Initially I left them as they are, next I tried deleting all unused fields. Neither had any effect on this problem.

Additional things that may or may not be relevant:
• The table I’m adding is new, it currently holds no records.
• I’m using the Pro version of the calendar, and FMP13.
• Source No 2 is not being used. The layout, and script steps in ‘Load source settings on startup …’ have been left intact, but the source is not included in the ‘Load Calendar settings on startup’ script.

Thanks in andvance for your help!

I'm very impressed with the troubleshooting you've done so far! I'm not sure what's still causing that error, but if you have FileMaker Advanced, run the "Dev On" script to preserve the SQL queries, click "Refresh", then look at $$sc_Query1 and $$sc_Query2 variables in the Data Viewer. If you are familiar with the SQL syntax, you should be able to see some clues there. Beyond that I would have to see your file to help you figure this out, which I'm happy to do. You can send it to me at [email protected]

Hi Jeff,

Unfortunately I don't have FileMaker Advanced, so I'll take up your generous offer of sending it to you.

Thanks!

The problem is that the zscTimestampStartCalc and zscTimestampEndCalc calculation fields in your "NP Appointments" table are returning a Timestamp result. They need to be set to return a Number result. Make that change and you shouldn't get the error.

Brilliant, thanks for getting back so quickly! A silly error for me to make- but at least it's easy to fix