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.

Tricky date calculation within a relationship

bigjnz
in Relationships

Featured Replies

  • Newbies

Hi Team,

 

This one is doing my head in a little and I am stumped for the best approach.

 

So from the start....

 

I have a large Performer Health DB that I am making currently and it is based on an older access DB, the main function is to track injuries and treatments of those injuries.

Each Performer has a record, related to that are each performers Injury records, related to that are the individual treatments for those injuries.

 

Performers > Injury > Treatment.

 

When a performer has an injury they are treated and the treatment record is updated with a status that is either Restricted, Available or unavailable depending on the seriousness of injury.

 

What I am wanting to do is work out how many days total each injury has had the performer restricted and how many days total each performer was unavailable.

 

Each treatment record gets a treatment date auto entered, so I am using this for calculations through relationships to work out the total days, the relationships look for the oldest unavailable date and the newest available date and work out the difference, I am doing the same thing for restrictions.

 

My Problem is this.....

 

The above system works fine for fine when the treatment pattern is as below....

 

Treatment 1 - 1/1/2013 - Unavailable

Treatment 2 - 3/1/2013 - Restricted

Treatment 3 - 10/1/2013 - Available

 

The current calculations I have will work out days unavailable or restricted no matter what order they come in, ie Treatment 1 restricted and treatment 2 unavailable.

 

It fails however when the treatment pattern is a such...

 

Treatment 1 - 1/1/2013 - Restricted

 

Treatment 2 - 3/1/2013 - Unavailable

Treatment 3 - 10/1/2013 - Restricted

Treatment 4 - 13/1/2013 - Available.

 

What I need is a way to calculate the days total between status changes and then add them for an injury so treatment 1 =  2 days restricted, treatment 2 = 7 days Unavailable, treatment 3 = 3 days restricted, totalling 5 days restricted and 7 days unavailable.

 

Any help or ideas any can provide would be greatly appreciated,

 

 

Create an account or sign in to comment

https://fmforums.com/topic/88366-tricky-date-calculation-within-a-relationship/
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.