calc for Overall Status

[Devin]

I'm looking at doing a Calc Field I think to return based on other info.

We have two tables.. Job and Part

The Job table we have a layout that has a portal for the Part Table. We are keying of "JOB ID MATCH FIELD"

We have a layout that on Job to show us the related Parts. Each part has a status... "In Progress" Or "Complete"

I'm wanting a way to return at the Job table the Overall Status of all the Parts.

So if all Parts are marked "In Progress" it returns "In Progress" If all the Parts are "Complete" it returns "Complete"

but if some are "Complete" and others are "In Progress" I would like it to return "Partially Done"

 

Any tips would be helpful..

I'm using Filemaker 14 at this time.

Thanks

 

[comment]

Try =

Let (
shortList = FilterValues ( "In Progress¶Complete" ; List ( Part::Status ) )
;
Case (
shortList = "In Progress¶Complete¶" ; "Partially Done" ;
shortList = "In Progress¶" ; "In Progress" ;
shortList = "Complete¶" ; "Complete"
)
)

 

Edited May 13, 2015 by comment

[Devin]

I'm getting an error about to many Parameters Error.. See screen shot. 

[comment]

That's not what I wrote.

[Devin]

Your right.. I changed the Part::Status to read Parts::Part Status.

Other then that I did a copy and past.

This time I did a copy and past to and editor then copy and pasted it to filemaker.. No errors.

But it's not returning anything for the calc.

[Devin]

Question? How does the formula tie to the Relationship?

[comment]

But it's not returning anything for the calc.

​Does this work for you?

Status.fp7

[Devin]

Your file worked... So I went back to my file and found the reason.

I had an extra space at the end of the data.... 

 

Thanks so much...