Presumably you meant to post this calculation:
If(projects::project status <> "Cancelled" and projects::project status <> "Complete"; Sum(Projects::PO Value))
And defined this calculation field in a table that is related to Projects in a one to many relationship:
But I could be guessing incorrectly.
When a calculation refers to a field from a related table (Projects::ProjectStatus), it only access the value of the "first related record". It does not access the data in any other related table. Thus your sum function returns the total of PO Value from all related records (regardless of status) or no sum at all depending on the value of project status in the first related record.
But there are multiple other methods for selectively computing the desired sum.
The simplest is to define cOpenProjectPOValue in the project status table as:
If ( project status ≠ "cancelled" and project status ≠ "Complete" ; PO Value )
Then, in your other table, you can use Sum (projects::cOpenProjectPOValue ) and sum will return the total for just the PO of the open projects that are related to the current record in "SomeTable".
Let me see if I can restate this and understand. We're dealing with two tables. "Accounts" and "Projects". Projects is link to accounts in a one-to-many relationship. The value I'd like displayed is on a layout based on the "accounts" table (project(s) are being displayed in a portal). You would like me to configure two calculated values. The first calculated values should be in the "projects" table and should look something like this:
If (Status <> "Complete" Or Status <> "Cancelled"; PO Value). Lets call this c_FilteredPOValue
The second calculated value should be configured in the "accounts" table and should look something like this:
c_OutstandingPOValue = Sum(c_FilteredPOValue)
Do I have that right or did I misunderstand you
It would be Sum ( Projects::c_FilteredPOValue ) not Sum ( c_FilteredPOValue )
And that highlights a key assumption on my part that may be incorrect. In which table have you defined POValue? is it defined in Accounts or Projects?
I'm now guessing that it is defined in Accounts and will change what has to be done to get the result that you want.
Yes...my bad on the typo. Your original assumption was correct. PO Value is defined in the "Projects" table not "Accounts". I think I have it set-up as your describe but it still does not calculate correctly. It still appears to be taking the entire value despite the project status:
My mistake. Replace "or" with "and" as you want values where status is not completed AND not cancelled.
Ahhh...I should have seen that....my fault I think. That made the difference. Two pair of eyes always better than one I guess. Thanks very much. I'm not sure if your one person or many but your timely answers to my novice questions over the last several weeks have helped me greatly make the transition from Access (still have way too much to learn)
I'm just me, no "ghost helpers" nor am I employed by FileMaker Inc.