Combination of AND and OR find requests

[Marcelo]

I need to sort of combine an AND and an OR find request.

THE SITUATION:

I have a couple thousand different products in a database that contains over 100,000 records. These products are compared in groups of about 10 or 20 at different locations. The set of products compared at each location are usually different i.e. the same group of products is rarely compared at two different locations. I need to compare 2 or 3 products at a time by looking at all of those locations in which they were tested together. It is no good for me to contrast the average performance of products A, B, and C in all locations in the database, for the comparison to be fair I need to compare the average performance of those 3 products based on only those locations at which all 3 were tested next to one another. In some cases a given product appears several times in a given location. Location, product, and performance are separate fields. My goal is to summarize performance by product across those locations they have in common.

MY QUESTION: How do I perform a find to retrieve only those records for products A,B, and C that were tested at a same location? Is this at all possible in FM?

Marcelo.

[Lee Smith]

Hi Marcelo,

If I'm understanding you correctly, Have you tried?

Enter Find Mode.

Go to the Field where A would be locate and type A

Go to the Field where the Location is and type the desired Location.

Add New Request

Go to the Field where B would be locate and type B

Go to the Field where the Location is and type the desired Location.

Add New Request

Go to the Field where C would be locate and type C

Go to the Field where the Location is and type the desired Location.

click on the Find button.

HTH

Lee

[Marcelo]

My point is that there is no location I can enter in the location field beforehand. There are sometimes hundreds of locations that would have any given combination of 3 products, and I want to retrieve them all. What I would like to do is a find like you described and enter "same" for location, such that it would retrieve all those locations in which all 3 A, B, and C have been tested. So it is like an AND search for Product conditional on Location being the same, regarless of what location it is.

[-Queue-]

Are there separate fields for all the products in a single record or are you using a related record for each product?

[Marcelo]

Each record contains one product, one location, and the performance of that product at that location.

[-Queue-]

Okay. You can create a self-relationship based on location and a value list of relationship::product. Then create an unstored calculation text field of ValueListItems( Status(CurrentFileName), "yourvaluelist" ) and do a find in this field for "productA productB productC".

[Marcelo]

I tried what you suggested but I won't be able to see if it works till Monday when I return to work, it is taking a long time to process the creation of that calculation field. I did not intentionally make it an unstored calculation field like you suggested, maybe that's why it is taking so long, it might be auto-indexing the field. Anyways, I will need to make frequent searches in this field, so speed in this respect is very important for me. Why should it be an unstored calculation? Can I store it so I can index it to find things faster? Would I need to replace the Status(CurrentFileName) part of the calculation for the actual database name in order to store it?

Hey, if this works, it is going to allow me to design a heck of a database to solve a big problem we have. Thanks a ton!

[-Queue-]

If you store it, it will never update and you would be better off scripting a Set Field of a normal text field whenever you change, add, or delete a record. It must be unstored ('Do not store calculation results' selected) to update after it has been created.

Speed is dependent on the size of your file and how many related records one record may have. Assuming you have a location file, it would probably be faster to use a relationship from it to this file and put your value list in it.

[Marcelo]

I'm afraid that did not work. I was not able to try it on the entire database, I estimated that a search for 2 different products in the unstored ValueListItem field would have taken about 10. I tried it with 10,000 records and it did not work. I am not quite sure what the results of the find represented, but it was not a list of the records of the 2 products selected that share the same location. I am still not sure how the calculation you suggested is suppossed to work, but maybe the fact that there are duplicate records for each location (about 20 products per location) and duplicates for each product (some products are tested in 200 and more locations) does not go along with that calculation.

If anyone has other ideas, they would be most welcome, for now I will continue to hit my head against this wall. Conceptually, what I need is simple: a find with 3 requests, one for each product, and a command like "same" to put in the Location field of each of the 3 requests, such that only those records that have a location in common would be retrieved.

[-Queue-]

When you view the ValueListItems calculation field in Browse Mode, you should see a return-delimited list of all products related to the current record's location. When you do a find on this field, it should return all records for a location that meets the criteria, not just the ones with the specific products. You would need a script to limit the unwanted records.

I would suggest putting the find criteria in three global text fields. Enter the criteria in Browse Mode. Then

Set Error Capture [On]

Enter Find Mode [ ]

Set Field [vlicalculation, globaltext1 & " " globaltext2 & " " & globaltext3]

Set Field [productfield, globaltext1]

Duplicate Record/Request

Set Field [productfield, globaltext2]

Duplicate Record/Request

Set Field [productfield, globaltext3]

Perform Find [ ]

If [not Status(CurrentFoundCount)]

Show Custom Dialog ["No records meet the given criteria."]

Show All Records

End If

Go to Layout ["list"]

This should limit the found set to only those locations whose related records contain the criteria and have one of the products in their productfield.