Mark Geerdes

I don't have an answer for you, but can verify your issue, I'm running into this too. I can also tell you that last month, it worked fine, so something that recently got upgraded is causing this. Oh, also, FileMaker doesn't officially support 2012 R2, so not sure we can get any help from there.

I don't know if you still need this info, but use the SendEmailWithAuthentication to do this.

okay, fixed the issue. I thought I had tried this once before posting here, but maybe my syntax was wrong. The trick is to make sure you are selecting from multiple records, not one large pre-formatted list in a single field. If you run the nested select on it's own, you should get a return delimited list instead of a comma separated list. Here is the statement that works for anyone else trying to figure this out: ExecuteSQL ( "SELECT COUNT (DISTINCT "C1FD~Date") FROM "PGC1~Field Days" WHERE "C1FD~Date" BETWEEN ? AND ? AND "C1FD~Primary" IN ( SELECT workingDays FROM "@WorkingDayList" ) AND "C1FD~EMPNo" = ? " ; "" ; "" ; ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; ScheduledTimeOff::id_EmployeeNumber )

I had tried to put single quotes into another field, and then doing the nested select to get to that list, but still came up with 0. In trying to work around this issue, I came up with this: ExecuteSQL ( "SELECT COUNT (DISTINCT "C1FD~Date") FROM "PGC1~Field Days" WHERE "C1FD~Date" BETWEEN ? AND ? AND "C1FD~Primary" IN ( " & STO » Resources::workingDaysSQLList & " ) AND "C1FD~EMPNo" = ? " ; "" ; "" ; ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; ScheduledTimeOff::id_EmployeeNumber ) This works, but I would still like any ideas on why the nested SELECT didn't work if anyone has any ideas.

Working on a nested SQL that I think should work, but is giving me wrong data: ExecuteSQL ( "SELECT COUNT (DISTINCT pgc1."C1FD~Date") FROM "PGC1~Field Days" pgc1 WHERE pgc1."C1FD~Date" BETWEEN ? AND ? AND pgc1."C1FD~Primary" IN ( 'Height Pole' ) " ; "" ; "" ; ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; ScheduledTimeOff::id_EmployeeNumber ) gives me 42 ExecuteSQL ( "SELECT r.workingDaysSQLList FROM "@Resources" r " ; "" ; "" ) gives me this: Flatbed,Escort,Tillerman,Height Pole,Assem/Disassem,Route Survey,Push Truck,Pull Truck,Load/Unload,Care/Maintenance (In-Field),Off-Duty (In-Field),Repositioning/Travel,Mentoring,Supervision,Training (In-Field),Training (In-Classroom),Shop/Office Work Note, Height Pole is 3rd in list ExecuteSQL ( "SELECT COUNT (DISTINCT pgc1."C1FD~Date") FROM "PGC1~Field Days" pgc1 WHERE pgc1."C1FD~Date" BETWEEN ? AND ? AND pgc1."C1FD~Primary" IN ( SELECT r.workingDaysSQLList FROM "@Resources" r ) " ; "" ; "" ; ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; ScheduledTimeOff::id_EmployeeNumber ) gives me 0 what's wrong with my nested select? it should work.