Group by particular values in multiple value field

[former_member905056]

I want a unique list of all IDs that contain value 1 from field X, regardless as to whether the ID also contains other values for the field, and a unique list of IDs that do not at all contain the value 1 from field X in the same report. Each ID can have multiple values from field X. I am not looking for a field per record or a summary that will tell me which group each ID fits into, but rather want to know how to get a group or way to get a list of IDs for each of the two conditions into one report.

[abhilash_kumar]

Ah, I missed some details. Here's what you need to do:

1. Modify the formula above to:

WhileReadingRecords;
stringvar s; 
if {FieldX} = 1 then
(
    if instr(s,totext({ID},'#')) = 0 then //you don't need the string conversion using totext() if the ID field is string
    (
        s := s + totext({ID},'#');
    );
);
if instr(s,totext({ID},'#')) > 0 then 'Contains 1' else 'Contains 2'

2. Insert a Group on this formula field.

3. Insert a second group on the ID field.

4. Suppress the Details Section.

5. Suppress the Group Footer #2 section.

-Abhilash

[former_member905056]

Hi-

This worked until I refreshed and changed my parameters. Thoughts?

[abhilash_kumar]

Hi Alisha,

Modify the formula I posted above to:

If {fieldx} = 1 Then 'IDs that contain 1' Else 'IDs that do not contain 1'

-Abhilash

[former_member905056]

This results in the same thing. I do not want IDs to repeat as in the example provided. If an ID at all contains 1 I want them in group 1 and only group 1, so if an ID has 1 and 3 I only want that ID to show in Group 1. If an ID does not at all contain 1 I want them to show up once in group 2.

[abhilash_kumar]

Hi Alesha,

Could you please post an example with data and the expected groupings?

-Abhilash

[former_member905056]

Hello,

Example of raw data:

ID FieldX

19 1

19 5

19 7

18 1

18 3

17 3

What I want are groupings or something that will give me lists in the same report that are:

IDs that contain 1:

19

18

IDs that do not contain 1:

17

[abhilash_kumar]

Hi Alesha,

1. Create a formula with this code:

If {fieldx} LIKE '*value 1*' Then 'Group1' Else 'Group2' //you may name the groups anything

2. Insert a group on this formula field.

3. Place the ID field on the Details Section.

-Abhilash

[former_member905056]

Hi,

This does not work as if an ID has a 1 and another value, the ID will show in both group 1 and group 2. What I want is a list of IDs where if any of the values for the field for that ID are 1 make a group and a group where none of the values in that field for that ID are 1.