AnsweredAssumed Answered

Field Displays ? if not Full Access

Question asked by carmenfisher on Apr 10, 2018
Latest reply on Apr 13, 2018 by carmenfisher

Like • Show 0 Likes0
Comment • 9

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?

philmodjunk Apr 13, 2018 8:26 AM

Correct Answer

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.

See the reply in context

No one else had this question

Outcomes

techt Apr 10, 2018 3:32 PM

I'll start with the simple possibility that as [Full Access] you're not seeing all records or the total. FMP usually displays a ? when the field isn't wide enough to show all of the information. Widening the field will resolve that (especially when developing on a Mac and having users on Windows). Sometimes when a calculation isn't set correctly (number instead of text) for the result, but that doesn't sound like this.

Actions

carmenfisher Apr 12, 2018 11:06 AM

Thanks for the suggestion, but I'm pretty sure it's not the field width.

Here is what it looks like when logged in with Full Access:

Here is what it looks like when logged in as a regular user:

The privileges on the three ? fields are the same as on the other fields. That's why I'm wondering if it has something to do with MTDSales being an ExecuteSQL calculation (which is subsequently used by the other two fields). I'm not well versed in SQL.

Actions

techt @ carmenfisher on Apr 12, 2018 11:09 AM

Is there a script that runs the eSQL statement? Could it be that the script needs to run in full access? Another possibility is that the privilege set for the regular user doesn't allow view into that field set.

Actions

carmenfisher @ techt on Apr 12, 2018 1:36 PM

There is no script. MTDSales is a calculation field using ExecuteSQL in the calculation (shown in the original post). It sums the field Performance::SalesTotal, to which the standard user has view only access. I tried giving that field (Performance::SalesTotal) modifiable access but it didn't make any difference. MTDSales, TodayLYGoal, and TodayGSGoal were view only but I upped them all to modifiable trying to make this work. I even tried turning on "yes" to Edit privileges in the Performance table to no avail.

If I can't solve this, I've considered just "hard wiring" the MTDSales by running a schedule at night to calculate it. But I have a feeling it's some privilege thing I'm overlooking. Does SQL have any statements that affect access?

Actions

techt @ carmenfisher on Apr 12, 2018 2:03 PM

I'm not aware of any access on SQL statements, but someone else here might. I'm guessing the calculation field is unstored. Certainly looks like a privilege issue at this point. What if you create a second calc field and have it auto-update with the value from the SQL calc field, and then put that field on the layout? Not a great solution, but might expose some other issue if it works.

Actions

carmenfisher @ techt on Apr 12, 2018 4:18 PM

You are correct that the calculation field is unstored since it needs to update daily. I tried adding a second calculation whose value was MTDSales, but got the same result -- shows up for Full Access, ? for standard user. I tried to uncheck the "unstored" property, but a dialog said I couldn't because it referenced an unstored field.

Thanks for the suggestion, though.

Actions

techt @ carmenfisher on Apr 13, 2018 7:49 AM

These are the kinds of problems that can drive you nuts. What happens if you run a script to replace field contents on the second field, and have it copy the values from the SQL calc field?

Actions

philmodjunk Apr 13, 2018 8:26 AM

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

Actions

carmenfisher @ philmodjunk on Apr 13, 2018 12:04 PM

Thanks, the problem is fixed. I think the issue was not having sufficient access for the Loc, SaleMonth, and SaleYear fields. I set them all to "view only" and the calculation started working. My takeaway is to be sure any field referenced in the calculation can be "seen" by that user.

Actions

9 Replies

techt Apr 10, 2018 3:32 PM
Mark Correct
Correct Answer
I'll start with the simple possibility that as [Full Access] you're not seeing all records or the total. FMP usually displays a ? when the field isn't wide enough to show all of the information. Widening the field will resolve that (especially when developing on a Mac and having users on Windows). Sometimes when a calculation isn't set correctly (number instead of text) for the result, but that doesn't sound like this.
Like • Show 0 Likes0
Actions
carmenfisher Apr 12, 2018 11:06 AM
Mark Correct
Correct Answer
Thanks for the suggestion, but I'm pretty sure it's not the field width.
Here is what it looks like when logged in with Full Access:

Here is what it looks like when logged in as a regular user:
The privileges on the three ? fields are the same as on the other fields. That's why I'm wondering if it has something to do with MTDSales being an ExecuteSQL calculation (which is subsequently used by the other two fields). I'm not well versed in SQL.
Like • Show 0 Likes0
Actions
techt @ carmenfisher on Apr 12, 2018 11:09 AM
Mark Correct
Correct Answer
Is there a script that runs the eSQL statement? Could it be that the script needs to run in full access? Another possibility is that the privilege set for the regular user doesn't allow view into that field set.
Like • Show 0 Likes0
Actions
carmenfisher @ techt on Apr 12, 2018 1:36 PM
Mark Correct
Correct Answer
There is no script. MTDSales is a calculation field using ExecuteSQL in the calculation (shown in the original post). It sums the field Performance::SalesTotal, to which the standard user has view only access. I tried giving that field (Performance::SalesTotal) modifiable access but it didn't make any difference. MTDSales, TodayLYGoal, and TodayGSGoal were view only but I upped them all to modifiable trying to make this work. I even tried turning on "yes" to Edit privileges in the Performance table to no avail.

If I can't solve this, I've considered just "hard wiring" the MTDSales by running a schedule at night to calculate it. But I have a feeling it's some privilege thing I'm overlooking. Does SQL have any statements that affect access?
Like • Show 0 Likes0
Actions
techt @ carmenfisher on Apr 12, 2018 2:03 PM
Mark Correct
Correct Answer
I'm not aware of any access on SQL statements, but someone else here might. I'm guessing the calculation field is unstored. Certainly looks like a privilege issue at this point. What if you create a second calc field and have it auto-update with the value from the SQL calc field, and then put that field on the layout? Not a great solution, but might expose some other issue if it works.
Like • Show 0 Likes0
Actions
carmenfisher @ techt on Apr 12, 2018 4:18 PM
Mark Correct
Correct Answer
You are correct that the calculation field is unstored since it needs to update daily. I tried adding a second calculation whose value was MTDSales, but got the same result -- shows up for Full Access, ? for standard user. I tried to uncheck the "unstored" property, but a dialog said I couldn't because it referenced an unstored field.
Thanks for the suggestion, though.
Like • Show 0 Likes0
Actions
techt @ carmenfisher on Apr 13, 2018 7:49 AM
Mark Correct
Correct Answer
These are the kinds of problems that can drive you nuts. What happens if you run a script to replace field contents on the second field, and have it copy the values from the SQL calc field?
Like • Show 0 Likes0
Actions
philmodjunk Apr 13, 2018 8:26 AM
Unmark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
carmenfisher @ philmodjunk on Apr 13, 2018 12:04 PM
Mark Correct
Correct Answer
Thanks, the problem is fixed. I think the issue was not having sufficient access for the Loc, SaleMonth, and SaleYear fields. I set them all to "view only" and the calculation started working. My takeaway is to be sure any field referenced in the calculation can be "seen" by that user.
Like • Show 0 Likes0
Actions

Field Displays ? if not Full Access

Outcomes

Related Content

Recommended Content