Solved

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

Posted on 2013-05-29
4
594 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

771 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

10 Experts available now in Live!

Get 1:1 Help Now