One problem: Portal filtering works on the display level, functions work on the data level – which means that if you reference related data in a function, all related records are used, not just the ones in a filtered portal of that relationship (which is logical: which of possibly multiple filtered portals should be used?)
This is the reason why you would see a correct display within the filtered portal, but a calculation field referencing these fields will fail (and you'd probably need a GetSummary() anyway, which only works with a field in the same table …)
One solution: give each summary field within the filtered portal an object name, then retrieve the respective value via GetLayoutObjectAttribute ( objectNameInQuotes ; "content" ).
Another one (more robust): use ExecuteSQL() where you reproduce the relationship predicate(s) AND the portal filter in the WHERE clause.
This thing is becoming unexpectedly complicated.... So, here is the problem:
On my Transactions table I have a field labeld CLR which is a checkbox that is checked when a check or deposit shows on the bank statement. These are 'cleared' items.
Items (checks and deposits) that are not 'cleared' are 'Outstanding'
I have succeeded in creating a filtered portal that shows CLR<>"Y" (not cleared). And, I have a portal above the main portal that holds the summary of Checks 'not cleared' and Deposits 'not cleared'. This works.
The sticking point is wanting to use the summaryChecks and summary Deposits in a calculation with a user input field called 'Beginning Balance'. This is where it falls apart. FM will not allow (except perhaps with ExecuteSQL) interaction between values in a filtered portal and an input field (Beginning Balance).
So, since what I am trying to do apparently is not doable in FM filtered portals, the question becomes:
"Should I forget about using a filtered portal and use a standard transactions table that 'hides' the CLR values instead. Or, are there complications beyond belief with that as well? ha
Forgetting filter may be simple.
Or, if your relation is one to many, you can refer 'Beginning Balance' from portal table. So try to make calc field in portal table
layoutsourceTable::Beginning Balance - GetSummary(Checks Out; Checks Out) + GetSummary(Deposits Out; Deposits Out)
and put it into same filtered portal.
I skipped to read orange background image...
Beginning Balance seems to be global, so any relation is ok.
The calc filed should not be global to avoid saving result value. If it is global, you need to input BB every time to update result when portal content is changed.
FM will not allow (except perhaps with ExecuteSQL) interaction between values in a filtered portal and an input field (Beginning Balance).
That's wrong. See attached. Be aware that though this works, it's a relatively fragile method since it depends on layout objects. Study ExecuteSQL().
I can see that for all intents and purposes your method works. Thanks for creating and sending.
I have looked at Execute SQL in working a different problem and found it unpredictable (even with SQL Explorer) and very fragile that I decided to just pursue FM methods.
This is a 'freeware' app for a non-profit and I am the sole creator.
I am surprised that FM does not allow their 'portal filter' to work with 'non filtered' fields. Perhaps FM 14?
I will study your solution and see if I can make it work. Thanks again!
It turns out that the simplest solution is best.
Here is the outcome:
And here is the code:
But, I used an OnOpen script trigger to go into the Transaction table and collect all
the 'non deposit' (checks, debit card etc) transactions that are outstanding
(ie, RefCHKKDep <>"Y") and then collect the totals into global variables which
are then used as the basis for calculated fields.
I hope this helps anyone else who may be 'mystified' by FM 'filter' function
(or should I say 'non-functioning')
I think these things are done by finding records, not loop.
If you need to keep found set, you can do it on new window.
I think these things are done by finding records, not loop
Actually, just loop through the portal rows …that is the intended found set.
The purpose of the loop is to collect summarry information according to transaction TYPE. Since there will never bee more than a few hundred records, it seemed like the quickest and easiest way.