Multiple Criteria Relationships for Portals

[brainonastick]

Im trying to set up a portal with 2 relationship criteria: a text tag "Amounts Outstanding" and a Date tag (week of year in this case) to list all outstanding amounts receivable over a one week period. I have already created a similar portal for Amounts Payable based on month and year which works fine, but this second one won't work for some reason. It works with just the "Amounts Outstanding" tag but not when I introduce the second "week of year" criteria. I want the portal to show only one week's worth of payments at a time so the list won't be too long, hence the second criteria. Any advice very gratefully received.

[Ender]

Hey Brain,

Your parent table should probably have a global gWeekOfYear and your related table should have an indexed calculated weekOfYear = weekOfYear(Date). They should both be Number types.

If you have that already, then maybe you can describe the relationship (from the Edit Relationship dialog) in more detail.

[MoonShadow]

Yes, it would help if you indicated what you mean by 'it won't work.' Does it produce too many records? Because if you have several years' worth of data, there may be several years accumulating (and displaying) in your portal because several years can share the same WeekOfYear. You might want to include a year in this portal relationship also; which it sounds like you already have available for your other relationship. With that tidbit of comment, I'll let Mike take it.

[brainonastick]

Hi and thanks for your replies.

Here's the full story:

The purpose of the portal is to show all payments that have not had a receipt generated. Each transaction has a payment status field called Accounts_Receivable which is a calculation with a text message result:

Case(((Amount_Due > 0 and Balance_Owing > 0) or (Members_Amount_Due > 0 and Member_Balance_Owing > 0)); "Amount Outstanding"; ((Amount_Refundable > 0 and Balance_Owing < 0) or (Amount_Refundable_Members > 0 and Member_Balance_Owing < 0)); "Refund Due"; ((Amount_Refundable > 0 and Balance_Owing = 0) or (Amount_Refundable_Members > 0 and Member_Balance_Owing = 0)); "Refund Issued"; "Paid")

The portal works off two fields - a field called Payment_Status_for_Portal which has a same drop-down list with the message options in the calculation shown above, and a Week_of_Year calculation field based that extracts the week from a Course_Date field. This is to restrict the portal list to one week's worth of entries. By changing the options in the Payment_Status_for_Portal field, the different Payment_Status items can be viewed in the portal. It worked fine with a similar portal that used Payment_Status_for_Portal as well as Month and Year but the Week_of_Year wasnt. I solved the problem by creating a field called Week_of_Year_Pipe which was just a calculation field = Week_of_Year and it started working when I used this second field in the portal relationship. I assumed that the relationship didnt like using a calculation result as a criteria. Im open to simpler/better ways to do this however.

My current issue which is related to the above is a two criteria relationship which shows all unreceipted or all unreceipted payments. This works off a calculation message field in a related membership DB(called Receipt_Status)_Message with results like "Receipt Not Issued" and "Receipt Issued" and a field with the same options as drop down items. The second criteria is an ID number (indviduals in one portal, companies in another). I can get the portal to work for several other types of payments (training, subscriptions) but when i create it for memberships payments it shows nothing at all in the portal. The calculationr esult issue mentone dabove doesnt appy as the ID numbers are just numbers and the drop-down options are just text.

Sorry about the long memo and hope the problem is clear.

[brainonastick]

Sorry it should read "receipted or unreceipted" in last para of previous entry.