Relationship not filtering portal as expected

[madman411]

Hi guys!

I'm about to embark on a project where I'm solely responsible for tracking a lot of different items distributed amongst 20 people. Barcodes were my solution and and the app I've put together works a treat. However, I'm trying to use checkboxes to filter out related records in a portal. When I try to implement this the portal goes blank, which isn't the result I expected.

The two tables I'm having an issue with are "Projects" and "Assignments". The "Assignments" portal is on the "Projects" layout. The relationship is:

Assignments::ProjectID = Projects::ProjectsID

This accurately displays all the related records in the portal. However, when I add a global field to the Projects table ("filter_assignment_status") and link the status value list to the "Status" field in the Assignments table, connect the with the added relationship the table goes blank:

Assignments::ProjectID = Projects::ProjectsID
AND Assignments::Status = Projects::filter_assignment_status

The value list matches any of the values that would appear in the Assignments::Status field. It's worth noting that the Status field is a calculation that uses the Case() function. I'm wondering if the unstored calculation field is the issue?

Case ( 

WrappedFlag = 1 ; "Wrapped" ;
IsEmpty ( ItemCount ) or ItemCount = 0 ; "No assets!" ; 
not IsEmpty ( ItemCount) ; "Active" ;

"OK"

)

 

[mr_vodka]

Unstored calc can not be used on the child side of the relationship as a key field.

[madman411]

@mr_vodka I figured this was it! Thank you.

Can you suggest a work around for this?

[mr_vodka]

well you can keep your original relationship and then use the filter portal option.

[madman411]

I've never attempted to filter a portal based on checkbox selections. Is there a particular function that would achieve this?

Never mind PatternCount() did the trick

[mr_vodka]

Case (  IsEmpty ( Projects::filter_assignment_status ); 1 ;  IsEmpty ( FilterValues ( Projects::filter_assignment_status; Assignments::Status ) ) )

 

You will need to refresh the portal.  You can use a simple script trigger to refresh the portal when the global field is modified. 

*  This also assumes that you want to show all the records if nothing is checked in the global.  If you do not want that, then just rid of the first part of the Case statement.

[madman411]

Thank you!

[comment]

Why can't the calculation be stored?