Portal summary field to calculate difference between first and last portal row

[Richard Burrow]

Hi all -

 

I'm working on a reporting tool to display the number of facebook page likes a page has and track it over 3 months. 

 

I have a portal displaying the number of facebook page likes over a 3 month period (portal fixed at 3 rows)

 

(portal row 1)    31-Dec-2013   Facebook Likes:  1806

(portal row 2)    31-Jan-2014    Facebook Likes:  1917

(portal row 3)    22-Feb-2014    Facebook Likes   2017

 

I need to calculate the increase / decrease between the first and third portal row eg:

 

difference between 2017 and 1806 (211)

 

Any ideas on how this can be achieved?

 

I can only seem to summarise totals / averages etc with summary fields. 

 

[comment]

(portal fixed at 3 rows)

 

What determines which records are shown in those 3 rows? I am assuming that there are more than three related records - so how exactly do you pick those three?

[Richard Burrow]

Hi - the portal showing records from table "facebook page likes" is placed on a layout showing records from table "report".

There is a ReportID foreign key in table facebook page likes. 

 

The portal is essentially just showing only related records with the same ReportID key.

[comment]

If the portal shows the entire related set, you can calculate the increase very easily as =

Last ( PortalTO::Value ) - PortalTO::Value

This will calculate the difference between the last (non-empty) related record and the first one (following the sort order of the relationship).

 

 

 

There is a ReportID foreign key in table facebook page likes.

 

That sounds a little strange - and probably unnecessary. A "report" table is also not required in most cases; as a general rule, your tables should hold data about some tracked entity.

[Lee Smith]

Hi Richard, and welcome to the Forum,

 

I moved your topic from "FileMaker 13 General Discussion" to "Portals”, because the General Topic for version 13 is reserved for questions about the tools that were new in that release of FileMaker 13, and not for asking how-to questions.

 

Lee

 

.

[Richard Burrow]

Hi Lee Smith - Thanks for the advice. 

Hi Comment - thanks for taking the time to reply to my post - your comment gives me hope. I've been struggling with this for hours. 

 

I have tried your suggestion - I have created a new calculation field adding:

Last ( PortalTO::Value ) - PortalTO::Value

to produce the calculated result. 

I then tried adding this new calculation field to the portal and also outside of the portal just on the reporting layout, but neither produced a result. It just showed "0"

 

What am I doing wrong?

[comment]

In which table is the calculation field defined? It should be in the parent (Report) table. Placing it in the portal shouldn't matter - though it makes little sense, since there is only one result for the entire related set.

[Richard Burrow]

That was what I was doing wrong. I created the calculation field, in the "facebook likes table" not the report table. 

 

Its working perfectly now. THANK YOU SO MUCH FOR YOUR HELP!