Jump to content

Indexed References to Related Field - FAIL


Dr. Evil

This topic is 4511 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4511 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.