Omitting Records from a Portal (or anything else!)

[jimb51]

I have two files; Accounts and Payments. Accounts records Debtors and the amount owed to Merchants. Payments records multiple payments by multiple Debtors to multiple Merchants. I created a layout which displays for each Debtor a portal to Payments which summerizes his balance due to each Merchant. Example:

Debtor1 (plus all his personal info.)
(Portal)
Merchant1 $100.00
Merchant2 $50.00

This works fine. However, what I would like to be able to do is if the Balance Due goes to zero, then NOT display that particular Merchant in the portal.

I can do this manually via the Find/Omit menus but I do not necessarily want to have my users go through that maneuver. I suspect I can mimic my manual steps via scripts, though I haven't gone that route yet. My question is if there is a cleaner method of filtering records from a portal using conditionals?

 

[fmdeveloper]

The easiest way to do this is to add an additional flag field.

If (balance = 0, 0, 1)

Then create a calculation field of your current key field and "_" and the flag field that you just created.

In the database containing the portal create a field with the key field and "_1".

Now you will only view the items with a balance greater than zero.

Tim Cormier
TSC

http://www.fmdeveloper.com

 

[jimb51]

Tim -- Need some amplification. I miss spoke on the file names I have. They are Accounts and Debtors. The rest remains same.

I created new flag field in Debtors file called BalFlag. Created new calc field in Debtors file concatenating #Debtor and BalFlag. Created new field in Accounts file called DebtorID_1. Debtors/Accounts related by #Debtor/DebtorID. I did not create any new relationships. Viewed the layout but data in portal blanked out. Tried to create new relationship based on new fields but same result. I took a look at the BalFlag to see the value and it takes on the value of the first related field in Accounts, i.e., if balance is zero then BalFlag=0 so that seems to work.

Hopefully, I'm missing something simple here.

 

[jimb51]

Tim -- Okay, got it to work! Created new field in Debtor file (key)_1. Created balance flag field in Accounts file and created new field (key from Debtors)_(balance flag). Created new relationship from Debtors to Accounts based on new fields. Reassigned the fields in the old portal to the new relationship (ouch!) and updated the existing entries in the Accounts table with the new field (double ouch!!) Success!!