Restricting SELECT lists

A place for users and developers of the Xataface to discuss and receive support.

Restricting SELECT lists

Postby njw » Fri Oct 17, 2008 7:29 am

I need to restrict user access to those records that relate to their own company. I have implemented this successfully in terms of forms and lists. However, on the edit and new record forms, the select boxes show all records. I have used SQL statements to create the SELECT lists.

I have a Company field on the User's record; I have a Company field on each data record. Is there an easy way to access the User Company field and incorporate into the SQL statement?

E.g. something like

Code: Select all
SELECT Student.ID, Student.DisplayName FROM Student WHERE Student.Company = '$Company$' ORDER BY Student.FamilyName, Student.DisplayName


Many thanks

Neil
njw
 
Posts: 280
Joined: Wed Dec 31, 1969 5:00 pm

Postby shannah » Fri Oct 17, 2008 8:02 am

There's no "easy" way, but there are some ways that aren't all that hard. You can use javascript to restrict the options in the list. Or you can override the widget entirely.

To override the widget entirely, i believe you would use the 'field_name_widget' block. E.g. if the field name is "company", you would implement the method:

Code: Select all
function block__company_widget(){
    $app =& Dataface_Application::getInstance();
    $student =& $app->getRecord();
    echo '<select>';
    $res = mysql_query("SELECT Student.ID, Student.DisplayName FROM Student WHERE Student.Company = '".addslashes($student->val('Company'))."' ' ORDER BY Student.FamilyName, Student.DisplayName", df_db());
    if ( !$res ) trigger_error(mysql_error(df_db()), E_USER_ERROR);
    while ( $row = mysql_fetch_assoc($res) ){
        if ( $student->val('ID') == $row['ID'] ) $selected = ' selected';
        else $selected = '';
        echo '<option>'.$row['DisplayName'].'</option>';
    }
}

or something along those lines.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Just to be sure I understand

Postby njw » Fri Oct 17, 2008 8:09 am

I am probably wrong, but the

Code: Select all
addslashes($student->val('Company'))


appears to me to use the value of the Student record Company field, not the User record.

Am I right?

If I am, then do I need to add a bit of code at the beginning of the routine to retrieve the user record?

Also, if I use this override route will it also override any other widget settings I have used for the field?

Many thanks

Neil
njw
 
Posts: 280
Joined: Wed Dec 31, 1969 5:00 pm

Postby shannah » Fri Oct 17, 2008 8:41 am

appears to me to use the value of the Student record Company field, not the User record.

Am I right?

You are probably right.. This snippet is more of a guideline than something you can actually use out of the box.

Also, if I use this override route will it also override any other widget settings I have used for the field?


It only overrides the widget - not the label, description, etc... But any changes to the widget itself would be overridden

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

How about this ...

Postby njw » Sat Oct 18, 2008 12:58 am

The processing of the value list is done in valuelisttool.php. If I amend this to retrieve the user record, I can then amend the sql code "on the fly" to be the code needed to restrict the data.

If this is then activated using the same trigger as the filters (which I still need to determine) then it would not be necessary to amend each field as it would happen automatically?

Do you think this would work?

Many thanks

Neil
njw
 
Posts: 280
Joined: Wed Dec 31, 1969 5:00 pm

Postby shannah » Sat Oct 18, 2008 11:07 am

I would take great care in amending the valuelist tool. Valuelists currently don't take any notion of context into consideration because they are used in many contexts within a single request.

If you need a valuelist to return different values in different circumstances, I'd recommend either using javascript to filter the results on the form, or the method I outlined above, rather than trying to modify how the valuelists work.

Valuelists are great and simple - but unfortunately have some limitations.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

OK

Postby njw » Sat Oct 18, 2008 9:41 pm

but, once the names are in the select table I don't know what company they are associated with, so I would need to look up every record again to work it out and that could be slow.

As, within this application, whenever I access data from certain select tables (which I know) I will always want to filter them, it makes more sense to me to do it at source.

I would also have thought that this was true whenever a security filter was applied to an application, which was why I was suggesting the mod.

Can you suggest any circumstances when I am wrong?

Many thanks

Neil
njw
 
Posts: 280
Joined: Wed Dec 31, 1969 5:00 pm

Postby Jean » Thu Oct 30, 2008 2:05 am

Hi Neil,
If I undertood well...
Did you look at this ?
http://xataface.com/forum/viewtopic.php?t=4150#20865

Jean
Jean
 
Posts: 259
Joined: Wed Nov 07, 2007 1:30 am
Location: Pau, France

Merci beaucoup

Postby njw » Thu Oct 30, 2008 8:00 am

You understood very well!

Neil
njw
 
Posts: 280
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 25 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved