[eliz]

NEED HELP USING THE SAME CRITERIA FILTER (End Date) MULTIPLE TIMES (with Parens) WITHIN THE SAME SAVED SEARCH, TO DETERMINE ACTIVE CUSTOMERS WHO USED TO BUY A SET OF PRODUCTS BUT NO LONGER BUY THOSE PRODUCTS NOW.

~~~~~

Can someone kindly help me figure out why my Saved Search isn’t returning any data, where I know the data exists?

I’m trying to determine which customers were billed on Sales Orders for a set of products in the past who aren’t currently buying that same set of products.

I’m needing Results from multiple different data tables (customers, contact, contract items, billed sales orders), but my preferred search would be Customer Search Criteria.

Here is my current Customer Search Criteria set that is returning zero data,

as well as one of my current Transaction Search Criteria set that is returning zero data.

I know that we have customers who have bought the products in the past that I'm selecting, who are currently not buying these same products, so I know the results exist, I just need wisdom from other to help me where I've gone astray.

Any help is greatly appreciated! 🙂

WHAT I'M SEEKING:

CRITERIA to return Customers who:were billed Sales Orders that have Contract Items
where the SO isn’t in current Status of Cancelled or Pending Approval
for a certain set of products (simplified here by lumping all those products into a Class)
with the End of the billed term in the past (End Date through yesterday),
ANDthis same set of products (same as above—simplified by Class)
is not currently appearing on any Sales Orders/Contract Items
in the present & future (transaction End Date from today forward).

Attached Files
This is a cached copy. Click here to see the original post.

[khultquist]

Using joined records in searches can be tricky, and sometimes the results columns can act like criteria. That said, I can see one immediate problem: You have two criteria that are mutually exclusiveContract Item: Contract Item End Date – is on or before yesterday
Contract Item: Contract Item End Date – is on or after today

Here's my advice: break this task up into smaller, manageable pieces.

For example, create a custom checkbox on the Customer record that signifies that they have purchased this group of items. Populate that checkbox with mass updates, csv, or script. Then create a search for customers with that checkbox = T, where the current Sales Order has the remaining criteria.

[eliz]

Thanks for your thoughts! I need both instances of that same Criteria because I need customers who bought the products in the past, but now are not buying those same products. I thought that the multiple parens would find this unique set of customers by both my date sets of the same products.

I unfortunately won't be able to create a custom checkbox then populate it with this unique group of customers,

but I really appreciate your helpful and speedy suggestion, though!

[Author]

Posts