Solved

FM - Creating a Total Record to Show in Portal and Export to Excel

Posted on 2013-05-29
4
598 Views
Last Modified: 2013-05-30
I have a portal that shows all Tenants for a particular property and their Rent, CAM, etc.  However, is there a way (in a portal, not in a report) to create another record that would calculate a total that would go at the bottom?  The only workaround I can think of is to create the sum or total fields and then place these fields below each column in the portal; however, portal rows vary between each property.  Any ideas?
0
Comment
Question by:rvfowler2
  • 2
  • 2
4 Comments
 
LVL 2

Author Comment

by:rvfowler2
ID: 39206178
Tried creating a report with totals at the bottom hoping it would save it this way in the spreadsheet, but instead of saving at the bottom, it saved each total as a column and repeating the total number for every record.  Not the solution.
0
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
ID: 39206289
The only way I can think of to do this within your portal records is to create a self-join relationship amongst the records in the portal, create calculation fields summing the related records, and then every record in the portal would have the totals you are looking for.

However, if I was doing it, I would simply create Calculation fields in the Property record using the Sum( ) function, one for each fields you wish to show the total for. You would then place these fields above or below the portal, aligned with the columns. You could even make it look like part of the portal - and I would suggest putting the fields at the top rather than the bottom of the portal - by adding various layout elements, lines and shading.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 39207545
Having the totals in fields on every record doesn't allow me to put them at the bottom of the respective columns, so I think putting a field at the top of each portal column is the way to go.  Too bad about the report solution; would have been nice if it exported as the last row in Excel.
0
 
LVL 24

Expert Comment

by:Will Loving
ID: 39207983
For reports like that, I will sometimes create a "Report" table used only for reporting. After importing the records needed, you create a single new record at the end and use calculations to display totals rather than individual record values. You can use the calculation test:

Get ( RecordNumber ) = Get ( TotalRecordCount )

to determine if you're on the last record. In fact, if you want your reporting table can be almost all calculations that reference the original table. Just import the Record ID and create a relationship back to original table. That way all fields but the ID are calculations that "pipeline" data from the original table. It lets you use natural field naming and the totals can be calculated by using the same relationship.

So a possible calc in the Reports table might look like:

Rent =
Case(
   Get ( RecordNumber ) <> Get ( TotalRecordCount ) ;
   OriginalTable::Rent ;
   Sum( OriginalTable::Rent )
)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now