February 12, 200619 yr 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.
February 12, 200619 yr 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.
February 13, 200619 yr 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?
February 13, 200619 yr Author 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...
February 13, 200619 yr Author Well, it seems to work - but it's not for the faint-hearted... FindCustProd.fp7.zip
February 13, 200619 yr Author Here's essentially the same thing, somewhat simplified by using a value list. FindCustProd2.fp7.zip
February 13, 200619 yr 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.)
February 13, 200619 yr Author 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.
February 13, 200619 yr 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.
February 13, 200619 yr Author 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?
February 14, 200619 yr 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.
February 14, 200619 yr That was searching on an unstored calc, like an aggregate function count() or sum(). That is still a problem.
February 14, 200619 yr 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.
February 14, 200619 yr Author 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.
February 14, 200619 yr 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
February 14, 200619 yr 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.
February 14, 200619 yr 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
February 14, 200619 yr Author 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.
Create an account or sign in to comment