Filter records on a form layout

[RDWirr]

I have a single table employee data file that contains all the usual kind of data including the department each person works in. I have a form layout that displays each employee’s detailed data and also a list view layout that shows the list of employees. I need to restrict access to the records of employees depending on the department they are in and also whether they are currently employed or not. I have set up layouts (the form and the list) that show all employee records and the alternate layouts that show only the filtered records I think I can determine access to the unrestricted layouts and restricted ones via security privileges. My problem is filtering the records.

I have a global field in the ‘Employee’ table for entering the filter criteria (a pilcrowed list of which departments are unrestricted). I have set up two TOs off the same ‘Employee’ table, let’s call them EmpMain and EmpFilter with a relationship that keys the global criteria field in the EmpMain to the ‘Department’ field in the EmpFilter TO. I have the unrestricted layout with the EmpMain as the underlying TO and the restricted layout with the EmpFilter TO. But I see all the same records in both layouts regardless of what I have in the criteria field. I know this is something very elementary but I have been playing with it for hours without getting it.

My eventual goal is to use the same restricted layouts to show different data sets depending on which privilege group logs in but first things first.

I also haven’t figured out the active employee one yet either but I think this will follow the same logic using a checkbox on each record and matching it to a global field and including this in the same relationship as the department filter.

Can anyone point me in the right direction?

Thanks in advance,

RDW

[RDWirr]

OK maybe it will help if I attach a sample file.

I am completely sure I am doing something dumb here maybe a mental block but anyway, if anyone could give me a hint, I would be much obliged.

Thanks,

RDW

Employee.zip

[efen]

I have only had a quick look at your file but I notice that your filtered form and list are on a EmpFilter TO layout - they should be on a EmpMain TO layout with the fields from EmpFilter TO

[RDWirr]

Hi Efen,

Thanks for your suggestion. This is something I tried earlier and just confirmed again, when I do that, change the filtered form to be based on the EmpMain TO and leave the fields on the EmpFilter TO. Then I only get the full number of records displayed but all showing data from only one record, so in the case of this sample file, 6 copies of Harpo's record.

The relationship filter works fine if in a layout with the EmpMain as the TO with a portal based on EmpFilter.

I am still stumped.

Thanks for your input.

RDW

[efen]

Is the attached - your file amended - any help?

Employee.zip

[RDWirr]

Hi Efen,

Thanks for sticking with me on this. Actually what I need is to filter the records accessible to the whole filtered form layout. No portals are required. Although, the portal works well for the list view but I still need a way to show only the records matching the filter key on the filtered Form layout. Maybe I have to relent and use a separate table to match the filter to. I was hoping to use a self join for this and I thought it is possible but I haven't found the way yet.

Rgds,

RDW

[efen]

but I still need a way to show only the records matching the filter key on the filtered Form layout.

I'm not sure what you want here because the filtered form layout does only show the filtered records?

[RDWirr]

Hello Efen,

The version you posted does show only the filtered records in the portal you inserted on the filtered form layout but the rest of the form layout still allows me to page through all the (in this case 6) records filtered and unfiltered. Unless we are looking at different versions. I just downloaded it again and it's like that.

I need to restrict the filtered records completely from the filtered layouts.

I know I can restrict records from the privileges setup with a calculation but this still shows the record places with a "No Access" in all the fields but for my application, there are a lot of restricted records and it would hinder the user having to deal with all those 'no access' records. OK I am getting off on a tangent here but anyway, my meaning is, I don't need a portal on the filtered layout, the list view serves that purpose. I just need to filter the records completely from the layout. Then I'll restrict access to the non-filtered layout from the security setup.

Thanks Efen, I appreciate your help on this.

RDW