Skipping Empty Fields, in Previous Records, for Calculations...

[GarrettC]

I am working in Filemaker Pro Advanced 8 on a Mac OSX 10.6.8.

 

The database is fairly straight forward: it is tracking the total number of comments (all types) and the sub total of online comments at a federal website on a new TSA regulation.

 

4 Data Fields: Month, Day, Total Comments and Online Comments (a subtotal)

 

Records are sorted by concatenating the numbers in a calculation field with the month and day fields.

 

This data is exported to a tab separated file and imported into Adobe Illustrator to make pretty charts.

 

All is well, so far...

 

Recently, we have found that there is something suspicious about the numbers supplied on the federal site, mainly that the subtotal daily increase exceeds the total comment increase.

 

To show this clearly and chart it out, I created 2 calculation fields plus functions, to find the difference between the current day and the previous day.

 

 

Comment Total - GetNthRecord(Comment Total;Get(RecordNumber)- 1)

 

and

 

Comment Short - GetNthRecord(Comment Short;Get(RecordNumber)- 1)

 

They work beautifully except for one problem: some of the day's samplings are missing. The outcome is that the calculation subtracts the current number from an empty field in the previous day, creating a large negative and inaccurate number.

 

What I would like to do is have the calculation skip an empty field and find the next, previous day field that is not empty and use it for the calculation.

 

I am not quite sure how to approach this problem. Thank you for any guidance.

Edited May 23, 2013 by GarrettC

[webko]

Run two finds for all records including today, and today less 1 and compare the Get(FoundCount) ?

 

Will allow for days that have no comments added...