How can I filter a portal by a calculation field that references a related child table?

I want to filter portal by "Working" and "Complete" but relation fails since the sorting field can not be indexed.

For some insight, tables set up is

ParentProject > ChildAssignment

I have a ProjectTable self relationship built, and want to sort this portal by "Working" and "Complete".

I have a global field that user can choose filter portal by "working" or "complete"

The global field is related to a calculation field that looks at the AssignmentChildren.

So working with three key fields here...

1) ParentProjectTable > GlobalProjectFilterKeyField; User uses to select what to sort portal by.

2) ParentProjectTable > CalculationProjectStatusField; The calculation that looks at the AssignmentChildren's status, looks like this...

Case(

not Sum ( PRJ~asgn`all_sort::clc_status_flag); "Complete"; "Working"

)

3) ChildAssignmentTable > A boolean calculation that refers to a text status field, looks like this...

Case(

txt_status_work = "Complete" or

txt_status_work = "Canceled"; 0;1

)

This is a mess trying to explain, let me know if you are interested in helping me solve this delima and or if you have questions on clearing this up.

THANKS A HEAP for your time and help on this!!!

In version 9, you'll need to use the so called "Ugo method": in a nutshell, use an unstored calculation field to return the ProjectID if the project is completed. Then define a self-join matching the calculation field to ProjectID.

In version 11, you can take advantage of the portal filtering feature.

Hey! Thanks, just now getting back to this, I will try the UgoMethod. Im running FM10 at the company I work for, but I have FM11 for personal, I will try both methods. I can't wait for FM12 to come out.

I appreciate the feedback Comment!

Comment, I have tried to build what you describe in a demo file and I'm still failing. Using the FM9 technique.

I have attached the demo file, maybe you can point out what I'm doing wrong? Thank you for your time! :-)

PortalFilterDEMOv01.fp7.zip

More like this, I think.

Note that assignment status should be recorded more explicitly, e.g. by filling a DateCompleted field.

UgoFilterByStatus.zip

Wow! Thanks for working demo file.

I have some studying to do on this.

Quick noob question, how did you get the field cClosed to display "Active" or "Closed" when it's value is 1 or 0?

Also... I do not have a close_date_field because then it would allow conflict of data, e.g. assignment status is "Working" but there is a Close Date.

Maybe I should automatically set status to "Complete" when a close date is entered. I'm just trying to keep number of user fields down to a minimal, also there, I do have a close date no the Project level. Thoughts?

Thanks again for your time.

how did you get the field cClosed to display "Active" or "Closed"

Format > Number… > Format as Boolean

I do not have a close_date_field because then it would allow conflict of data

I meant that the status (if necessary) would be calculated from the recorded facts.