Calculating across portal (related) records

[ericsm]

This project is for a school. I have a group of records with data:

State_________Days_______Billing Days

PA____________29

PA____________60

PA____________193

What I need to occur is have Billing Days be a calculation that says if any of the Day fields are over 180, put the following calc: If(Days >180, 180, Days).

So the data would look like:

State_________Days________Billing Days

PA____________29____________29

PA____________60____________60

PA____________193__________180

But this should only happen if one or more of the State records has over 180 in one of the Day fields. If the records look like the following:

State_________Days_______Billing Days

PA____________29

PA____________60

PA____________75

Billing days should be blank.

I know there must be a way to do this but I don't know the math!

Thanks for any help you can provide.

Eric

[email protected]

Version: v6.x

Platform: Windows XP

[Ugo DI LUCA]

Hi,

But this should only happen if one or more of the State records has over 180 in one of the Day fields.

You could have an unstored calc of type

Case(Max(SjConstant:BillingDays>180,Case(BillingDays>180, 180,BillingDays),"") with SjConstant being a constant relationship matching all values in your related file.

If the result should just be restricted to a subset of related records, then you might have this same result with a global or calculated field "equal" to your Parent key and matching the Foreign Key in your related file.

Note that calculations accross related records are slow.

[ericsm]

This is only one file. Tried your calc but FMP is returning the error - "to few separators in the calc"

[Ugo DI LUCA]

Ok,

Only one file.

So create a calculated field called cConstant, and enter "1" in the Calculation box.

Now, go into Define Relationships and create a Relationship "SjConstant" with cConstant for both sides of your relationship.

Now, create this other calculation

cShowBillingDays = Case(Max(SjConstant::BillingDays)>180,Case(BillingDays>180, 180,BillingDays),"")

I'm unsure what is your exact experience of FM, so please pardon me in advance if this was obvious for you, but...SjConstant::BillingDays would come by selecting (from the pop-up list above the fieldNames in the calculation box) the SjConstant relationship, and then picking the BillingDays from the list.

Tell if it still doesn't work.

There was a typo anyway in my first answer as there was a missing parenthesis in Max (AnyField)..

[ericsm]

That calc works great in my test file! I used the State field as the constant and it works prefectly. I'm pretty advanced with FM but these calcs sometimes get my head spinning.

I will work it into the live files and let you know how it goes.

Thanks again.