Jump to content

Calculating across portal (related) records


ericsm
 Share

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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)..

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6515 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
 Share

×
×
  • Create New...

Important Information

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