Portal Filtering using PatternCount

[DMDewey]

It's my first time to actually try this. I have an art database with three tables Artwork, Inventory (supplies) and Inventory Used.

In the Artwork layout, I'm trying to show two portals: One with all the Inventory items with a search box and the other will have a list of inventory items used to create the art.

I'm having a problem searching the portal using a global constant ("1") compared to a pattern count of the search string.

I have all three tables related with two extra instances of the Inventory table used to list all of the items and filter them (creating a total of 4 relationships).

I'm trying to take the search string and use it in a patterncount calculation that searches the inventory name and then use that calculation field to relate it to a constant of "1". Does this make sense?

Hopefully the sample file will make sense. Any help would be greatly appreciated.

portal_filtering.zip

[DMDewey]

I've read that portal filtering works by using the relationship so I use a patterncount calculation to take the search string and compare it to the inventory name. It'll return a "1" or more if it is found so I use this calculation field to relate it to a global constant ("1") in another table occurance of the same table. So if the search string is found, then at least a "1" is returned for that field and I use a greater than or equal operator in the relationship.

There must be something I'm doing wrong. I don't really know if I'm performing a legal operation.

[DMDewey]

I guess relating a calculation to a global doesn't work so I made a script to search the inventory and it works how I need it to. Now I just need to make a script to add it the Inventory_Used table.. Is there a better possible way to search the portal?

portal_filtering_mod.zip

[Fenton]

You can create a global field, in Artwork, then relate directly to a TO of Inventory. Your relationships for that don't work; they have |---| unstored fields on both sides. The target side cannot be an unstored field (unless you're doing a Cartesian (X) relationship)

It is difficult to do PatternCount ( target field; global field ) in a relationship, because the target would be an unstored field. A relationship matches on lines. You could create an "exploded" target field in Inventory, with a calculation, text, stored:

Substitute ( Inventory_Name; " "; ¶ )

Which would get you:

Cadmium

Red

Light

Which could be matched by a global field in Artwork with the word "red".

[Fenton]

Actually, if you really need a PatternCount() instead of a word match, there are ways to do it. One way is to "explode" the target field, into all its pieces.

Another way would be similar to what you did, but using a variation of "Ugo's method", to look thru the middle table. But I'd rather not try that unless you need it -|

[DMDewey]

I got it. Thanks for the info. I knew I was doing something wrong. I wanted to make sure that I wasn't doing too many extra steps when I could've done something much simpler. The search script I created will suffice this time, but I'll bookmark this thread just in case I run into a similar problem.

BTW...I've used your Timekeeping database as a model for my own time tracking system. I'll have to send you a copy of it for you to check out. It's got a webviewer with a javascript counter to show you the time elapsed.