Aggregate Totals In Portal Row

[im2bethe1]

I am working on an invoice database (child for a Contracts DB)for my employer who wants to see totals by month/year and by year (based on the contract). Since I am not allowed to send the current file I am working on, I was able to find something close on "Afilemakeraffliction" website. In the video that goes with this file it states that it is possible to show each month individually instead of multiple duplicate month and total amounts. I was wondering if anyone could help with this in showing me how to make each month entry only appear once showing total amounts. The file uses the aggregated function which I think is great, and since I am still new at this, I just can't pin point how to resolve it. I have been searching like crazy to find examples of how this is done with little results. I would have thought this would be the type of information that all businesses would want and that there would be examples galore.

My main file (Contracts) would show each contract invoiced payments total. Using the "Record ID" to only show the related "Invoice Numbers" totals. If I could get any assistance, I would really appreciate it.

AggPortal.zip

[-Queue-]

Are you referring to a printed summary by month? You would just need a layout with a subsummary by month_year (calculation of the month and year), sorted by month_year, and previewed or printed.

[Fenton]

If you only want it to appear once in the portal, you have to make a field that only appears once in the child file.

You need a real auto-entered incrementing SerialID field in the child file. Then a calculation field:

Case( SerialID = Last(Self_State_MonthYear::SerialID),

Sum(Self_State_MonthYear::Sales Amount), "")

Last() makes it only appear on the last line for the self-relationship. Remove it and it will appear on the first.

This doubles the relationship calculations involved, so it will slow the portal somewhat. That's one reason you don't often see this kind of thing done. It's not efficient from a calculation viewpoint. It also means that there are blank lines in the portal. Unless the portal's relationship is sorted correctly, it's going to make no sense at all.

In this case you're only looking at 1 state, 1 month, 1 year; sorted by state. So it works. Other combinations may not.

[im2bethe1]

No, I need an active view to show in "Browse" mode. So as invoices are received, the total allotment is reduced. Thereby only showing totals in the example provided by total amount spent for the previous FY (Fiscal Years). Only as it is now, every invoice created shows duplication for the FY's w/total being the same, instead of one total for each year.

[im2bethe1]

Thanks Fenton, I will give it a try. If this doesn't work, my employer is just going to have to give up some money so I can actually receive some real training, the book just doesn't seem to do it for me, I am more of a hands on type person. It seems from a business stand point, FileMaker should include this type of functionality. I know for me in my company, the "Bosses" aren't very computer literate, they just want to open a screen and hit a button to see totals. They could get the same thing in a report, but sometimes they don't want another piece of paper on their desk.

Thanks again...

[Fenton]

Well, I often want to see things on the screen, in portals, etc., without running an actual report. The main advantage is that you remain in Browse mode, where you can have buttons.

In Preview mode your buttons are gone, though you can still control the interface by pausing then continuing the script; or even use another little file for "preview" buttons; you can also copy/paste the results of a short report into a container field, where it shows all cute-as-pie in Browse mode.

In other words, reports don't have to be printed. They can just be viewed, then return to wherever. But you do have to control navigation (as above).

As I said in my earlier email, the "totals only on 1 line in the portal" trick works, but is calculation inefficient, and could easily be broken by incorrect implementation.

FileMaker is a type of "scripting" environment. It is not pre-built to be a specific type of business application. It is flexible. If your boss wants/expects software to do just what he wants out of the box, he'll have to go find it. If he wants to change it substantially, he can forget it.

No, the Bosses do not have to learn to program. They should be able to hit a button (to do something that makes sense). They should be willing to pay someone who can make that happen, 'cause it ain't all that easy (but it is interesting).

[im2bethe1]

Well, for now I am going to take your advice and let them view from a preview mode. I can always make enhancements later, but for now I will give them a basic solution to pacify them. Thanks again, and if I do find a better way to do this type of function (by experimentation of course), I will email you back and let you know.