Counting that number of events that occur after a date (FM11)

[jbm3]

I have two files (among others) in my database:  'Users' and 'Questions'.  

 

Users has one entry per user.  One of the fields is a timestamp with the last date/time the user logged on.  

Questions has one entry for every question asked in a forum.  It has a timestamp field that indicates when the question was asked.

 

I want to be able to count the number of questions that were asked for each user since that user last logged on.  I set up a new calculated field in Users with the following:

 

If( Questions::InsertedDate>PresentLoginDate;  Count ( Questions::InsertedDate );"")

 

My logic is to count the number of instances of InsertedDate in Questions where the date the question was inserted was more recent that the user's last login (as stored in PresentLoginDate).

 

Thank you in advance for a solution!  I've searched for related topics but come up short.

[doughemi]

Create a new TO of Questions.  Relate it to Users by Users::PresentLoginDate < QuestionsNewTO::InsertedDate .

 

Then create a calc field  Users::NewQuestions = Count(QuestionsNewTO::InsertedDate)

[jbm3]

I like the approach, and it seems straightforward, but it does not seem to be functioning.  It may be a function on my not completely understanding the approach.  Here is what I did:

 

1.  Created a relationship (and this the only relationship) between Users and Questions where Users::PresentLoginDate < Questions::InsertedDate

2.  Created a new field (NumberOfNewQuestionsSinceLogon) in Users with the calculated value of:  Count(Questions::InsertedDate)

 

However, the value of NumberOfNewQuestionsSinceLogon when I browse the Users file is blank.  There seems to be a disconnect because what you suggested makes sense.

[doughemi]

Is the calculation unstored?

[webko]

And are you certain that both the match fields are of the same Type - Date in this case?

[jbm3]

The calculation is unstored and both fields are timestamps.

[jbm3]

If the box "Do not evaluate if all reference fields are empty" is checked, then the results are all blank.  Otherwise the results are all zero.  I checked and there are appropriate values in the fields of both files.

[doughemi]

It was almost working for me, except that it was not updating in real time without refreshing the window.  I did some research, and found an old tip from fp3 days that recommended to add a portal which used the same relationship to the layout. The portal doesn't need anything in it.  Weird!  See attached example.

 

questions.fmp12.zip

[jbm3]

Clearly the portal idea was creative, but it did not work.  However, I discovered it was not because your approach was flawed, rather my file was flawed.  The fields for the PresentLoginDate and InsertedDate were indicated to be timestamps, however they were imported from a CSV file and while they looked like valid timestamp, Filemaker was treating them as text.  I created a new field and converted this text into a timestamp and everything now works using the first suggestion you provided.  Thank you so much!