Find AND on stored only

[comment]

Here's the problem in a nutshell:

Structure:

Customers <-> Invoices <-> LineItems <-> Products

Mission:

Find customers who have bought, at any time, both Product X and Product Y - using only stored fields for the find.

[Ender]

Oh sure, you finally post your own topic, and it's a tough one! Go figure.

Anyway, I don't think this find could be done in one stage. I think you'd need to find the customers for Product X, then use the IDs from the result to either filter a relationship to Customers via Product Y, or find separately for Product Y, then use the FilterValues() function to show the intersection of the two ID lists, and jump to the related Customers.

[Genx]

i dunno... maybe this is a stupid question... but why not search through a portal to products on the customers layout.. search product x, then constrain to product y?

[comment]

Because that is NOT searching on stored, therefore slow.

[comment]

I thought about filtering value lists in a cascade (there also might be product z, etc.), but I wonder if such complexity would at the end show a speed improvement over a find on related. I suppose normalizing has its down sides...

[Genx]

... sorry :

[comment]

Well, it seems to work - but it's not for the faint-hearted...

FindCustProd.fp7.zip

[comment]

Here's essentially the same thing, somewhat simplified by using a value list.

FindCustProd2.fp7.zip

[Ender]

Looks good. Starting in the Product table allows for the more general case, allowing any number of products.

Performance-wise it looks reasonable. For a max of n customers who have ordered the selected products, and m products in the found set of products, there would be O(n*m) operations, including the building of value lists (I don't know what effect the FilterValues() function itself has on performance.)

[comment]

It still feels a bit esoteric to me. There should be a more straightforward way to do this. This has, IMHO, wider implementations than my example: we keep telling people to split attributes off to a related table - but often there will be a requirement to find objects with a particular combination of attributes, e.g. a computer with both Excel and Filemaker installed, or a person that works for Company A and lives in Town X.

[Ender]

I don't know why I didn't think of it earlier, but this seems to work:

Perform a traditional Find for those Customers having Product X, then constrain that found set by those having Product Y. Same idea could be used in those other implementations.

[comment]

Huh? Can you play this again - at half-speed? What is a "traditional Find for those Customers having Product X"? Wouldn't that be searching on related fields?

[Ender]

What is a "traditional Find for those Customers having Product X"?

In the Customer layout, perform a Find for Product X (in the portal,) then enter Find Mode again, and enter Product Y (again, in the portal.) Then select Constrain Found Set.

Wouldn't that be searching on related fields?

Sure, but that shouldn't matter. FM7/8 is pretty quick about searching related fields.

[comment]

So then what was this all about?

[Ender]

That was searching on an unstored calc, like an aggregate function count() or sum(). That is still a problem.

[Vaughan]

Ender: of course!

In FMP 7/8 a related field isn't (necessarily) an unstored calculation, especially since there is no need to pipeline data across relationships like in FMP 6.

[comment]

I am sure the difference between searching an unstored calc vs. an indexed field is much more dramatic than the difference between searching a related field vs. a local one.

However, my test shows that searching in the table where the actual fields are *is* faster.

[Genx]

... so im not a complete idiot... yay for me :

[Søren Dyhr]

However, my test shows that searching in the table where the actual fields are *is* faster.

My thoughts as well, but I wonder if a selfjoin makes any difference (well it doesn't really make too much sense either) ...I came to think of Jon Rosens old...old AND find from way before constrain found set arrived. What he did was making the search by adding multible values in a repeating calcfield that only references the records value sans Extend( . - Perhaps it still has some bite to it because it only uses the behind the screens algorithm once, and not twice as constrain in the modern way would.

--sd

[Ender]

However, my test shows that searching in the table where the actual fields are *is* faster.

Sure, but it's not that bad. Less than 10 seconds in my tests with Server hosting.

[Søren Dyhr]

Sure, but it's not that bad. Less than 10 seconds in my tests with Server hosting.

But I seems to remember something "packetwatching" ...that means first attemt is going to build a virtual index while second time around it's done, the search is faster until the index vanishes into thin air ...say by opening another layout??

--sd

[comment]

Wouldn't the same be true for a flat file on a stand-alone computer? I am not sure the index needs to remain virtual here - why couldn't the local index be used over a relationship?

Anyway, I gather this technique has no earth-shattering impact. Still, scripting a "traditional" find based on a found set in Products is no picnic either, so it may have some use after all.