select ALL codes

[Tonyvee73]

I have a select line as below:
if {table.field} = ["12548","12457","12248","95684","65236","78555","78854","12544"] then 'set1'
else {table.field}

Trouble is I want to only mark as 'set1' if ALL of them are in a record there as at the moment its putting in 'set1' if ANY of them are there.
Is this possible?

 

[tomcat24]

https://www.infosol.com/crystal-reports-selecting-all-values-in-a-dynamic-prompt-list/

Answer: In order to do this, you will need to create a SQL command, that grabs all the values you want for your prompt and joins it to another query, that pulls the wildcard (*) value and the word ALL that a user can select.

Steps:

We will be using the Northwind sample SQL database that comes with SQL Server 2000 and the “Customers” table.
Create a new, blank report connecting to the Northwind SQL database and only add the “Customers” table.

 

[lbass]

There will only be one instance of a field per row. If you are trying to group the data into sets, but the sets only count if they contain all of certain specified records, then you could do something like this. First establish whether a record is a member of the desired set. You could use your existing formula for this:

//{@memberset1}:
if {table.field} = ["12548","12457","12248","95684","65236","78555","78854","12544"] then 'memberset1'
else 'nonmemberset1'

Then you can insert a group on the above formula {@memberset1}, and then assess whether all members are present:

if distinctcount({@memberset1})=8 and //here a complete set contains 8 distinct values based on your formula; not sure how you know what comprises a complete set
{@memberset1}='memberset1' then
'Set 1'

Place this formula in the Group Header.

-LB