date plus text math?

[David Nelson]

I am trying to mix true dates into a text situation and get date back. I have fields in Checklist for:

Days (they can enter any whole number for number of days)

WhenDue ( text is Before or After as an option)

Dates (text with options of "Acceptance", "Check-In"; "Check-Out" or "Created") <--- this tells them which date to base their 'formula' after

This stores the rules about automatically dating new Task records. Then in the table Tasks which is related 1 to 1 to Checklist, I have auto-enter by Replacement calculation and I want a date returned.

It works as a text result, giving me 3/3/2012+1 or 2/25/2012-3 but when I change the calculation to Date, I only get ?

Let ( [

n = Max ( 0 ; Checklist::Days ) ; // number of days defined in Checklists

op = Case ( Checklist::WhenDue = "Before" ; "-" ; "+" ) ; // this will add or subtract the days specificed from the following date result

theDate = Case (

IsEmpty ( Checklist::ChecklistID ) and IsEmpty ( Self ) ; CreateDate ;

IsEmpty ( Checklist::ChecklistID ) and not IsEmpty ( Self ) ; Self ;

Checklist::Dates = "Acceptance" ; Rental::AcceptDate & op & n ;

Checklist::Dates = "Check-In" ; Rental::Check In & op & n ;

Checklist::Dates = "Check-Out" ; Rental::Check Out & op & n ;

Checklist::Dates = "Created" ; CreateDate ;

Self)

] ;

theDate

)

I know it has to do with what I read about type casting and I think the 'op' attempt at creating an operator to put between the date and the number doesn't work well here but I cannot figure it out. Ideas appreciated.

[David Nelson]

I know it's the & op & n part that ruins it because if I remove it and use date result, I get the dates without the adjustment part on it. But how to combine it is the issue. I tried GetAsDate and Date() putting Day ( ) and none worked for me.

[comment]

How about something like =

Let ( [

baseDate = Case (

Checklist::Dates = "Acceptance" ; Rental::AcceptDate ;

Checklist::Dates = "Check-In" ; Rental::Check In ;

Checklist::Dates = "Check-Out" ; Rental::Check Out ;

Checklist::Dates = "Created" ; CreateDate

) ;



n = Case ( Checklist::WhenDue = "Before" ; - Checklist::Days ; Checklist::Days )

] ;

baseDate + n

)

BTW, why do you need a one-to-one relationship, and why couldn't this be a calculation field?

[David Nelson]

I have not tried your calc but I know it will work. - Checklist::Days ... it is very nice!

Why the relationship? People can create a template which allows them to specify a series of tasks to do by default. They set rules like above on each item they create then in Checklists. So when that type of project is created, their default tasks are created as records in Tasks related to that Project and the dates prefill when due and act as reminders to them. They have to check them off when done, which sets a CompleteDate (for tracking their swiftness) and they sometimes change the date. They also add notes onto these records and sometimes they delete one or add several 'free form' ones. And that is why I have if no .... ooops I see your calc has no ability for if there is no related Checklists? I will check that because if there is no related, it should fill with Create Date or if a User puts a date in, stay with that one.

Anway the one on one is to provide the formulas to that Tasks table where the ChecklistID is imported. That relationship is where I get the formulas. I used calculation originally but they need to change it the date.

[David Nelson]

Oh me of little faith. It was perfect.

Was there a problem with what I did with 1 to 1 so I could get the formula from Checklist? I tried to think this all through very carefully but I have narrow understandings on this stuff.

Oops nope, new free-form entry gives no date. Small adjustment I am sure and it helped for you to keep your calc so clean so I could understand it easier.

Oh that is pretty calculation. Thank you Comment again.