Filtering a "Search"

[daddyz]

I have a database setup with the field "salesperson" The salesperson field is a lookup field based on user_name(). How do you write a script that puts the current form in "find by form mode" and automatically sets the 'salesperson" field to the "current user" so it will only return records that belong to him?

Thanks so much,

[Keith Hubert]

Re: Filtering a "Search"

Hi JC,

This sounds like you want to filter the records on a form based on the user name. In design mode of the form, right mouse click and select properties. Now select Order/Filter. In the filter enter user_name()=field_name that_has_the_value_the_user_in_the_record.

I use that method in my School application, where a Teacher can only see their own classes.

I hope that helps.

[daddyz]

Re: Filtering a "Search"

Thanks for that. But, what about when a "administrator" logs in as administrator and wants to see "all the records".


thanks so much,
JC

[Tom Cone Jr]

Re: Filtering a "Search"

JC,

You can manipulate the base query for the form dynamically. You'd implement this by opening the form on a button push, and have the button's script set the base query for the form before it's opened.

You can also use an expression to define the filter. You'd implement this by opening the form from a button push and having the button's script set a global variable which is then used by the filter expression in your form layout.

[daddyz]

Re: Filtering a "Search"

Tom:
thanks for the reply, however as a beginner with alpha that's a little over my head as far as how to correctly write the expression. I have two groups. One for "manager" who can see all the records, another group "salespeople, who can only see their own records. Is there a way to write this code into the expression of the "filter" properties of the form.

thanks
JC

[Tom Cone Jr]

Re: Filtering a "Search"

JC, I failed miserably at developing an expression that could be used to change the base query for my form. I swear I've seen the technique used before but it's been a while and I can't come up with it this afternoon. Maybe someone else can lend a hand.

[Mike Wilson]

Re: Filtering a "Search"

JC,

I have a database that has clients for counseling. The user can only see records that have them assigned as a provider. I accomplish this with a query for the users records on the Form OnInit event. I have a button on the form that is password protected for administrators password that will query for records where they are not the assigned provider when the password is accepted.

If you need some escorting through this, holler back.

[daddyz]

Re: Filtering a "Search"

hate to bring up the word "filemaker" but: I am re-doing a fm application.

In FM I have a simple script that opens the form and "show all" records if it is a manager log in.

Open form
if(account name)=manager
Show all records
else
Enter find mode
set field (salesperson)
user_name()
perform find.

Why am I changing. Watching Fm sort throught Thousands of records is like wating paint dry on a wall. A long slow process.

I'm looking for the equivalent in alpha.

thanks

[G Gabriel]

Re: Filtering a "Search"

Tom:

I failed miserably at developing an expression that could be used to change the base query for my form. I swear I've seen the technique used before

There is quite a few of those:
form.INDEX_SETEXPLICIT()
form.BASEQUERYRUN()
<tbl>.INDEX_PRIMARY_PUT()

[Tom Cone Jr]

Re: Filtering a &quot;Search&quot;

G, thanks. I was trying manipulate the embedded filter expression and couldn't. There are other methods to solve the problem as you correctly note and as I mentioned this morning.

JC, here's an example that is probably pretty equivalent to what you set up in Filemaker. Unzip the attachment to an empty folder. The database has security enabled. The Administrator password is 1234. There are two users. "LicensedUser" logs in with password abcd and is a member of the Managers group. "TestUser" logs in with password xyz and is not a member of the Managers group.

The Customers table contains a field called "Salesman". LicensedUser's name is some of the records (as Salesman) and TestUser is the salesman inthe others.

This should match what you're used to seeing (I hope).

The database contains one form "MyTestForm" and one script "OpenFormFiltered". YOu'll find the script on the Code page of the Control Panel.

I suggest you familiarize yourself with the table and with the form after having logged in as Administrator or "LicensedUser". Then run the script. You'll have access to all records.

then log in as "TestUser". Run the script again. All you'll be able to reach are TestUser's records.

Hope this helps, and will be happy to answer questions.

-- tom

[daddyz]

Re: Filtering a &quot;Search&quot;

Thanks so much for that, I will take a long look at that tomorrow.

thanks again
JC

[Mike Wilson]

Re: Filtering a &quot;Search&quot;

JC,
I hijacked Tom's example and put together an example of the approach I related in my post #7 of this thread. See if this might approach your needs.

[Tom Henkel]

Re: Filtering a &quot;Search&quot;

We have a similar situation where employees can view their available time, but a supervisor needs to be able to view his own time or the time of those reporting to him. We have a form that shows the time, and just filter the form based on who is looking.

This is the code to accomplish it. Obviously, not all of it, but enough to give you an idea.

	[COLOR="red"]filter = "EMP_ID = [varN->sup1]"[/COLOR]
	query.filter = replace_parameters(filter,local_variables())
	query.order = "EMP_ID"
	'Open the layout_type showing just the records that satisfy the filter
	Form.ViewQueried("fEE_TimeAvail",query.filter,query.order)
 else
	if access <90 .or.access >999
		sup1 = 0
		sup= upper(trim(lookupc("F",access,"name","secure2.dbf","access")))
		sup1 = lookupn("F",sup,"emp_id","g_employ.dbf","name")
		if sup1 >0
			access = sup1
		end if
		traceln(sup)
		[COLOR="Red"]filter = "Supvr_ID = [varN->sup1]"[/COLOR]
		query.filter = replace_parameters(filter,local_variables())
		query.order = "EMP_ID"
		'Open the layout_type showing just the records that satisfy the filter
		FORM.ViewQueried("fEE_TimeAvail",QUERY.FILTER,QUERY.ORDER)

What we're doing her is filtering the form view on either the employee's ID, or the Supervisor's ID. If we use Supervisor's ID, it is a different filter. Again, this is NOT the entire script, but it should give you an idea.

Tom

[Tom Cone Jr]

Re: Filtering a &quot;Search&quot;

Mike, thanks for the additional example. Always glad to see a different approach.

I note a key difference in the result however. In my example the base filter for the form is set by the script. When the form opens the user cannot push the "show All" button on the toolbar to release the query and see all the records. In your example the base filter remains untouched and a query is run to limit the range of visible records. Your query can be released by the "Show All" button on the toolbar.

If you are curious here's what the onInit script in your form would look like if you wanted to set the base filter for the form instead of running a query on top of (against) an unfiltered form:

dim global vLSid as C

if vLSrank = "05"
	this.basequeryrun(".t.","Name")
else
	this.basequeryrun("Staff_id = " + quote(Var->vLSid),"Name")
end if

[Mike Wilson]

Re: Filtering a &quot;Search&quot;

Tom,
Thanks for pointing that out, I had forgotten that. So rarely is it that I have need (desire) to batten down a form like that. I guess I have just used the approach that none of the toolbars in a restricted form in the final applications have the Query and Show All as options, so I guess that's how I have handled it. Nice to have more options, thanks.