I have a found set of customers who bought products A and B. Some bought only one, others bought both. I want to identify the customers who bought both A and B, then remove those records from the found set.
I considered using variables and a loop to compare and mark the records.
My assumptions: there is a customer table; there is a purchase table; the customer and purchase tables are linked via a relationship.
Perform a find from a layout that uses the customer table. Ensure this layout has a portal that points to the purchase table. You will need three find requests: find related product A; find related product B; omit related product A AND related product B.
To clarify, I have a customer table and a purchase table. Here is what worked:
Go to purchase table and clear flags
Find product A OR B
Find duplicate (!) customer records and constrain
Set a flag on these records
Find product A AND empty (=) flags
The resulting found set is customers who bought product A but not product B.
I could not get the previous suggestions to work. I believe the reason is that the omit and constrain commands will only work with a single criteria.
The omit requests must be the last requests of a set of find requests. You can omit multiple "and" and "or" requests that way. Constrain found set also works with multiple criteria.
I’ve experimented with constrain and omit. I can successfully create various found sets when I’m constraining/omitting based on multiple fields (e.g., customerNo and productName).
I don't believe there is a "native" way do do this with find, provided that the product information is in the product table or order lines.
The ! operator is of no use in that situation.
I think this is one of the rare situations where you need to search on an unstored calc (or loop if there is a risk because of the database size/number of users.
So:
1 - find the ones that have bought A or B
2 - loop through records and omit the ones that have bought both. (or constraint on an unstored calc)
Yes, I found out ! didn’t work. Ultimately, I was able to do a search for A and save the customer_IDs into a variable. Then, I did the same for B. I could then compare $ListA to $ListB and find the set of records I wanted.