Portal Filtering...All vs. Filtered

[gillette236]

I've searched the forum and can't find this particular portal filtering question. I'm not sure there's a solution, but I figured I'd try.

I'm trying to create a layout which will list all related records in a portal or filter them by "last name". I have included a screen shot of my current layout which is currently filtering by last name. Settng up a relationship to filter by last name is easy. So is setting up a relationship to display all records. I'm tryng to figure out a way to do both.

The box next to "search by last name" is a global text field. I have a relationship from the "transaction" record (in the portal) to this global. The transaction side is also a text field. If I enter a last name, such as "smith", my portal gets filtered very nicely.

What I'd also like to do is allow the user to clear that global text field and have all "transaction" records get displayed.

I tried to get clever and create a calc field on the transaction side that was based on the global text field. If the global field was empty, I'd make the calc field empty, if it had text in it, I set the calc field to the client's last name. A little bit of testing showed that the calc field was changing accordingly. I then created an equal relationship between this calculated field and the global text field. My hope was that when the global text had a last name, the records whose calc field had the matching last names would be displayed. When the global field was empty, all the records would be displayed since the calc field on all the records would also be empty.

Of course, since I'm here, that didn't work. I always get am empty portal. With all this being said, is there a solution to my quest?

Any help would be appreciated.

[Ender]

This is possible, but first it will help us to know more about the relationship:

Is it filtered by the entire last name, or do you allow martial matches? If using partial matches, can you show what method you're using?

[gillette236]

No partial matches. The two fields have to be an exact match. I hadn't though of allowing partial matches though that would be an interesting extension to what I'm trying to do. The relationship is simply a "preferences" table with the requested last name and date range to a "transaction" table with the correlating fields.

Originally, I was just displaying all transaction records within a date range (filtering by dates was easy). The user asked if they could have the ability to filter by a client's last name. But they'd still like the ability to display all transactions if they wish. I figured I'd implement that by using an empty name box as the trigger.

[Ender]

If you didn't need partial matches, you could simply use a calculated key on the parent side:

cParentKey (calculation, text result) = case(isempty(gNameLast); "zzzAll";gNameLast)

and a calculated key on the child side:

cMatchKey (calculation, text result) = Name Last & "¶zzzAll"

But if you do want partial matches, you could use a range relationship on the text in the fields. Check out the v7 "Type Ahead" example in the Resources section on http://www.databasepros.com/ for a good demo of how this works. You can add that range relationship criteria to your existing date range relationship.

[gillette236]

Thanks, I'll give it a try.

[gillette236]

Got it working. Thanks for the help :thumbup: