James Gill Posted April 6, 2011 Share Posted April 6, 2011 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. Link to comment Share on other sites More sharing options...
comment Posted April 6, 2011 Share Posted April 6, 2011 I cannot see the logic behind your examples. Link to comment Share on other sites More sharing options...
James Gill Posted April 6, 2011 Author Share Posted April 6, 2011 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. Link to comment Share on other sites More sharing options...
comment Posted April 6, 2011 Share Posted April 6, 2011 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4740 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 accountSign in
Already have an account? Sign in here.
Sign In Now