Filter list calculation based on 2nd repetition value not being empty

[4Justme2]

I have a family center database wherein people, more specifically, children often reside in multiple households. A field labled HHDType can have one of two values, "Primary" and "Additional"... which represents two types of households they could live in.

Each household has a repetitive field called activityDateSpan (allowing for up to two repetition) indicating a startDate and endDate. These dates represent the following: rep 1 = the date an individual began association within a particular household and rep2 = when the association is terminated (repetition#2).

I have a calculation called cHousehold which identifies all the possible households this person resides in, in list format in the following calculation:

cHousehold (calculation, text)

Substitute ( List (_HOUSEHOLDS::householdName ) ; ¶ ; ", " )

Here's my dilema...

If the activityDateSpan contains a value in the second repetition, I do not want these households to be include in my cHousehold calculation (another words, I don't want to include them in the list).

Any ideas on how I might accomplish this?

[Pavlk]

You can make a calculation field (say "rep2_Closed"), which would set itself to "1" (for example) in case if Repetition 2 is not empty. Then you duplicate your table in Table Graph and make a new relation between these tables: rep2_Closed=notEmpty). notEmpty field is always set to 0.

That relation would let you make a new Value List with values starting from Table1. Take a look at it in my attached file. Is that what you meant? Try to change some endDate to see what will happen.

Waiting for comments.

Household.fp7.zip

[comment]

Each household has a repetitive field called activityDateSpan (allowing for up to two repetition) indicating a startDate and endDate. These dates represent the following: rep 1 = the date an individual began association within a particular household and rep2 = when the association is terminated (repetition#2).

I am not sure that's a good approach in general. Esp. if you ever want to search for StartDate > $someDate or similar.

[Pavlk]

I am not sure that's a good approach in general. Esp. if you ever want to search for StartDate > $someDate or similar.

Actually I don't see any difficulties searching exact date.

[comment]

Actually I don't see any difficulties searching exact date.

The difficulty is that you cannot isolate records where start date = $someDate. A search like that in a repeating field will also find records where end date = $someDate.