Portal displays no records

[Cap'n Splatter]

Hi there

I have a kind of "home page" on my database containing a single record, and the layout has a collection of portals showing me the status of jobs, invoices etc. at a glance.

The problem is that one of them doesn't work and I can't figure out why. It's the portal showing me all unpaid invoices.

Here's how it works (or in this case, doesn't)

The [color:red]INVOICES table has a calculation field called [color:red]STATUS which checks if anything is entered into the [color:red]DATE PAID field, then displays the colour coded invoice status accordingly, like this:

[color:brown]Case ( not IsEmpty ( Date paid ) ; TextColor ( "Paid" ; RGB (0;160;0)) ;

(Home::Current date) > Due date ; TextColor ( "Overdue" ; RGB (200;0;0)) ;

TextColor ( "Due" ; RGB (0;0;200))

)

The [color:red]HOME table has a global field called [color:red]PAID INVOICES containing the word "Paid"

The relationship graph has a table occurrence called [color:red]INVOICE STATUS set up as [color:red]HOME::PAID INVOICES (does not equal) INVOICE STATUS::STATUS

The portal shows nothing. I'm running Filemaker 8 on a Mac

Does this make sense, and can anyone help?

Many thanks

Edited April 26, 2006 by Guest

[IdealData]

Are you sure that the fields in your portal belong to the relationship?

[Cap'n Splatter]

Yes, the portal is set to display records from the Invoice Status table.

[comment]

Your relationship cannot work, because the match field in Invoices is not indexable. It is forced to be unstored because it references a related field (which also happens to be an unstored calculation field, so it wouldn't work even if CurrentDate were to be moved into Invoices).

Try basing your relationship on another field, e.g. a calculation =

Case ( not IsEmpty ( Date paid ) ; "Paid" )

[Cap'n Splatter]

Genius!

I created that calculation field and swapped it with Status in the relationship graph, and now everything works a treat.

Thanks a million!