many to many with restricted criteria

[burdenslifted]

Somehow I can't quite wrap my head around this one. I have People affiliated with Organizations via a join table for a many to many relationship. I have a field "status" that is calculated by criteria as follows:

Case (

not IsEmpty (date_deceased); "Deceased";

not IsEmpty (transferred_out_date); "Transferred";

not IsEmpty (date_retired); "Retired";

not IsEmpty (PEOPLE_j_organizations:_kp_organization_join) and IsEmpty (PEOPLE_j_organizations::date_end); "Active";

"Inactive"

)

I want status to read "active" if ANY of the joined affiliations does not have an end date. How do I do this, or is there a better way to make this happen?

[Søren Dyhr]

The trimmings needs to go to the join table records but the info reside in one of the other tables?

A way to do it is auto ID each of the join table records, and then make the calc in the jointable instead. The result of the calc will in case the condtion is met the records ID is copied via a calc field, which now is made into a primary key from the join table ... but connected to a selfjoin of the jointable, this will make the filtering, but where you in the old portal showed all, would a layout change pointing at the selfjoined a step further away...

Perhaps it makes more sense to see a template?

--sd

dodah.zip

[burdenslifted]

Not sure what you mean by trimmings, but yes, deaceased_date etc are in People table (as is status), not in join table. But your comment got me thinking about having something in the join table, so I made a calc field (count_active_affiliations) with formula "Case ( IsEmpty (end_date); 1; 0)" to return 1 for every open affiliation and 0 for closed. Then I changed the status formula to:

Case (

not IsEmpty (date_deceased); "Deceased";

not IsEmpty (transferred_out_date); "Transferred";

not IsEmpty (date_retired); "Retired";

Sum (PEOPLE_j_organizations:_c_affiliations_active) > 0; "Active";

"Inactive"

)

Seems to work. Side benefit... can count open (& closed) affiliations. So thanks for the inspiration!

Edited February 5, 2010 by Guest

[comment]

How about:

Count ( Affiliations::PersonID ) > Count ( Affiliations::EndDate )