RLA Calc Needed

[bcooney]

I'm hoping simply posting this will let me see my mistake.

 

Data model party->party_role<-project

 

hoping to only allow a certain priv set to party records that are on the same project "team"

 

current calc:

 

FilterValues ( ExecuteSQL ( "SELECT id_project FROM party_role WHERE id_party = ?"; "" ; ""; $$ID_CURRENT_PARTY ); party::mk_projects_c )>0
or
party::zz_accountCreated = Get (AccountName)
or
$$VIEW_CONTACTS_ALLOWED =1

 

party::mk_projects_c is a multi key.

 

Other complication, if it matters, is that this is a separated solution. I am defining the RLA in the data file. $$ID_CURRENT_PARTY is set in the open_file_open, and right now I have it hard-coded to the staffID that is my "test" account with this priv set.

[Wim Decorte]

Is the trouble with any one part of this calc?  (as in - if you remove one OR, it always works fine?)

 

Some other thoughts:

 

Using ExecuteSQL in an RLA calc can induce unexpected performance problems.  I've demoed performance considerations with ExecuteSQL() at the last devcon, it revolves around open records.  Since you have no control over when the RLA calc fires, you may shoot yourself in the foot with that. 

 

 

Get(AccountName) is not reliable in an External Authentication scenario.  On a windows domain for instance users can log in with:

- DOMAINuser

- user

- user@domain

and Get(AccountName) will show what the user logged in with.  Which may not be what they logged in the last time or when the record was created.

 

 

You are using an implicit text to number conversion by asking if FilterValues > 0.  Valuecount( FilterValues) > 0  or Isempty() would be better to avoid any confusion or when FM changes the rules on those under-the-hood conversions.

[eos]

FilterValues ( ExecuteSQL ( "SELECT id_project FROM party_role WHERE id_party = ?"; "" ; ""; $$ID_CURRENT_PARTY ); party::mk_projects_c )>0

 

Not sure if you don't need 

ValueCount ( FilterValues ( … ) ) // > 0

or simply 

not IsEmpty ( FilterValues ( ExecuteSQL ( … ) ) )

or just

ExecuteSQL ( " 
  SELECT COUNT (*) 
  FROM party_role 
  WHERE 
    id_party = ? AND
    id_project IN (" & Substitute ( party::mk_projects_c ; ¶ ; "," ) & ") 
  "; "" ; ""; $$ID_CURRENT_PARTY 
)

Just the reminder that if your keys are textual, you need to single-quote them within the calculated array.

[bcooney]

"Just the reminder that if your keys are textual, you need to single-quote them within the calculated array."

 

could be that! they are UUIDs. 

 

Will take all your suggestions and report back!

[eos]

"Just the reminder that if your keys are textual, you need to single-quote them within the calculated array."

could be that! they are UUIDs. 

 

I don't think that's the problem with the calculation you posted; in the ExecuteSQL() part, you're using an injected parameter (which takes care of such details), and the list check is done by a native FM function. (The reminder related to my modified calc, not yours …)

[bcooney]

I understand. You are pointing out that IN needs a comma-separated, single quoted input string. 

[eos]

You are pointing out that IN needs a comma-separated, single quoted input string. 

 

Not exactly; I'm pointing out that IN expects a comma-delimited list of values, where each individual value needs to be single-quoted if they are strings:

 

"…myNumberKey IN (1,2,3,4)", but "…myTextKey IN ('a','b','c','d')"

[bcooney]

I read http://filemakerhacks.com/2012/05/08/fm-12-executesql-dynamic-parameters-part-2/to further understand what you meant, eos. I decided not to use IN. Couldn't get my head around all the quotes!

 

Here's what I have working:

 

$$MY_PROJECTS is set on file open using the ESQL statement above.

 

RLA for party table =

 

// this party is on a project team that I am also on.
// $$MY_PROJECTS established on file open and with every nav to projects list

 

not IsEmpty ( FilterValues ( $$MY_PROJECTS; mk_projects_c ))
or
zz_accountCreated = Get (AccountName)
or
$$VIEW_CONTACTS_ALLOWED =1 // this is set on a particular onLayoutLoad, bcs I need user to see all contacts then.

 

I am thinking of setting $$MY_PROJECTS in my primary Nav script so that it is refreshed often.

 

Wim, not using Windows Domain, so am I ok to use Get (account name)? 

[eos]

to further understand what you meant, eos. I decided not to use IN. Couldn't get my head around all the quotes!

 

Hey, it's your file, Babsi  ; maybe better to go the “native route”, considering the potential performance hit that Wim described.

[bcooney]

Well, I'm sure Mr. Blackwell will disparage my use of $$vars in RLA. I need this working today! I can experiment for a better way tomorrow. Your help and Wim's is so very appreciated!

[bcooney]

Oh, it's working in my dev copy, but not the live version. ARGH!

[eos]

ARGH!

 

“Luck” shouldn't be a contributing factor in programming, but anyway: the best of luck!

[bcooney]

What could I possibly be assuming, Oliver?? I cannot get this to work! 

 

I've even tried hard-coding the $$MY_PROJECTS var to the list of the user's project_ids, thinking that perhaps it is not getting set in the data file. 

 

However, I can "see" the $$MY_PROJECTS in both files (I send it via script parameter to the data file). The user only sees projects that they've created (that OR is true).

 

I thought perhaps that the multi key needs to be stored, so I created a temp field and put one project_id in it. I changed the RLA to:

 

not IsEmpty ( FilterValues ( $$MY_PROJECTS; temp_project_id ))

 

Still, No Access.

The only "quirk" is that a user does not "see" the data file window. It is hidden and they do not have Show Window in their custom menu set. Custom menu set? Could that impact this?

[eos]

Maybe log in with user credentials and use Script Debugger / Data Viewer, then do some on-the-fly testing, namely: why does

not IsEmpty ( FilterValues ( $$MY_PROJECTS; party::mk_projects_c ) )

not evaluate 'correctly' for the user when the individual parts seem to be 'correct'?

 

Custom menu set? Could that impact this?

 

I'd say you're grasping … but prove me wrong!

[bcooney]

I am grasping! (Shouldn't YOU be sleeping? -- glad you're not!)

 

I have logged in with user credentials and used debugger to try to "see" these values. The complication I have is that I have two rules: one for party and one for project and they are apparently conflicting.

 

Party RLA:

not IsEmpty ( FilterValues ( $$MY_PROJECTS; mk_projects_c ))
or
zz_accountCreated = Get (AccountName)
or
$$VIEW_CONTACTS_ALLOWED =1

 

PROJECTS RLA

not IsEmpty ( FilterValues ( $$MY_PROJECTS  ; id) )

or zz_accountCreated=Get (AccountName)

 

So, I need to turn off one of these rules to remove that as a factor.

[bcooney]

Well, allowed full access to Projects, so that party::mk_projects_c wouldn't run into any troubles.

 

No joy. User STILL cannot see any contacts.

 

Client has allowed me to remove RLA for now since we're just really getting started on the use of the system.

 

Hopefully this becomes a learning experience for someone!

 

btw: this is a FileMaker 13.0v4 file hosted with FM12.0v5 on OS X 10.8.5

(gonna search tech net, KB for bug reports that may be relevant)

[eos]

I'm not sure how they can be conflicting, since each is applied to a different table … ?

 

So, I need to turn off one of these rules to remove that as a factor.

 

But that's good idea. regardless. Then start deconstructing and rebuilding the individual predicates of each RLA calc.

 

I am grasping! (Shouldn't YOU be sleeping? -- glad you're not!)

 

Yes, probably I should – but then my effective problem solving contribution was rather … slight anyway 

 

Better “luck” tomorrow!

[bcooney]

G'night, Oliver. I'm hoping that walking away from this will clear my head. Just listening to me is a help :-), btw.

[Steven H. Blackwell]

Well this certainly is a vexing thread.  I don't have an immediate answer.  However http://thefmkb.com/7161may have some application here.  There are some RLA tests that just do not work.  I am not ready to conclude that this is one of them.  However, that is something to consider.

 

More later perhaps.

 

Steven

Well, I'm sure Mr. Blackwell will disparage my use of $$vars in RLA. 

No, other than the fact these are visible and perhaps editable in the Data Viewer. And the Data Viewer in 12 and 13 has a numer of security issues associated with it.

 

Steven

[bcooney]

I was hoping you'd see this, Steven. I will read the link.

 

Back from reading. Doesn't seem to be relevant to my problem. Steven, are you available for a private consult? PM me please.

[bcooney]

Here is a demo file showing the same problem. I would like a party to only see the projects to which they are assigned. I am obviously making a simple mistake, but I cannot see it :-( . Please take a look! The full access account is Admin with no password.

rla_demo.fmp12.zip

[bcooney]

Hey, it's working!? I closed the file to upload it, and then reopened it and the rule is working.

 

OK, onto next rule. Michael can only see the parties that are assigned to one of his projects.

[eos]

So not only is posting a question a proven method for clearing the cobwebs in your brain – the mere act of posting a hitherto non-functioning file immediately lets it work properly.

 

Forums must possess magical powers of debugging!

[bcooney]

Hey there!

The funny thing is that I grabbed production files and moved them to my Mac. Created local accounts in the files that use the "designer" priv set and the security RLA is working fine. So, next step is to host them on a FMS12 server without changing anything and see if the rules still work. If that works, I'll remove the FM accounts and use EA.

I'll just keep layering on until it matches production. I'm worried it's the fact that I'm hosting a FM13 file on FMS12.

[bcooney]

I've removed custom menus, and everything works just fine locally! host with FMS13 and it all fails. Here's an interesting screen shot showing how the RLA is working, but for some reason the list view returns no records.

[bcooney]

Some progress (if you want to call it that). Nav to list without any Find and user can see "his" records as per the RLA rule. As soon as any find is run, such as find all primary keys = "*", user does not see ANY records.

 

Question: Running with Standard Filemaker menus as user with limited privs. Why is Find mode not accessible in the menu bar?*

 

*duh, priv set to show minimum menus. Seems like it really has nothing to do with menus, though.

 

Next step, get rid of $$var and use global field. Wondering if this is relevant:

 

"VARIABLES

If a stored calculation field references a global variable, and the variable does not exist at the time the calc field is defined, then that calculation field will never evaluate for records that existed at the time the field was defined.

Remember that there is no such thing as an empty variable in FileMaker. If you set $$var to “” (the empty string), FM will explicitly delete the variable, releasing its memory allocation.

For those records, the calc field will always be empty (until the field is redefined). If the global variable does exist when the field is defined, then every record that exists in that table at that time will have that variable’s value in the field, and they won’t update ever again (until the field is redefined). Regardless of when the variable is defined, if it exists when a new record is created, the new record will have the variable’s value in the field — and will never update again until the field is redefined.

The upshot of this is: don’t define a stored calculation field that does nothing else but reference a global variable.

An unstored calc field that references a variable will update whenever the screen is refreshed."

 

--from Darren Terry's Shaking the Dependency Tree post at http://filemakerhacks.com/2014/08/08/shaking-the-dependency-tree/ 

 

But...I doubt it. My calc references the project::id field.

[bcooney]

Hi All! Back with a simple demo file.

 

Login as "Admin" with no password for developer access.

 

The RLA rules work beautifully when this is local. When it is hosted, the party Michael does not see "his" projects in the portal on the party form. I feel that I must be missing a fundamental rule of context.

rla_demo.fmp12.zip

[bcooney]

shameless bump.

[Wim Decorte]

Sitting on airplanes a lot tomorrow so I'll spin this up and give it a look...

[bcooney]

Wim! Thank you. It works fine local, and fails when served. Of course, that pings the "it's something to do with a global" bell in my head, but I cannot find it. Full access as Admin, no password.

[Steven H. Blackwell]

A side thought here:

 

All row level access tests are done on the Server (when there is a server).  If some function evaluates differently on the server than it does on a client, might that be the source of the issue?

 

Steven

[bcooney]

Steven, does that mean one cannot use globals in a RLA calc?

[Wim Decorte]

If you use a global field instead of a global variable then it works. It's a timing issue. I was working on a similar issue not too long ago and use the UTC milliseconds features to see when what fires and especially to see when the RLA calc gets invoked in relation to the onOpen script.  Part of your RLA calc depends on the OnOpen script completing before the RLA calc is done.  And it does that with global fields, but not so much with global variables.

[bcooney]

Awesome! I couldn't for the life of me figure out how to do this without any globals of any sort. Thank you. I will post another demo using a global field so that people have it.

rla_demo.fmp12 2.zip

[bcooney]

And it's working in Production!

 

A huge thanks to Wim Decorte, eos, LR and Steven Blackwell for their time and attention to this. Any and all help I've ever offered in this forum has been repaid in spades!