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, 201015 yr by Guest

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.

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.

@ 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.)

How about something like:

Overdue.zip

Thank you. That's it.