Relational filter or portal filter?

[Charity]

Immediate real problem: I did this entire project without considering how to script it at the end and it is 99% done and now I hit a snag. That is like building a bridge before knowing what river it might cross. I will not make this mistake again. It is writing to variables for virtual columnar reporting using filtered portals with GetLayoutObjectAttribute().

Relationship (actually all TOs are appended with ~reporting but I dropped that for simplicity here):

Accounts::AccountID =Transactions::AccountID

and

Accounts::gStart <= Transactions::Date // corrected the operator. I had it reversed originally.

and

Accounts::gEnd >= Transactions::Date // corrected the operator. I had it reversed originally.

Other Fields influencing (both these are custom value lists):

gMode (whether Months, Years, Weeks, Department, Division, Days ) not all in place yet

gStyle (Contiguous or Comparative) comparative is apr 2010, apr 2011, may 2010, may 2011 and Contiguous is Apr 2011, May 2011, Jun 2011.

Setup: On Accounts layout, I have filtered one-row portal based upon Transactions labeled Months which turns above range into one month increment. Within portal is single Transactions::sTotalAmt Object named Months. I have other portals, each similarly labeled, filtered and object-named for years, departments …

This example: Months is one of the user selections from Mode which says what the columns should represent so I can reference the correct summary in the correct portal as I run the script. I have calculation which tells how many total columns to fill and which virtual Reports layout should display the results. When I manually increment gScriptNum, it iterates properly.

The Theory: Use global number gScriptNum within each filter on each of those portals as the increment as Apr, May, Jun or 2010, 2011. This is so that I do not have to create 12 portals for each Mode/Style combination and instead the filter calcs are iterated by script. Here is example of Months filter:

Let ( [

mo = ADMIN::gScriptNum ;

action = TRANSACTIONS::date ;

start = Date ( Month ( Accounts::gStart ) - 1 + mo ; 1 ; Year ( Accounts::gStart ) ) ;

end = Date ( Month ( start ) + 1 ; 0 ; Year ( start ) )

] ;

action ≥ start and action ≤ end

)

The Plan: Change the number in gScriptNum, commit the record and grab total from one single portal, increment gScriptNum and grab the next columns amt, in this case incrementing through months. Writing the $$amt1 line then would be (at least in theory; it has worked similarly for me in other tests):

Let ( [

amt = Filter ( GetLayoutObjectAttribute ( TRANSACTIONS::sTotalAmt ; ADMIN::gMode ) ; 1234567890 )

] ;

List ( $$amt1 ; Case ( not amt ; " " ; amt ) )

)

gMode = Object name which is ‘Months’ on the months portal.

Problem: If you have bothered to read thus far, I am indebted and impressed. If the person wants Comparative, my date range relational filter (and this is what smacked me about building a bridge to nowhere) only gives current values.

I like using relational date range to ease pressure on the portal filters and the dates can be changed by script. Volume of transactions might be up to 20,000 per year. Is that process-hungry? I would need to include Mode and Style in the relational filters then, right? Or should I drop the dates from the relationship and try to include all this logic in the portal filter calcs themselves? Then I wondered if maybe one single portal could do them all, whether Mode was Years, Department and so forth all from the relationship level with help from my script?

Anyone want to buy a bridge? No wait … wait until after I’ve jumped off of it …

modified to reverse the relational keys. I had the incorrect.

[comment]

If you have bothered to read thus far

Actually, I skipped the above and went directly to:

Volume of transactions might be up to 20,000 per year.

If you plan to filter a portal that would show 20,000 records if unfiltered, then you are very likely to be disappointed by the speed of redraw.

[Charity]

That is total for the table but it will be filtered relationally filtered per account by the AccountID. So maybe 600 per year (high) per account. Does your perspective still hold true then? And hello there, Comment, and thank you!

adjusted numbers from 5,000 to 600 per account maximum. Some accounts have three times as many as other accounts so maybe highest one would be 1,500 a year.

[Charity]

By the way, the additional requirement is ability to export with pre-named first row and not our developer table gibberish. I have done this with ID 0 in reports table (which I omit and only include as first row if needed). This is another reason I went with virtual. Since I use globals to set the column headings in the report, I can also set the first record if export is needed.

I have not written these headers yet but I found a thread which covers how to get the groups. In fact, you were in it. So I think I have that all translated from repetitions to columns.

[comment]

So maybe 600 per year

Well, that's something you'd best test on your actual system. Always keep in mind that while portal filtering can save a TO (even many TOs), the filtering test must be re-calculated for each related record at each screen refresh.

[Charity]

Comment said, "the filtering test must be re-calculated for each related record at each screen refresh."

If I use globals and a relationship and set the parent key, commit and grab the sum, would that be faster? Or is it still requiring a recalculation at screen refresh?

[comment]

Let's leave out the "grab the sum" part for a moment. With a relationship, Filemaker looks at the index of the child matchfield in order to determine which records are related. No calculations are evaluated at this point - it's the equivalent of performing a find in an indexed field.

A filtered portal, OTOH, evaluates the filtering expression for each related child record, in order to determine if it's included or not. This is a layout drawing decision, so it must be repeated at each redraw. It could be likened to displaying an unstored calculation field in list view.

---

It's actually a bit more complex than that, because "join results" are cached, and portal filtering is (probably) not - further increasing the performance gap in favor of relationships.

Edited October 31, 2011 by comment

[Charity]

So if I use relationships and write to variables for virtual, do you think it would it be faster to:

set the date range, commit, write amt1 and loop through all the records and then set the range to the next column and write amt2

or

set the date range, commit, write amt1, change date range, commit, write amt2 and complete each account as I go?

[comment]

I am afraid you have lost me at this point. I don't know what you write to variables, nor why.

Doesn't seem to be related to the original topic anyway, because if your child table is a "virtual table" then the fields cannot be indexed.

[Charity]

I am afraid you have lost me at this point. I don't know what you write to variables, nor why.

I write summaries from Transactions (please see above) because I need to produce columnar reports which you warned FM is not good at. No kidding.

Doesn't seem to be related to the original topic anyway, because if your child table is a "virtual table" then the fields cannot be indexed.

Never said the virtual table was the child. I was trying to 'grab' the totals...

Anyway, I am moving on. Just about any product on the market can handle columnar reporting. This is ridiculous. I have a lot invested in this but not too much to know when to bail. I thank you all for helping me and wish you the best with your programs.

Charity

[Charity]

Hello Comment,

I spent two weeks trying different programs. Not much luck. Then I spent two weeks creating multiple tables in the graph so I could simply get the filtered totals for my columns. But this project piece is now done and they love it. I still feel like I had the wind knocked out of me and FileMaker is not nearly as exciting as it was before. I have made it clear to management that I will never do columns again so the company may post job listings for a "column generator person." Since I do not get paid for any of this, I don't care if it costs them. I hope this is the only negative surprise.

I am beginning to understand why designers get at least 10 times the money I currently make. I may not be up to the task but I am going to try again. Thank you for making most of the tasks fun and possible for me and I hope you accept my apology if I sounded offensive.

[comment]

I haven't noticed your being offensive. Filemaker has many limitations, some of which can be very frustrating. OTOH, the best fun comes from overcoming those limitations in creative ways. :tongue:

[LaRetta]

Hi Charity,

Working in FileMaker can be frustrating but that is true for any development environment. To succeed at this, you need to have intelligence (you certainly do), must be willing to study hard (from our conversations, you are studying very hard), and you must stick through the difficult times (which you are because you are back here). I suggest you also laugh a lot and to assist you in the laughter department, here is link that I believe you will appreciate:

http://fmforums.com/forum/topic/57970-does-anyone-takes-us-seriously/

Many are given the task by their managers, like you were; many drop off and give up; you have not. With the great help available from Comment and other top developers here, you can make those big bucks too. Nothing takes the place of persistence. :wink3:

[Charity]

Thank you, Comment. I guess I had better learn to ride the waves a bit better and focus on the fun of learning. Heck, I might even do columns again.

Hi LaRetta,

You have helped me so much. I wish I had the confidence in me that you have in me. Winston Churchill once said to never never never never give up. I guess that is what I will try to do here. And laugh. I will learn to laugh more and not be such a serious wicket. :laugh2: