Restricting SELECT lists

[njw]

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

[shannah]

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

[njw]

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

[shannah]

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

[njw]

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

[shannah]

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

[njw]

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

[Jean]

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

Jean

[njw]

You understood very well!

Neil