Dear FM Forums Users

I’m looking for some assistance to filter records based on a 2 table relationship.

My solution has the following tables:

1. Users (contains fields - ID, Name, account creation date, etc)
2. Activities (contains fields -  Activity ID, User ID, start date, start time, etc)

Real life example: Lets say i have 10'000 registered users in the Users table, with registration dates ranging from the 1st Jan 2018 to 11th May 2019.

Desired output:

I would like to know how many users in the registration timeframe above performed an activity from the 1st Oct 2018 onwards.

I assume I need to join the two tables via the common user id and then create 2 x global fields for the date ranges.  

Any advice/help would be greatly appreciated.

Kind regards

Edited May 11, 20196 yr by dodger
grammar edit

4 hours ago, dodger said:

I would like to know how many users in the registration timeframe above performed an activity from the 1st Oct 2018 onwards.

The simplest method would be to perform a find in the Users table while entering criteria in fields of both tables. Then use the Get (FoundCount) function to retrieve the number. This is of course assuming you do have a relationship based on matching UserID.

To get the same number by counting related records would be more complex. You would need a group of three table occurrences, something like:

AnyTable::gMinStartDate ≤ Acitivities::StartDate

and then:

Acitivities::UserID = Users 2::UserID
AND
Acitivities::gMinRegistrationDate ≤ Users 2::RegistrationDate

With this in place, you can count the related records from Users 2 (from the context of AnyTable) to get the same number as before.

 

Another option worth mentioning here is the ExecuteSQL() function.

 

 

 

 

Edited May 11, 20196 yr by comment

Thank you.  That has done the job 🙂