Using Calculated Field in a Relationship

[HunterBoss]

Well, after much work and combination of different types of functions, I was able to get the results in a field I needed to do the type of relationship I needed between 2 tables.

Then, much to my surprise I finally realize my relationship won't work -- I'm using a calculated field on one side.

Then I find Soren's post about how to do a relationship with a calculated field.

http://www.fmforums.com/forum/showtopic.php?tid/187182/post/252366/#252366

So, I'm confused.

Is this something new with 9.0?

Please and thank you

Christopher

[David Jondreau]

You're having a problem with a relationship. Without more details, it's hard to say what's going on.

There are a couple things you can check until you give more information, ideally a small sample file showing the problem and a description of what you're trying to achieve. If you can't post a file, describe the problem...are you attempting to use the relationship for a calculation or to show related records in a portal or what? Are the wrong records being viewed or none at all?

Does the correct calculated result show up for a record when viewed in Browse mode?

Is the calculated field stored? If unstored, it can only be used on the "parent" side of the relationship

[HunterBoss]

D J

Thanks

Q) "... are you attempting to use the relationship for a calculation or to show related records in a portal or what?"

A) I'm attempting to use the relationship to show related records in a portal. I'm trying to use values from a self join. I'm using the List function and the PatternCount function so that it displays only the necessary value in the calculated field. That part's working fine.

Q) "Are the wrong records being viewed or none at all?"

A) No records are showing

Q) "Does the correct calculated result show up for a record when viewed in Browse mode?"

A) Yes

Q) "Is the calculated field stored? If unstored, it can only be used on the "parent" side of the relationship..."

A) The calculated field is not stored. I thought you could not store a record. How do you do that? I, unfortunately, have the unstored calculation on the child side. I just realized that in the post SD was referencing the calculated field was the parent. Also, when I try to set the storage options for this field, I get an error message telling me I can't.

Before I saw your post and the fact that you can store a calculation, I did think of one way to get around this. Write a script to copy the calculated value from the calculated field and paste it into a second field just for this purpose. Getting the script to go through all the records is going to be the trick and probable subject of a separate post in another forum.

thanks

Now, regarding the attached file. The TOs in the RG that are in question are the 2 that I've changed to green. They're supposed to show the related records in the portal on the layout z_Resources Filter.

Christopher

Test_Tables.fp7.zip

Edited March 4, 2008 by Guest
forgot something

[David Jondreau]

I'm not sure what you're trying to accomplish. I think z_Resources represents "a plan's" (not sure what that is). And you're trying to view all the customers that have had a plan within the same timeframe as that plan's. If that's not it, can you try to tell me in plain english?

That aside, there are a few things you need to know.

-An field with an auto-enter calc won't get unpdated (as in Customer::PlanYearEndIDStored) when an unstored calc referenced within is changed.

-Unrelated fields won't evaluate true in calcs (as in Customer::PlanYearEndID)

-You can't store calculations that reference related data (Customer::PlanYearEndID)

-Do a search for Anchor-Buoy

[HunterBoss]

DJ

Thanks

The PlanYearEndIDStored was an attempt at something last night. I realize now I should have deleted that field before I attached it to my post.

I have not yet searched for Anchor-Buoy yet but will in a bit.

What I'm trying to do (I'm going to try to do this in English and not use any FM language):

1. get a list/group of customers who meet one criteria and do not have a second criteria.

2. The criteria they are to equal is the field called customer type.

3. The criteria that they are to not equal is the plan year end id field.

Now, back to FM speak.

The customer type in the z-Resource layout is a value list. It lists the types of retirement plans they could have. There will be a similar field on the customer record.

The plan year end id is from a value list that is linked to a year end table. There is a join table in between customer and year end. Why? Customers will have multiple year ends the longer they have a plan. But, not all of them start the same year and not all of them end the same year.

on the z_Resources layout, I want to be able to select a year end from that year end value list.

somewhere, probably in a portal, I need to have the clients that match the customer type but do NOT have the year end selected.

I've done my best to make that clear and hope I have.

Christopher

[David Jondreau]

I think what you really want to do is link Resources not to Customers, but to the CustomerYear join table.

[HunterBoss]

DJ

Funny you should say that. That's what I thought as well. But, that only relates me to customers that are already in that join table. I need all customers, not just those who already have a year end ID associated with them.

In theory, will the script idea doing the copy and past work?

Christopher