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 March 15, 20169 yr by sirsancho09

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 ... ?

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

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

ShowMissingCategories.fp7