filtered portal and summary

[Charity]

I did not create file because I believe this is simple for you. I could not find it. Accounts table and table Bank related on Account_ID assigned in Bank when entering lines.

Want report by all Account numbers so I go to Accounts table. I want two columns, LastMonth and ThisMonth which is sum of Bank::Amount. I have summary in Bank so I create two portals and place summary in it and filter by the dates. Dandy. But

now I need to also sub total the categories those accounts are based upon. Placing that same portal with summary in parts doesn't work and now I am stuck. Ideas greatly wanted. btw can I add an object to portal itself and then use that object in a portal filter calculation? example child::field = 'portal object' and if match display the related records? Also can we name objects dynamically?

[Charity]

No ideas then?

[comment]

Usually, no replies indicate the question is not clear. I, for one, do not understand the situation described:

Accounts table and table Bank related on Account_ID

OK, so that would make "one account has many banks" - doesn't sound so reasonable, but suppose it's possible.

now I need to also sub total the categories those accounts are based upon.

And now you have lost me: is there a category field in the Accounts table? But you are summarizing by account, so how exactly is this a "sub-total"?

In general, Filemaker is best at reporting from the most atomic table (Banks?), with each sub-summary part shown as a row.

[Charity]

Hi Comment,

My apology if unclear. Bank is a list of payments and they must be assigned to a GL account number. Requested if I could give list of all accounts (even if no money against it). Accounts has field called Agent. One agent can be assigned to many accounts. There is more but if I can figure out this one sub summary then I can get the rest.

So they want a list of all accounts and to the right have columns for months. If I create the report from Bank then I have same issue - the columns and I read filter portals with summary and it works. I have 12 single row portals on rows of a list in Accounts. I have it all worked through and done except for sub total by Agent. Putting those filtered portals into leading part sorted by Agent does nothing and I feel like I'm back to needing 12 relationships. I hope not. But how else am I going to filter the summary by Agent?

[comment]

You cannot summarize the summaries of filtered portals - they do not exist anywhere except "live" on the layout.

You could use another relationship matching on agent, and base the portals in the sub-summary by agent part on this relationship.

Bank is a list of payments

Then why not call it Payments?

[Charity]

Hi Comment,

I do not know what they call it in the office. They asked me if my program could take data from them and get report like they want which can't be done in MAS90. We can call it whatever you wish since I know having clear names is important.

It sounds like only approach then is as you suggest of relation on Agent and filter portal for summary based upon date column. I will give them the report because I know it is possible from reading here so far but everyone here cringes and cries and says 'oh no not that' and so I do not look forward to having to do the full 12 relationships route. If I can achieve it using filter portals so much better right? I appreciate your ideas.

Well, I think it would be better and also because I have put so much into these 12 portals and their date filters and such that I want to use them now. If you have any other ideas, Comment, I am open to hear them although I am powerless to make them do anything.

[comment]

It sounds like only approach then is as you suggest of relation on Agent and filter portal for summary based upon date column

It's not the only approach - but it's a simple one and it eliminates the need for the 12 relationships. OTOH, it will be slower.

They asked me if my program could take data from them and get report like they want

Well, it can - but it's not really good at it. As I said earlier, Filemaker is geared towards vertical reporting.