Conditional browse not properly filtering records based on another browse�s value

[rmcgaffic]

My form contains a conditional browse whose condition is is based on the value of a field in another browse and it�s not working. Have I misspecified the browse and what happened to the conditional browse's columns in the Object Explorer?

Here�s the condition expression:

Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id

I even have placed a type-in field on my form for tblPersonprofile->Profile_Id and it is being ignored.

The form behaves correctly in every manner except that this filter is not correctly applied; instead all records are displayed.

The form is based on the following set definition

tblPerson (Person_Id)
----tblPersonProfile (Person_Id, Profile_Id)
--------tblProfile (Profile_Id)
------------tblProfileItem (Profile_Id, Item_Id)
--------tblPersonProfileItem (PersonId, Profile_Id, Item_Id)

What I�m trying to do is replicate the functionality that Janna Professional Contact manager offered before it the company was acquired and that product was discontinued.

It is a very handy way to allow your user to �customize� your form without actually exposing them to form design.

If any of you have read any of Harvey McKay�s books such as �Swim with Sharks without being Eaten Alive,� you may recall that this master of the Rolodex captured 66 different items on his contact such as hobbies, schools attended, who picks up the restaurant check, kids names, etc. This information is farily unstructured and you don�t necessarily want to maintain all the information on all of your contacts.

So here is my design:
One person can have many profiles
One profile can have many items.
One person can have one item value for each item for each profile assigned to him/her.

My plan is to allow the user to maintain tblProfile and tblProfileItem on a separate form.

Any ideas why the browse is just sitting there. I even added a button to refresh the form, but that doesn�t help.

Please see frmPersonProfileItemTest in the attached database.

Click on each line in the first browse and note that the second and third browses are perfectly synchronized and that the type in value of Profile_Id also changes correctly.

But all records are displayed in the fourth browse.

The problemsome browse is Browse9. I�m also concerned that none of its columns are exposed in the Object Explorer. I would hope its possible to set color and fonts individually for a conditional browse just as easily as for an embedded browse. Is this possible?

Bob McGaffic
Pittsburgh, PA

[Peter.Greulich]

Re: Conditional browse not properly filtering records based on another browse�s value

Have I misspecified the browse and what happened to the conditional browse's columns in the Object Explorer?

Here’s the condition expression:

Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id

Bob,

I have no idea what you are trying to do. But, apparently, you are completely misunderstanding conditional browses.

Here is a simple example:
Let's say you have three (free standing) browses that you want to display conditionally on your form - browse1, browse2, & browse3.

Let's say that you want to switch browses based upon the value of a variable called vBrowse.

In such a case you could use this expression: var->vBrowse

Then you might use a multistate button assigned to that variable with values of browse1, browse2, & browse3 corresponding to 3 values on the multi button. You might need a <browse>.refresh() on the multi's OnChange event (I forget).

Make sense?

Needless to say, your expression make no sense whatsoever in this context.

[Peter.Greulich]

Re: Conditional browse not properly filtering records based on another browse�s value

Edit:

I see what you are trying to do. You want to filter "browse9" based upon an expression. That's a horse of a different color and has nothing to do with "conditional browses", per se.

[rmcgaffic]

Re: Conditional browse not properly filtering records based on another browse�s value

Peter,

Thank you for the clarification. I misunderstood the conditional browse, assuming that it was a filter than is applied to the browse. The reason for that confusion is that the browse's properties simply state that it is "specify browse by expression" which I took to mean a filter.

I know see how a condition browse works by your example. And, am I glad I was off track because not seeing the columns of the browse in the Object Explorer really bothered me.

So I guess, the correct question to ask is how to apply a filter to the 4th browse: Browse9.

I have reset the browse properites back to (X) Embed browse layout, but I'm not seeing anywhere else were I can specify a filter.

I would like to restrict the records displayed in the last browse to those whose Profile_Id equals that of the type-in field Profile_Id.

Is there a best practice for creating this filter like this?

I've reviewed all of the available Action Script options, but they all seem to prompt for an expression. I don't want to prompt for a filter value, just to apply one that is already on the screen -- the value of type-in field Profile_Id.

What I'm looking for is an Action Script something like "Apply filter to browse" which I would assign to the OnChange event for the type-in field Profile_Id.

Thanks,
Bob McGaffic
Pittsburgh, PA

[Tom Cone Jr]

Re: Conditional browse not properly filtering records based on another browse�s value

[ edit ]

[Tbaker]

Re: Conditional browse not properly filtering records based on another browse�s value

Bob

In the onchange event for Profile_id

use action scripting

1 - activate browse9

2 - use quick filter using the value in profile_id

I reattached your zip with an on change event on the type in profile_id

But I am a little lost on what you really want to see - so I hope I quessed right. I might need a little tweeking - my action scripting is rusty I would have rather created a query in xbasic for the onchange event

dim tbl as p
dim qry as p
tbl=table.get("tblpersonprofileitem")
query.filter="Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id"
query.order=""
query.options="T"
qry=tbl.query_create()

parentform:browse9.refresh_layout()

FWIW

Tom B

[rmcgaffic]

Re: Conditional browse not properly filtering records based on another browse�s value

Tom,

Thanks for your suggestion. We're getting closer to the solution.

The filter needs to be on both the Person_Id and the Profile_Id. I've extended your XBasic code to use both terms, but the filter is unsuccessful.

In your zipped database, If you click on Profile_Id "0000000000000000", it displays six records in the browse, rather than three. In other words, while the browse shows the correct profile records, it does so for both Person_Id "0000000000000000" and Person_Id "0000000000000001".

The quick filter allows only the specification of a single parameter. Is there one that will accomodate both Person_Id and Profile_Id?

I found the Multi-Field Quick Filter, but unfortuantely it generates a popup to specify the fields: "The user chooses the fields at run-time on which to filter". I was hoping I'd get to specify the fields in the action script directly, similar to what the Quick Filter permits.

Bob McGaffic
Pittsburgh, PA

PS: I extended your XBasic code as follows:

Before -- doesn't work, returns 6 records

query.filter="Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id"

After -- doesn't work, returns 6 records

query.filter="Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id .and. tblPersonProfileItem->Person_id = tblPerson->Person_Id"

After -- doesn't work, returns 6 records

query.filter="Tblpersonprofileitem->Profile_Id=Tblpersonprofile->Profile_Id .and. tblPersonProfileItem->Person_id = tblPersonProfile->Person_Id"

[Tbaker]

Re: Conditional browse not properly filtering records based on another browse�s value

Bob

Create a calculated field on the form

vpsearch = tblperson->Person_id+tblpersonprofile->Profile_id

hide it
remove the onchange event from the type in Profile_id

Create action scripting onchange event on that calculated field
Activate browse9
run quick filter using the value in calculated field vpsearch as the filter

I reattached your zip again with what I think that you want

Tom

[rmcgaffic]

Re: Conditional browse not properly filtering records based on another browse�s value

Tom,

Thank you for developing the two alternatives. I have tried both, but unfortunately have found that each of them displays Person records that should not be displayed.

For example, for Person 0000000000000000, the browse for Person_Id, Profile_Id, Item_Id displays records for
Person 0000000000000000 and
Person 0000000000000001.

So I took another approach but it isn't fully working either.

I redefined the set as follows, removed an existing link and created a new one:

tblPerson
-----tblPersonProfileItem

This results in the correct Person_Ids.

When Person_Id 000000000000000 is selected at the top of the form, only its records are displayed in the browse at the bottom of the form Browse9.

The problem is that this browse now displays all Profile records for the person rather than just those of the selected profile.

I made this change on both your examples without success.

Gee, this is like working with Jello!

Bob McGaffic
Pittsburgh, PA

[Tom Cone Jr]

Re: Conditional browse not properly filtering records based on another browse�s value

Bob, your set has 11 tables. The structure reminds of an important article at www.learn alpha.com called "Simplify your Applications for Added Performance". If you intend to use this set for data entry may I suggest that you spend a few minutes reviewing Dr. Wayne's article? The article will be even more valuable to you if the app is to be deployed over a local network. -- tom

[Peter.Greulich]

Re: Conditional browse not properly filtering records based on another browse�s value

In addition to what Tom said (the PKW article is a must-practice rule), a couple of other points:

Why not link Tblpersonprofileitem table to the Tblpersonprofile table? Then your filter will always be in effect.

Although the need to simplify your set is a given, conversely you can also refer to the same table more than once if necessary. Thus, you could maintain the linking system you have + the one I just suggested.

And lastly, why 0000000000000000? That's ten quadrillion possible records. IOWs, why introduce impossible to read numbers if there is no earthly chance of ever having that many records. My suggestion is to figure out the maximum number of records that you anticipate the db will ever hold, and multiply by 10. Say that you think 10,000 is the max. Then use a 5-character filed which effectively gives you 100,000 records (i.e. 99,999 + 00000).

[Peter.Greulich]

Re: Conditional browse not properly filtering records based on another browse�s value

Oh. Your form has 2 hidden tree controls? Probably doesn't help anything.

[rmcgaffic]

Re: Conditional browse not properly filtering records based on another browse�s value

Peter,

I am still in the prototyping stage with my project. I just accepted Alpha's default width of character fields. I am just trying to get Alpha to behave similar to other development tools I have used, and if successful, clean up the tables and forms later. In other, right now I'm in "rough cut" mode.

Thanks for the hint that a table can be referred to more than once in a set. I was not aware of this functionality.

Bob McGaffic
Pittsburgh, PA

[Peter.Greulich]

Re: Conditional browse not properly filtering records based on another browse�s value

...right now I'm in "rough cut" mode.

No problem.

[rmcgaffic]

Re: Conditional browse not properly filtering records based on another browse�s value

Peter,

I am still in the prototyping stage with my project. I just accepted Alpha's default width of character fields. I am just trying to get Alpha to behave similar to other development tools I have used, and if successful, clean up the tables and forms later. In other, right now I'm in "rough cut" mode.

But one last question:

Your suggestion to create the following link:

tblPersonProfile
-----tblPersonProfileItem

was actually my very first design, but my problem was that I thought I had to choose between Person_Id or Profile_Id as the linking key.

Triggered by your suggestion, I just figured out the set definition allows compound keys! That both the parent key and the child key can be expressions.

[Light bulb goes on!!!].

This is powerful stuff! The compound key was literally the "missing link".

Bob McGaffic
Pittsburgh, PA