FileMaker 12 and ExecuteSQL

Question

I have this that works:

ExecuteSQL ( "Select  email_Full from staffing where Branch = ? and EmployeeID  = ? and Title= ?"; " "; " "; "001" ; "33748"; "Supv V" )

but I need the first parameter to be within the range of 001-300; the second parameter to be >0 and the third could be Supv, Supv 1, Supv 2, Supv 4 or Supv 5

All my attempts fail.

(If you put four spaces at the start of a line, it will format it as code for display.) — Andrew Morton, Feb 18 '15 at 19:12

score 1 · Answer 1 · answered Feb 19 '15 at 11:49

I suggest:

 ExecuteSQL ( " Select  email_Full from staffing 
                WHERE Branch = ? 
                AND EmployeeID  = ? 
                AND Title= ?
                AND EmployeeID > 0
                AND Branch BETWEEN 1 AND 300
                AND Title IN (\"Supv\", \"Supv 1\", \"Supv 2\", \"Supv 4\" or \"Supv 5\")"; 
                ""; ""; GetAsNumber("001") ; "33748"; "Supv V" )

I am not sure about EmployeeID check, I think you are actually need NOT NULL instead of >0

I casted Branch as number at parameter level, overwise you will have to test it against a list of strings with IN or LIKE.

score 0 · Answer 2 · answered Feb 18 '15 at 17:54

0

Try this:

ExecuteSQL ( "SELECT email_Full FROM staffing WHERE Branch < 300 AND EmployeeId > 0 AND Title LIKE '%Supv%'" ; "" ; "" )

answered Feb 18 '15 at 17:54

CristosLC

412
4
16

Branch < 300 will allow branch 0 and negative numbers, '%Supv%' will match Supv3 and Supv 6, which could be OK, but it does not really need the first %. – Nicolai Kant Feb 19 '15 at 14:02

score 0 · Answer 3 · answered Feb 18 '15 at 18:59

0

I have made headway with ExecuteSQL ( "Select email_Full from staffing where Branch = ? and Employee_ID > ? and Title LIKE ?"; " "; " "; "024" ; "1"; "Supv%" )

If I use the FileMaker field for branch it returns email full for all branches. I need it one at at time.

answered Feb 18 '15 at 18:59

Jim Shelton

1

1

You ought to make this into an edit on your original question. That way, we can easily see what progress you have made and it keeps all the information we may need to answer your question in one place. And welcome to Stack Overflow :) – Andrew Morton Feb 18 '15 at 19:14
LIKE "Supv%" will match Supv 5, Supv 6, Supv 1000 etc.. Make sure you are happy with this. You selecting records from stuffing and not from branches, so you have branch email record for every staff member and not for branch. You can try DISTINCT email_Full, which is easier, or you can try to join on Branches table – Nicolai Kant Feb 19 '15 at 11:54

FileMaker 12 and ExecuteSQL

3 Answers3