SeedCode Support Forum

In my a/r file I calculate the customers pay average for each year, what I would like to do is see the pay average in their customer file.(that is through a portal) I have no problem setting up the relationship for the portal, but I do not want to see every record for every year. I just want to see for example:Year 2005 30 days Year 2004 35 days etc.
I hope this is clear.
Thanks
Dean

Iam using fm 7

Hi. I think you want to use a calculated field in your Customer's file, instead of a portal. You'll still make use of the relationship you *would* use for a portal, you'll just apply an aggregate function over the relationship. Something like this"

Average (RelationshipToPayments::Amount) for the average amount, etc.

You may need a couple relationships (one for each year) or you can base the relationship on a global year field in Customers (in addition to the customer ID) so that by changing the global year, you can change which year's average you're looking at.

Hope that helps.

Hi John,

Thanks for the response. Let me try and give you more detail.

I have a customer with 37 records dating back to 2002, 2 records are 2002, 13 are 2003, 18 are 2004 & 4 are 2005.

I evaluate from a self joined rel the Get (FoundCount) the results are above. I am trying to get a key identifier for each year so I could use a portal in my customer file to see just the years as follows.
(ie 2002 50
2003 35
2004 28
2005 25)

I hope this sheds some light on what I am trying to do.

Thanks again,
Dean

I think I hear you Dean...

I evaluate from a self joined rel the Get (FoundCount) the results are above. I am trying to get a key identifier for each year so I could use a portal in my customer file to see just the years as follows.
(ie 2002 50
2003 35
2004 28
2005 25)

Well you can't get a portal like that unless you have a table of "Years" where you have one record per Year. You'd then need to have a global field (somewhere) containing the Customer's ID so that each Year record knows which customer's line items to add up. So you'd have a relationship from Customer to years- this could be an "X" join so all years show up in the portal. Then you'd have a relationship from years to customer's line items based on both the year and this global customer ID. Using that relationships you'd have a sum calc in the years table that would calculate the number of line items for the globally defined customer. Note that you'd have to set this global for each Customer as you scrolled form user to user.

For that reason, I don't really like the portal solution.

Rather, I like a solution where you have 4 (for example) calc fields in the Customer's file. These calcs would each contain 1 year you're interested in seeing totals for. Then you'd have 4 (for example) relationships from your Customer's file to the line items file based on both the Customer ID and ons of the Year fields. This would add up the line items for that customer and that year:

Sum ( LineItemsByCustomerYear::Amount )

The advantage of this is that you don't have to change anything as you scroll from user to user AND you can see more than 1 user's totals at once (in a list view, for instance).

Going further, you can make the calcs which contain the year to be unstored calcs based on an external settings table so that you can use this settings table to determined which 4 years (for example) you're looking at. If you make these unstored, however, you won't be able to search on these totals, so keeping the year calcs stored and re-defining them as necessary might be the best choice.

John,

Thanks for the feed back. I agree with you, I do not want to set up a seperate table for years. I think I will setup a dozen year fields for my customer file and relate everyting from there.

Thanks again for your input,

Dean

John,

Just to give an update on my progress. I created a calc:

[Case(Last(my rel::job = job:1:"")] which does just give me the last job for my customer.(it is an unsored calc however so my relationship woud not work with it. In my A/R file I have a button to record recievables so I added a replace to a field that I could index, the results are a portal with just the years and pay average I was looking for.

Thanks again for getting my head around this,
Dean