using calculation fields in portal relationships

[brainonastick]

Hi there,

I am trying to create a portal that shows different members with different kinds of membership status information. I have created one field called Status_for_Portal and another called Year_for_Portal - the idea is that a user selects a Status and a Year from a drop down list in each of these fields. The relationship is betwen these 2 fields and a Membership Status field in each related record with exactly the same text result as the drop down item in the status_for_Portal field, and a Year field that matches the Year_for_Portal field.

The Membership Status field in each related record is a calculation field based on whether a member has paid their fees etc.

When I create the portal like this, no records show.

However If I build the same relationship using a text field called Membership Status (ie it is not a calculation field), the portal works fine.

Can calculation fields be used in portal relationships? I havent read anywhere to the contrary.

How can I channel my calculation data into the portal relationship without using a script - I just want the data to show up without the user having to doing anything except select the Status Type and the Year.

Regards,

Steven

[Ender]

Hi Steven,

Sure you can use calcs as match fields in a relationship, but they must be indexed (or indexable.) This means the match field cannot be based on a global field or a related field.

Your relationship should work if the fields on the parent side of the relationship are globals of the same type as the match fields, and the match fields are calcs that are based on regular text, number, date, or time fields.

[Ender]

It may be that your Membership Status calc is looking to see if the Balance Due is zero, where the Balance Due is calculated from a related Payment table. For this type of situation, you will likely need to have a script update a non-calculated (number) Balance Due field upon processing each payment. This is a common method for updating inventory counts too. You just need to script any payment and billing additions and deletions.

[brainonastick]

thanks for your replies. the presence of a related field in the calculation is in fact the problem so i have built a script that pastes the status of each record into a text field that is now used in the relationship instead of the calculation field and the portal works fine. it just means users have to refresh the portal with a button evert time they look at it. this issue strikes me as being a significant design fault with the software.

[Ender]

This is how it works. I suspect other database systems have the same limitation.

[brainonastick]

Hi Ender,

Hi Steven,

Sure you can use calcs as match fields in a relationship, but they must be indexed (or indexable.) This means the match field cannot be based on a global field or a related field.

I didnt notice this when I read your reply before but I have a number of portals that use global fields in relationships - but they use them at the parent end of the relationship - ie I have a portal that is based on a relationship between Unregistered_Receipts_Global and a related match field: Unregistered_Receipts. This portal finds all instances of Unregistered_Receipts in the other DB and displays them in the portal. It seems to work fine - but is there a problem with this that I need to know about?

Chees,

brainonastick (steven)

[Ender]

You can use globals and unstored calcs on the parent side of relationships.