Multiple criteria on the same field

[Snake Plisskin]

Hi,

I have the following problem:

My clients can buy apparel and footwear, or only one of them.

My question is: how do i find clients who bought apparel (X) and footwear (S) --> they are stored in the same field

eg:

Smith X 110 pieces 2000 euro

Smith S 220 pieces 3000 euro

Jones X 100 pieces 1800 euro

Jack X 80 pieces 1600 euro

I'd like to find only Smith, since he bought both category's.

So it comes to: multiple criteria in the same field.

Thanks for helping me out.

Cheers,

Snake

[comment]

Do you have a client table?

[Snake Plisskin]

No, i only have one table with all data in it

[comment]

That makes it difficult, requiring a a complex looping script that I would not suggest to a beginner. Actually, I'd prefer to add a clients table myself. Then it can be easy, searching for specific products in a portal on a client layout.

[Søren Dyhr]

But even if you portalize stuff, will you only be able to enter the first portalrow in find mode, which makes it tough to ANDFIND several items!

The answer is to let the user make yet another request, but how intutive would you say that is? Then is a search on unstored values perhaps not always exactly what pleases users to wait for?? Significant changes have made searches on unstored fields quite fast with fm8v2 ...but it doesn't help fm7'ers.

Unfortunately have the algorithms behind the relational stuff changed the way searches are made in repeating fields making Jon Rosens old ANDFIND template from ISO e-zine in 1998, not working any more.

However does his interface have some points, worth considering! The method I have thought up of works as Jon's but unfortunately only with FM8+ due to the use of GTRR(FS) This is why I need to upload both the scripting as well as the template.

If someone can think up a fm7+ solution along these lines ...please don't hessitate, let us all know!!

There exists other methods to make ANDFINDS from the related side of the matter by stacking omitted request, and then GTRR(FS) afterwards and finalize with show omitted only in the main tables. But unfortunately does it only work when all values are covered in the search.

Say you wish to find two values in a portal somewhere. Bummer if you only enter two values as omitted request for the DeMorgan'ing reasoning here, the portals with more than two values in the portal have a foot in both camps ....

So by and large is the problem quite unsolvable with fm7, while pre 7 should utilize Jon's way, and the post 7'ers could consider my method.

--sd

andSearch.zip

Edited July 26, 2006 by Guest

[comment]

The answer is to let the user make yet another request, but how intutive would you say that is?

I'd say it's a LOT more intuitive than your file... Which apart from being very difficult (and not only to a beginner), is not very useful to the poster with version 7. And your method wouldn't work without a clients table either, so what's the point?

So by and large is the problem quite unsolvable with fm7

Oh, it's quite solvable - if you have the proper relational structure. It's not THAT difficult to script multiple requests. It's even possible to make the search on stored fields only - see the two files I have posted here. But, as I found out in that thread, the difference between stored and unstored is not dramatic enough to justify the effort.

[Søren Dyhr]

if you have the proper relational structure. It's not THAT difficult to script multiple requests. It's even possible to make the search on stored fields only

This was the aim with the post I made, I struggled to see a solution to a pretty commonly occuring problem ...who was it we sold n and nn to just some weeks ago??

I was not long ago taken by surprice, that Jon Rosens old template didn't work any more ... so my line of posting made you reply exactly as I wished you to - namely to give perspective!!!

Thanks a lot!

--sd

Edited July 27, 2006 by Guest

[nomad]

I have a problem similar to the origional poster "Snake Plisskin's" problem, except I have DO have a related 'clients' table with an 'items' portal in it.

If I do a multiple request find in the 'items' portal.

request 1 for "X"

request 2 for "S"

I get a results set of clients who ordered items "X OR S" not client who ordered "X and S".

I looked over Søren Dyhr's solution but unfortunately, it is over my head. (perhaps I could understand it with a bit more context)

What are the general approachs to this problem?

My best guess would be to write a script that finds a Clients with item 'X' orders... marking those client records in a temporary field and then searchs again for marked client records that had 'S' items.

Any nudges in the right dirrection on this issue would be greatly appreciated.

Thanks!

Jason

[comment]

You are almost there: first, find clients with item X. Then search for clients with item Y, but instead of Perform Find, do a Constrain Found Set.

[nomad]

aaaaaahhhhhh! That's what the "constrain found set" is for! I couldn't see the answer because it was right in my face. (duh!)

Thank you wery much Mr. comment!