Select and exclude from the same field?

[#1]

I have two questions, actually. Our CRM system pumps out a lot of data for each account, according to the activity taken by various people (sales, customer service, billing, etc.). The Result field lets me identify successful sales ('S') and unsuccessful sales ('S U'). Another field lets me identify the various activity types (phone, email, meeting, etc.) along with the data and who performed the action.

First question: Since we can have multiple sales (and failures) to the same customer, I want to identify customers who we have never had a successful sale with, but where we have had an unsuccessful sale recorded (many customers are in 'limbo', with neither S or S U records). If I select "S U" in my list box, I get customers whose complete history includes both "S" and "S U" records; if I select (exclude 'S'), I get all the limbo customers along with 'S U' customers. Is there a way to include and exclude values from the same field, using a list box? If not, is there another way to do it?

Second question: The "S" and "S U" records are associated with a particular date. I want to see all the other activities up to that date. Again, when I select "S U", I only get the record associated with that entry displayed (i.e. none of the other activities show up in the "activity type" field - they are all blank).

So, ultimately, what I want is the full set of activity types (dates, activity, user) for all companies where at least one "record type" entry is "S U", and no "record type" entry is "S". e.g.

Company	Activity Type	Date	User ID	Result
AAA	Call	9/22	JS
AAA	Email	9/23	MB
AAA		9/24	JS	S U
BBB	Call	9/15	TC
		9/15	TC	S U

Is this something I do in set analysis (which I intellectually understand but am struggling with syntax - the devil's in the details!)?

[giakoum]

You can use a button and action to include and exclude values for the same field.

See attached. Predifined values can easily be substituted by variables, so even the user can determine what to include and what to exclude.

Hope this helps!

[kuba_michalik]

As to your first question, this is easiest to achieve with advanced search. Make a selection on Customer listbox (CustomerID, whatever identifies customers) by typing

=Only(Result)='S U'

(that's assuming Result is either 'S', 'S U' or null)

This should also solve your other requirement - since the selection is made on customers, not Result, you will see all dates for the found customers.

[giakoum]

Hi Kuba_Michalik

this is excellent, I did not kno it was possible. Can you also use a wirldcart some way? I tried =Only(Result)='S U*' but did not work.

[kuba_michalik]

Not exactly like that, but this:

=WildMatch(Only(Result),'S U*')

should probably work.

[#6]

Hi,

thanks for your reply, it was helpful on many levels. However, my second problem remains: when I select the "S U" in "Result" field, my table only shows the date that code was entered, e.g.

Company	Date	Activity Code	User	Result
AAA	9/23		JS	S U
BBB	9/25		MB	S U
DDD	9/24		JS	S U
GGG	9/27		MB	S U

where what I really want is to see all the dates and activities that led up to the sale decision, not just the date the decision was made.

I'm wondering if this is best done in the script? Not that I'm sure how to do that, either...

[giakoum]

mabe you can share the application to save some time.

[kuba_michalik]

Have you tried to follow my suggestion (do not select anything in "Result" directly - select Company based on whether only Result filled is 'S U'; because you are not making the selection directly on Result, all records for these customers will show)? This won't work if the empty Result values are empty strings or spaces and not NULLs (hard to say just looking at your tables 😉 )

I'm attaching a quick example of what I have in mind.