Summary field for portal with changeable filters

[bcolburn]

Hello all,

I have a portal from a related table and it has multiple iterations on a particular layout with different filters to display different records of interest. I want to create a summary field that can summarize the DISPLAYED records (not all records from the portal's source table) for each portal shown.

I realize it is possible to solve this dilemma by using a single portal and using a global search field to conditionally define a filter (and thereby make it possible to easily solve this issue too. But is there another way to do it that allows me to display ALL the portals at the same time with summary fields? I want to give users the most information possible at once.

Ben

[LaRetta]

Hi Ben,

Put each summary from each portal within a single-row portal which is also filtered the same and place it below each portal. It will summarize only the filtered set. You can make the single-row filtered portal just large enough for the summary field 2 px wider and taller then center the field within the portal.

I want to give users the most information possible at once.

That is a good thought, Ben, but not always the best approach. When a screen has too much, Users will see very little of it. Also be careful of overloading because it will slow redraw as Users scroll through records.

I am not judging your situation; I do not know it. These are just 'be aware of' comments so you are not caught unexpectedly.

[bcolburn]

Very good idea. Thanks so much!

[bcolburn]

Should the summary field be globally stored? I think I have to create the calculation field in the same table as the portal records for it to show up and in that case, each record would have the same value, which makes me consider global storage. Thoughts?

[Vaughan]

Summary fields cannot be globally stored. They also, as LaRetta suggests, create a calculation overhead that can be significant and impact greatly on the user experience.

Eg: a client has a solution that has a Total summary field on the data entry form screen. The table has 400,000 records in it. They have a hack in place where the startup script displays only the last 10 records in the table. Should an unfortunate user hit the Show All Records command they're in for a 2 to 3 minute wait while the summary field calculates across all 400,000 records.

There is no business reason for the summary field to be on the data entry screen, but they like it there.

[bcolburn]

Right. The summary field is on a database overview layout only. But the user will still need to interact with it, even if it is not intended for modifications.

[LaRetta]

You might consider putting the various portals within tabs. This eases the load which otherwise would hit the layout simultaneously. I believe that, if not displayed, filtered portals will not calculate and neither would unstored calculations or summaries(?) Please everyone correct me if wrong ...

[LaRetta]

I think I have to create the calculation field in the same table as the portal records for it to show up and in that case, each record would have the same value, which makes me consider global storage. Thoughts?

My apology, I neglected to answer your question so I will try now ...

Yes, the summary should be in the child table. As Vaughan mentions, summaries summarize the found set. But in this case, the summary is in the CHILD table so the 'found set' of the summary field placed on a parent layout is filtered down to that specific parent. It is the redraw/recalculations switching between records which can become noticeable ... with a jidder and a flash. So many factors come into play I could not possibly cover them. How many users are being served, network configurations yada yada.

We do not know, as I said, your situation - how many fields are in the portals, whether they are unstored calculations - just test it thoroughly and be aware. And do not be surprised if it runs fast in testing and then dogs down when served. Serving solutuions opens a new world of problems and slow-downs. Surprise! And if it is too heavy in your tests, go with filtered relationships instead. Switching from one to the other wouldn't take that much time.

[LaRetta]

Eg: a client has a solution that has a Total summary field on the data entry form screen. The table has 400,000 records in it. They have a hack in place where the startup script displays only the last 10 records in the table. Should an unfortunate user hit the Show All Records command they're in for a 2 to 3 minute wait while the summary field calculates across all 400,000 records.

The summary field will be from the child table. I am not sure it will calculate through all of the parent records, Vaughan. I thought it would only calculate for the parent record being displayed, just as Sum() or any aggregate or unstored calculation. No?

[Vaughan]

LaRetta, in this instance there are no related tables, the summary field is the total of an "amount" field in the current table. Very poor design IMHO and made worse with a hack to find the last 10 records at startup instead of removing the field form the layout!

[LaRetta]

Well now I feel like I am in Twilight Zone. There IS related table (this is about portals) and Ben says, "I have a portal from a related table." And I see nothing about wanting the last 10 records from the table. ?

[bcolburn]

Alright so Vaughan, what is your opinion then? Obviously you DO use summary fields somehow. So how would you display portal record summary data?

[Vyk]

Use the single row portal method, It works wonderfully and since there is a filter in place, it is not summarizing thousands of records each time it loads.

[Vaughan]

Alright so Vaughan, what is your opinion then? Obviously you DO use summary fields somehow. So how would you display portal record summary data?

I may have miss-fired on this. I missed the reference to FILTERED portals which pose a problem. I have used a summary field in the related table to display the count of filtered records, but nothing more than that.

I'm not a fan of displaying summarised data "live" because it can be a serious performance killer when record numbers get high -- so it;s a trap for young players because it looks great in testing and maybe for a while in production until the real data sets get big, then it starts to crawl.

I think that stats should be generated as a specific step. They can be displayed in purpose-built screen separate to data entry.

[LaRetta]

I think that stats should be generated as a specific step. They can be displayed in purpose-built screen separate to data entry.

I agree! Ben said, "The summary field is on a database overview layout only" Summary fields are one of my favorite tools because of their versatility. Other aggregates are tied to one table occurrence to one child occurrence whereas summaries can freely be placed on any layout up the related line and it will produce proper results. And summaries then, by their very existence, release the power of GetSummary() which can save the day in many ways (not yet all discovered by me). I cannot recall the last time I even used Sum()...

Thank you for mentioning the potential performance hits one can experience with aggregates, Vaughan. Ben is not new to forum nor FM so I trusted he understood but others reading this thread may not. :smile3: