Hello:

I have a customer layout based on customer table with a portal based on invoice table that shows invoices for each customer.

Question: How can I filter the portal to show invoices with balance different than zero? Currently the portal shows all invoices; invoices with a balance and invoices with 0.00 balance. I want to hide invoices with 0.00 balance.

I tried a number of things but nothing has worked so far.

I am stuck, any idea will be highly appreciated.

You can create a global field in the customer file and have it set to "1", while the file is not being hosted. When you host the file this field will remain, if you do this while file is being hosted, the value will be lost. (If you want to do this while file is being hosted, create the field but do not make it global. Make the field have an auto enter value of "1". You will then go to your new field and type "1" without the quotes, show all records, then perform a replace with the value of 1).

In your invoice table, create a calculation field such as:

EXCLUDE_INVOICE = if (InvoiceBalance > 0);"1";"")

In your relationship graph, click on the "=" representing your current relationship and then click on the new fields to relate them. Do not click on CHANGE but instead, click on ADD.

Your portal will now show ONLY invoices with a balance.

Hi Breezer:

I had tried this before with "Y" instead of "1", it did not work. When I link the global field in Customer to the new calc field in Invoice table the portal shows no records at all. I gave it a try with "1" as you suggested same result. I think (not sure) it's because the Invoice balance field cannot be used for filtering in a portal and any calc field based on it won't do either.

I think this a major weakness in FM, in a portal you cannot filter a cal field that gets its value from a related table. That's at least my personal experience.

Any FM guru out there who can shed some light on the issue?

Try something like this:

In Invoices, create a calculation field cCondID =

Case ( Sum ( Related::Amount ) ; InvoiceID )

Make the result same type as your InvoiceID field.

Now create a new TO of Invoices and relate it to Invoices:

Invoices::cCondID = Invoices 2::InvoiceID

Place a portal to Invoices 2 on Customer layout.

Hello Comment:

I gave it a try but did not work. The portal shows no records at all. If this solution worked with you, could you please attach a sample.

Thanks

I'd prefer you post a sample of your own first - so that we can see your structure and how you compute invoice balance.

Hi Comment:

Here is the file.

thanks.

MTHZ.ZIP

Edited January 8, 200620 yr by Guest

make sure the relation keys are of the same type, ie, if you used "Y" before, then both key fields must be TEXT, for my example you can use either NUMBEr or TEXT but must be of same type from the INVOICE table and CUSTOMER table. Also the field in INVOICE must be stored and indexed.

The relationship between Invoices and Invoices 2 should be:

Invoices::cCondID = Invoices 2::InvoiceID

not the other way around.

It works!!

THANKS COMMENT.

THANK YOU - THANK YOU - THANK YOU - THANK YOU

Comment:

How would you change the formula in the cCondID field if the criteria were different, like Invoices with a balance above $10 or invoices with a credit balance (Balance < 0.00).

Thanks again.

Hi

the calc that you have now is:

Case ( Sum ( Related::Amount ) ; InvoiceID )

that is the same as :

Case ( Sum ( Related::Amount )> 0 ; InvoiceID )

So, if you wish to find > 10, the calc becomes:

Case ( Sum ( Related::Amount )> 10 ; InvoiceID )

On the other side, if you wish negative numbers:

Case ( Sum ( Related::Amount )< 0; InvoiceID )

Thanks Daniele, it worked perfectly.

Thanks again.

the calc that you have now is:

Case ( Sum ( Related::Amount ) ; InvoiceID )

that is the same as

Case ( Sum ( Related::Amount )> 0 ; InvoiceID )

Just for the record:

Case ( Sum ( Related::Amount ) ; InvoiceID )

is the same as:

Case ( Sum ( Related::Amount ) ≠ 0 ; InvoiceID )

IOW, negative values are also true. Only zero is false.

Hi comment

another right correction ! :B