Date Field w/calc Value?

[LaRetta]

Hi Everyone!

I have two date fields representing a date range. I always enter a date in the FROM date field. I want the option of data-entering the TO date or have it *update daily* with the current date Status(CurrentDate) *if blank*. 80% of the time, this field will be blank.

Would I need two fields - one Date field and one calc? I tried date field, auto-enter calc Status(CurrentDate) but it doesn't allow me to *do not store* so it will update daily (?). This TO date will be used in scripts so it must stay current. Or should I create a calc which *unstored* is: Case(IsEmpty(ToDate),Status(CurrentDate),ToDate) and always reference that in my scripts?

How can I best accomplish this task?

LaRetta

[jasonwood]

I think your calculation would be correct, except it is an IF statement, not a case statement, and I think you should use "Today" instead of Status(CurrentDate).

"Today" is updated with the current date every time the file is opened... which won't work so well if your database is running 24 hours a day.

[andygaunt]

If this is for a date range find, why not just script it there.

If (IsEmpty (ToDate)

Set Field [ ToDate, Status(CurrentDate) ]

End If

HTH and Happy New Year

[LaRetta]

Hi Andy!

This date range will be used in the Service dB to determine if an Enrolment and Program is valid for a particular Service. And then determine the correct Contract (dB) based upon the valid Enrolment and then generate an Invoice (dB). I suppose I could run an External Enrolments script (from within the PivotInvoice script) to select the correct (valid) Enrolment (sorted >> by ToDate and ... at this point use your script to set the field only right before I bill?

If I use an External script in Enrolments, can I SET the TO Date at that time? Service to Enrolments is 1:n.

LaRetta

[LaRetta]

Hi Jason,

I forgot that Case always returns a numeric result, right? I recall getting into major trouble with an 'irritating calc' not that long ago

I guess my thinking was that a date is a number and I thought Status(CurrentDate) was the one to use to update. Thanks!

LaRetta

[jasonwood]

I forgot that Case always returns a numeric result

no no, case allows you to have a series of conditions, IF is for just one.

If (this is true, then this, else this)

Case (this is true, then this, else if this is true, then this, else if this is true, then this, else do this if none of those were true)

[jasonwood]

Wait a sec... now that I think about it... wouldn't Case work in place of IF in all circumstances? Why do we have IF???

[CobaltSky]

First of all, there is *no* reason not to use the Case( ) function for this. Both Case( ) and If( ) work equally well for single and multiple tests and results, but the syntax for Case( ) is more efficient. For example, with Case( ) the result-if-false parameter is optional, but it isn't for If( ).

Notwithstanding Andy's suggestion, you should be able to do this without setting the TO date at all, by simply using a calculation in your Service DB (either in an unstored calculation field - or better still, in an If[ ] or Set Field[ ] command within a script in the Service DB.

Either way the expression to handle the 'To' date parameter in the context of your larger date range check would be something like:

Case(IsEmpty(ToDate), Status(CurrentDate), ToDate)

Or, more simply, if the exercise is to determine whether a service date (in the Service db) falls within a date range for a related record in the Enrolments DB, you should be able to use something along the lines of:

Case(ServiceDate >= ParentChild::FromDate and ServiceDate <= Case(IsEmpty(ParentChild::ToDate), Status(CurrentDate), ParentChild::ToDate), "Valid", "Invalid")

...or within a script:

If["ServiceDate >= ParentChild::FromDate and ServiceDate <= Case(IsEmpty(ParentChild::ToDate), Status(CurrentDate), ParentChild::ToDate)"]

[place your valid enrolment actions here]

EndIf

In either case (no pun intended) there is no need to actually set or reset the ToDate field in the Enrolments database.

[CobaltSky]

Hi Jason,

The reason we have both Case( ) and If( ) is because Case( ) was introduced relatively recently to provide a more efficient syntax for single result and compound logic.

If( ) had to be retained so that the vast legacy of code written prior to the introduction of Case( ) would still work - otherwise everyone would have had to do a massive redevelopment effort to migrate all their If( )s to Case( )s.

In situations where a single tier of logic and two results are to be implemented, If is marginally more efficient than case (if only because it is two letters shorter to type!) but in all other cases, Case( ) has the edge!

[Eric Crisp]

I think it's important to note here that using the Today function can often lead to users having to wait for the system to calculate it when first opened and if you have a lot of records, this can be problematic. If this is a small system then no problem, but generally the Today function should be avoided like the plague and you can accomplish any task needed through scripting around using the Today function.

Eric

[LaRetta]

Hi Ray!

Thank you so much! I will use this formulae in the Service dB within a script as you suggested, to determine valid Enrolments. I appreciate your help

Hi Eric!

Thanks for clarifying Today! It is my understanding that there is one exception in which Today() might be appropriate. Per Vaughan, "Today has *one* advantage over Status(CurrentDate) -- it can be indexed, which means a calculation that uses Today can be used to generate a value list. However it'll break for all of the reasons Ray et al have already mentioned, so I only ever use Today for solutions where I need the field to be indexed AND I know it'll only ever be single user AND it won't be hosted on a server machine. Which means not very often."

Thanks everyone for helping me!

LaRetta

[Eric Crisp]

LaRetta,

Glad I could help. Good luck with your development!

Eric