Calculate days past

[defectivo]

Hi there,

I have a database for a school guidance department where guidance counsellors put students on a wait list for courses that are full.

I would like very much to have a calculation that takes the original date the record was entered into the system and then looks at today's date and calculates how long the student has been waiting for a course (ex. 1 day, 7 days 14 days,...). I have a global container field with graphics to indicate each level. I know the following calculation is wrong but hopefully it can help out...

Case( Date of Entry + Today>= 1, GetRepetition( Wait Levels , 1 ),

Date of Entry + Today>= 7, GetRepetition( Wait Levels , 2 )

)

If you're reading this and laughing sorry...not the best calculation person. :)

[Fitch]

You're close:

Case(

Today - Date of Entry <= 1, GetRepetition( Wait Levels , 1 ),

Today - Date of Entry <= 7, GetRepetition( Wait Levels , 2 ) )

[defectivo]

Hi there and thank you so very very much for your help. It was so amazing to see results finally when I entered your calculation in. I did toy around with some dates and switched the month...when this happens, my graphic representation of the days past goes blank...it's like this only works for the one month. Would it be possible to get this calc to work for the entire year. I have put in:

Case(

Today- Date of Entry <= 1, GetRepetition( Wait Levels , 1 ),

Today- Date of Entry <= 7, GetRepetition( Wait Levels , 2 ),

Today- Date of Entry <= 14, GetRepetition( Wait Levels , 3 ),

Today- Date of Entry <= 21, GetRepetition( Wait Levels , 4 ),

Today- Date of Entry <= 28, GetRepetition( Wait Levels , 5 ))

Thank you very much for the help.

[rellis32]

If you add a default value at the end, it can give you a result for anything higher than 28

Case(

Today- Date of Entry <= 1, GetRepetition( Wait Levels , 1 ),

Today- Date of Entry <= 7, GetRepetition( Wait Levels , 2 ),

Today- Date of Entry <= 14, GetRepetition( Wait Levels , 3 ),

Today- Date of Entry <= 21, GetRepetition( Wait Levels , 4 ),

Today- Date of Entry <= 28, GetRepetition( Wait Levels , 5 )

; GetRepetition (Wait Levels ; 6 ))

Basically giving you a visual indicator of any record older than 1 month or there abouts...

[rellis32]

I think this may be a simpler solution, I THINK...

Let ([

_today = Get ( CurrentDate ) ;

_days = _today - dateOfEntry ;

_weeks = Int ( _days / 7 ) ] ;

GetRepetition ( waitLevels ; _weeks ))

Sorry, just realized you were using FM 5, can't do variables. You could still use something like this, you would need to make the variables a calculation field though, should just need 1 though

Weeks = Int (( Today - dateOfEntry )/ 7 )

Display = Case ( Weeks < _total ; GetRepetition ( WaitLevels ; Weeks ) ; GetRepetition ( WaitLevels ; _total ))

_total being the number of colors or what have you in the WaitLevels container field.

Edited June 30, 2006 by Guest

[defectivo]

Wow...it worked! Thank you so very much. I'm amazed to see such visual results. Thank you all for your help.