Filtering a portal

[gaby78]

Hi everybody:

I am having trouble filtering a portal.

I have a standard Invoice table linked to an InvoiceItems table. The Invoice table has a calculated field InvoiceTotal with the following expression: InvoiceTotal = sum(InvoiceItems::Linetotal)

On the graph I have Invoice and Invoice2 with the following relationship:

CustomerID = Customer ID and

gAmount < InvoiceTotal (gAmount is a global field in Invoice table)

The portal (Invoice2) placed in the Invoice layout does not filter by amount. It filters by CustomerID, but not by amount. The portal shows all invoices for a specific customer regardless of the amount entered in the global field. Probably because the InvoiceTotal field is unstored (it references a related field).

So my question is how can I further filter by InvoiceTotal.

When I enter 5000 in the global field I want to see in the portal only invoices with total greater than 5000.

I have tried a number of things with no results.

Any suggestion will be highly appreciated.

[Sanjai]

Can you tell, how you have created the relationship:

CustomerID = Customer ID and

gAmount < InvoiceTotal (gAmount is a global field in Invoice table)

How did you manage to create a relationship using customerID + amount criteria?

The relationships which I have created has always been a replacement to join clause where I want to find the matching data between two tables/files. It can be based on multiple fields. But I have never tried to create a relationship based on a matching field + certain dynamic condition.

I would have used the InvoiceTable itself where in after the user inputs the customer name and the amount, internall run a script to find only those invoices matching the customer name and greater than amount.

[gaby78]

Hi Sanjay:

I have 2 TOs of Invoice: Invoice and Invoice2. I created a relationship between the two (self join) with 2 criteria as follows:

Invoice ----- Invoice2

customID = customID

gAmount < InvoiceTotal

I tested it with 2 simple tables where InvoiceTotal is a regular number field and it works fine. However when InvoiceTotal is a calculated field that references a related field it does not work.

I like your suggestion. Could you tell me how the script would be trigerred?