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

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?
LVL 2
rvfowler2Asked:
Who is Participating?
 
Will LovingPresidentCommented:
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
 
rvfowler2Author Commented:
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
 
rvfowler2Author Commented:
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
 
Will LovingPresidentCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.