Solved

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

Posted on 2013-05-29
4
604 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 25

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 25

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filemaker Grouping Fields and Button 1 498
Filemaker 13 Export / Import Records 1 1,008
Filemaker 14 vs Delphi Embarcadero 7 201
FFmpeg : cut starting 10 sec and ending 10 sec 5 61
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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