Filtering portal

[ping]

Hi,

I create invoicing solution with separate tables for incoming invoice and outgoing invoice. In both of them I have calculation fields which can not be indexed because they calculate payment terms (30, 40, or 90 days from delivery).

Calculation in field "c_valute" looks like:

Get (Current date) - Incoming : When payable

Result is a number. "Do not store results - reculculate when needed" option is selected. After that I created field which tells me if my payment is due or not.

Calculation for field c_due":

If ( Incoming : c_valute ≥ 0 ; "must be paid" : "not payable yet" )

Results is a test. "Do not store results - reculculate when needed" option is selected. In Incoming invoices I want to create several portals where I would separate :

all invoices

must be paid invoices

not payable yet invoices

paid invoices

not paid invoices.

I can view records in portals only if calculation field can be indexed. However, I want to create relationship with fields which I can not indexed. I created portal with all invoices an now I look to find a way to filter those records. I found certain solutions on the web but none of them deal with unstored records.

Edited May 2, 2010 by Guest

[comment]

It can be done by moving the unstored calculation to the other side of relationship. For example, if you have an unstored calculation field cTodayMinus30 in the Customers table =

Get (CurrentDate) - 30

and a relationship:

Customers::CustomerID = Invoices::CustomerID

AND

Customers::cTodayMinus30 ≥ Invoices::DueDate

the relationship will include only invoices that are 30 days or more past their due date.

[bruceR]

Switch to FileMaker 11. FM11 portal filtering is not dependent on indexing. You want the base relation be based on indexed values; and FM11 portal filtering will have performance issues with large related record sets. But it's quite useful when the limitations are understood.

[ping]

@ comment

I tried all option possible and I am not able to create relationship which will filter portal and separate payment due and overdue. What I get, is list of all records for selected ID. Any clues?

@ BruceR

Unfortunately, switching to version 11 is not an option now. (My boss has different views from me, when it comes to which software is really needed.)

[comment]

How about something like:

Overdue.zip

[ping]

Thank you. That's it.