# List Missing Values in a Group

This topic is 2297 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I am wondering how one would go about listing missing values in a group. I have a group of records that are assigned a value in a field called Incidental, for ease of use, 1-20. I may have 12 records with the value of 1, and 3 with a value of  2, none with a value of 3 or 4, 2 with a value of 5 and so on. Some records won't have a value at all in that field.  I need to know if there is a way to generate a list of the numbers between 1-20 that do not have a record connected to them. Is there a way to poll the records and get a list of that? I created this script:

Perform Find // Shows only records that have a value in the Incidental Field

Sort Records // Sorts the Incidental Field in Order

Set Field [gincidental; ""] // Clears Data and is used to create list of values

Loop

If [\$PI = Incidental]

Set varialble [PI; Incidental]

Go to Record/Request/Page [Next; Exit After Last]

Else If

If [(\$PI+1)= Incidental=0]

Set varialble [PI; Incidental]

Go to Record/Request/Page [Next; Exit After Last]

Else If

If [Incidental - \$PI >= 1]

Set Field [gincidental; If(gincidental=""; (\$PI)+(Incidental-\$P1)-1; gincidental & ", " & (\$PI)+(Incidental-\$PI)-1)

End If

Set varialble [PI; Incidental]

Go to Record/Request/Page [Next; Exit After Last]

End Loop

I know the calculation is bad because I am not getting all the missing values, because it doesn't account for all missing values, so when it goes from 2 to 5 in the above example it would only show 4. I feel like there is a more elegant solution that would show me 3,4, and and other missing values accounting for duplicates, and if possible, empty records as well.

Any help would be grandly appreciated,

Shaun

Edited by sirsancho09
##### Share on other sites

In order to know which values are missing, you need to have a list of all possible values. Where would you keep such list? Would it be in a custom value list, or in a table, or ... ?

##### Share on other sites

I don't have one, I could place the values on a Value List.

##### Share on other sites

Well, here's one way you could look at it.

ShowMissingCategories.fp7

• 1
##### Share on other sites

This topic is 2297 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account