Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Counting Related Records Within A Date Range

mattc
in Calculation Engine (Define Fields)

Featured Replies

In my two database files (Contract) & (Contract Log), I need to be able to count the number of related Contract Logs per Contract, which fall in between two date fields (defined in Contract). At the momemt this isn't working because I suspect I'm miss-using the count command. The two files are related via a (Contract Ref No).

This is the calculation field i've been trying within Contract which returns a number, however, it seems to count all records in Contract Log, not just the ones within the date range:

Case(contract log::log date >= date1 and contract log::log date < date2, count contract log::contract ref no))

What is the correct way to do this?

Thanks

In the Log file create a calc.field "cWithinContractDateRange":

If((log-date >= contract::date1) and (log-date < contract::date2), 1, 0)

Now in the Contract file create a calc.field "cLogCount":

sum(contract-log::cWithinContractDateRange)

  • Author

Thanks - I knew it would be simple!

Cheers

Create an account or sign in to comment

https://fmforums.com/topic/10082-counting-related-records-within-a-date-range/
Go to topic listing

Important Information

By using this site, you agree to our Terms of Use.

I accept

Configure browser push notifications
Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.