I have a table called Goals. I create a record each month with two goals, LYGoal and GSGoal, for each location. I calculate the number of days left in the month and the month-to-date sales. Then the goal target for the day is calculated for both LY and GS.
Most of the fields in the Goals table display correctly. My problem is that three fields display perfectly for me (logged in with full access), but non-admin users see ?. That makes me think it's a security issue, but I'm also wondering if it's because MTDSales is an ExecuteSQL calculation. MTDSales is also used in the other two fields.
For non-admin users, the Goals layout is set to View Only. The fields are set to View Only. I've tried setting the fields to modifiable with no change. The field "SalesTotal" in the Performance table (used by month-to-date sales) is set to View Only.
The calculations look like this:
MTDSales:
ExecuteSQL (
"
SELECT sum(SalesTotal) AS Sales
from PERFORMANCE
WHERE
Loc = ?
AND SaleMonth = ?
AND SaleYear = ?
GROUP BY loc
" ;
"" ;
"" ;
GOALS::Loc;
GOALS::MonthNo;
GOALS::YearNo
)
TodayLYGoal:
If ( DaysLeft = 0 ; MTDSales - LYgoal ;
If ( LYgoal > MTDSales ;
Round ( (LYgoal - MTDSales)/DaysLeft; 2)
; "0" )
)
TodayGSGoal:
If ( DaysLeft = 0 ; MTDSales - GSgoal ;
If ( GSgoal > MTDSales ;
Round ( (GSgoal - MTDSales)/DaysLeft; 2)
; "0" )
)
[Purpose of the IF statements -- When looking at previous months, I want to display the difference between the Sales and the goal. If Sales have exceeded the goal, today's target is zero.]
Does ExecuteSQL have security ramification? Or have I just missed something in my security settings?
I would carefully check access options specified in the privilege set for each table and field used in this query. On the surface, it seems like you have missed one.
In MTD alone, that would appear to be:
Table:
PERFORMANCE
Fields:
SalesTotal
Loc
SaleMonth
SaleYear
Table:
Goals
Fields:
Loc;
MonthNo;
YearNo
This assumes that Goals and Performance are two different tables and not two occurrences of the same table. Keep in mind that in Manage Security, you are looking at table names not table occurrence names as is the case in most other parts of FileMaker.