Remove Records from Found Set

Questions
1

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.

Any other suggestions?

2

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.

Hope this helps.

1 Like
3

Are you able to search on both product A and product B, then use a Constrain Find?

1 Like
4

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.

5

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.

2 Likes
6

I’ll try that methodology again, then — I must have made an error in my scripting. I appreciate your assistance.

1 Like
7

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).

Thanks for your help.

1 Like
8

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)

1 Like
9

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.

10

One more alternative:

In the Purchases table:

  • create two find requests: find A and find B
  • perform find
  • sort by customer ID
  • for each customer:
    • is the summary group of records greater than one?
      • Yes: do those records contain both A and B?
        • Yes: omit the group
1 Like