SeedCode Support Forum • View topic - Resource Filtering Script Error

Posts: 4
Joined: Thu Dec 27, 2012 8:26 am

Posted: Thu Dec 27, 2012 7:33 pm

I just finished going through the quick integration for Calendar Pro. Opted to not include Gantt. At the end all was ok with my events. My next step was to get to adding my resources and modify filtering process. After, changing the "Resource" Value List and after I ran upon opening script, I attempted to filter to a resource. Everytime I select a resource a Script Error Dialogue prompt pops up.

"...problem with script "Capture Events by Date Range - Run SQL Query...error in SQL...check $Query in the script 'Cast Events as Variables.'...."

Did I miss something? I also tried to replace the Resource value in the --- IF IsEmpty ( $sc_Resources )--- condition. Inevitably I would like to move on to the other resource Filtering instructions and to have full integration. Just got stuck at this point. Thanks.

Max

Attachments

: Screen Shot 2012-12-27 at 5.19.45 PM.png (191.45 KiB) Viewed 6578 times

Top

SeedCode Staff

Posts: 691
Joined: Mon Feb 28, 2011 2:47 pm

Posted: Fri Dec 28, 2012 8:24 am

Hi Max,

That's the general error we throw when there's an error with the SQL Query create to find the events. We don't get much specifics from FileMaker when there's an error (just a "?") so we just have the one error too.

We have some troubleshooting tools for looking at this if you have FileMaker Advanced. If you do, then we have a script called Dev_On which just tells some scripts to not clear the global variables they use. If you run this script and then cause the error. The variables that have the queries that caused the error are still there and we can try to identify why they failed.

The Script:

: Script.png (43.11 KiB) Viewed 6573 times

The Queries in the Data Viewer:

: Data Viewer.png (54.63 KiB) Viewed 6573 times

And the Query Body:

: Query1.png (89.8 KiB) Viewed 6573 times

If you could paste the queries for $$sc_Query1 and $$sc_Query2 here, we might be able to figure it out. If you don't have advance, then send me an e-mail at [email protected].

Thanks!
-Jason

Top

Posts: 4
Joined: Thu Dec 27, 2012 8:26 am

Posted: Fri Dec 28, 2012 11:22 pm

Thanks. I ran the dev1 program. Error still occurred. I also made the full integration instead of the quick just one and just finished and have similar error.

On the quick integration ...
Query1 Reads

SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampStartCalcNum" Between 63492249600 and 63493545599
Union
SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampEndCalcNum" Between 63492249600 and 63493545599
Union
SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampStartCalcNum" < 63492249600 and a."zscTimestampEndCalcNum" > 63493545599 ORDER by 1

Query2 Reads

SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampStartCalcNum" Between 63492249600 and 63493545599
Union
SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampEndCalcNum" Between 63492249600 and 63493545599
Union
SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."__900RANumber" LIKE '-----' and a."zscTimestampStartCalcNum" < 63492249600 and a."zscTimestampEndCalcNum" > 63493545599 ORDER by 1

__________________________________
On the fully integrated one ...

Query 1 Reads

SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."zscTimestampStartCalcNum" Between and
Union SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."zscTimestampEndCalcNum" Between and
Union SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_920DateOut", a."_925DateDueIn", a."_921TimeOut", a."_926TimeDueIn", ''||a."_915_200aUnitNumber" , 1, a."zscTimestampEndCalcNum" FROM "SampleEvents" a where a."zscTimestampStartCalcNum" < and a."zscTimestampEndCalcNum" >
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."DateStart", b."DateEnd", b."TimeStart", b."TimeEnd", ''||b."Resource" , 2, b."zscTimestampEndCalcNum" FROM "ToDoList" b where b."zscTimestampStartCalcNum" Between and
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."DateStart", b."DateEnd", b."TimeStart", b."TimeEnd", ''||b."Resource" , 2, b."zscTimestampEndCalcNum" FROM "ToDoList" b where b."zscTimestampEndCalcNum" Between and
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."DateStart", b."DateEnd", b."TimeStart", b."TimeEnd", ''||b."Resource" , 2, b."zscTimestampEndCalcNum" FROM "ToDoList" b where b."zscTimestampStartCalcNum" < and b."zscTimestampEndCalcNum" > ORDER by 1

Query2 Reads

SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."zscTimestampStartCalcNum" Between and
Union SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."zscTimestampEndCalcNum" Between and
Union SELECT a."zscTimestampStartCalcNum", ''||a."__900RANumber", a."_90100FKFullNamePhone", ''||a."_915_205Model" FROM "SampleEvents" a where a."zscTimestampStartCalcNum" < and a."zscTimestampEndCalcNum" >
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."zscToDoSummaryCalc", ''||b."Status" FROM "ToDoList" b where b."zscTimestampStartCalcNum" Between and
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."zscToDoSummaryCalc", ''||b."Status" FROM "ToDoList" b where b."zscTimestampEndCalcNum" Between and
Union SELECT b."zscTimestampStartCalcNum", ''||b."id", b."zscToDoSummaryCalc", ''||b."Status" FROM "ToDoList" b where b."zscTimestampStartCalcNum" < and b."zscTimestampEndCalcNum" > ORDER by 1

Top

SeedCode Staff

Posts: 691
Joined: Mon Feb 28, 2011 2:47 pm

Posted: Sat Dec 29, 2012 9:38 am

Great, thanks for posting those.

I think the issue is that your events Table Occurrence name is not SampleEvents, which is the default. If the TO name is something else, you need to adjust the script steps at the top of the script

Load Source Settings at Startup --- Describe Your Sources Here ---

to reference your TO name (see below). You will need to restart the file for this change to take effect.

If that's not it, I'd like to take a look at the file, so please e-mail me at [email protected] and we can make arrangements for that.

Happy New Year!

: SourceSettings.png (173.88 KiB) Viewed 6562 times

Top

Posts: 4
Joined: Thu Dec 27, 2012 8:26 am

Posted: Sat Dec 29, 2012 10:04 am

I double checked once more and went to the "Load Source Settings Startup.." Script and also checked that against the drop down list of tables (on both versions I did). I also double-checked that the table that Source No 1 is using. I don't think that it the T.O. setting on that script line item. As I have redone the whole process as well.

I have other suspicions on what may be on my side. I will try to recreate a simple event/contact database and see what happens. I will definitely email you. THANKS. Happy New Year.

Top

JasonYoung JasonYoung's Profile SeedCode Staff Posts: 691 Joined: Mon Feb 28, 2011 2:47 pm	Posted: Sat Dec 29, 2012 11:34 am Sound good. Let me know if you need a second set of eyes on it. Happy New Year to you as well!
Top

Max Macasieb Max Macasieb's Profile Posts: 4 Joined: Thu Dec 27, 2012 8:26 am	Posted: Wed Jan 02, 2013 10:30 am Jason, On the quick integration version clarifying one more step may help other users. --> "Load Calendar Settings on startup ..." script has to be updated: on the section "Map Filters to Field" This and updating the Value list took care of the issue mentioned on this thread. ... Regards, Max
Top

JasonYoung JasonYoung's Profile SeedCode Staff Posts: 691 Joined: Mon Feb 28, 2011 2:47 pm	Posted: Wed Jan 02, 2013 11:15 am Thanks Max, Glad you figured it out. I added a link to filtering in the Going Further section there at the bottom, so hopefully that will help. Cheers and Happy New Year.
Top

Resource Filtering Script Error

Who is online