Struggling with simple calc

[James Gill]

I have 3 records that each contain a field I will call "Filter". All of the child records have the same parent record that contains a field I will call "Calc". I want to be able to return a result so I can filter the child records given the following:

Parent Record (Calc = 1)

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Expected Result = Record A filtered

Parent Record (Calc = 2)

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Expected Result = Record B filtered

Parent Record (Calc = 1, 2)

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Expected Result = All records filtered

Given the powerful Filter and List commands I thought this would be a fairly easy task but for some reason I am only able to get the following result:

Parent Record (Calc = 1)

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Actual Result = Record A filtered out, C remains

Parent Record (Calc = 2 )

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Actual Result = Record B filtered out , Records A and B remain

Parent Record (Calc = 1, 2)

Record A Filter = 1

Record B Filter = 1, 2

Record C Filter = 2

Actual Result = All records filtered

Everything except for the first actual result is what I would expect to see. I finally have a calc that is returning the result I'd expect to see, but it's a huge convoluted thing where I'm sure a more elegant solution is available. For my calc currently, I am doing a value count of the Parent Record calc and then filtering it against the child record. I then compare the two results and if they are not equal, display the record. Here's the calc in all of it's ugly glory

ValueCount ( FilterValues ( List ( ChildRecord::Filter ) ; ParentRecord::Calc ) )  ≠  ValueCount ( List ( ChildRecord::Filter ) )

The calc is being performed from the context of the parent record table.

Edit: After looking at the calc on the forum layout it's not too bad, but in Filemaker's calc window it makes things very messy. I'm just wondering if there's a better way to go about accomplishing this.

[comment]

I cannot see the logic behind your examples.

[James Gill]

I cannot see the logic behind your examples.

Uh...maybe this will help. I am trying to create a portal that will only display records that do not meet the filter criteria.

[comment]

Try this as the portal filtering calculation:

Let ( [

p = Substitute ( Parent::Calc ; ", " ; ¶ ) ;

c = Substitute ( Child::Field ; ", " ; ¶ ) 

] ;

c = FilterValues ( c ; p )

)

As you can see, it's easier to work with return-separated values - perhaps you should make that your format to begin with.