Revising a Contract & Making it "Inactive"

[agtjazz]

I am working on automating contract revisions…. And have a button to duplicate the contract (except for a few fields- including cn_pk and salary or other fields that are known to change on revisions)

“Revision” script is looking like:

Duplicate Record/Request

Set Next Serial Value (contract::cn_pk)

Set Field [contract: cn_memodate; “”]

Set Field [contract:: cn_salary_fall; “”]

Set Field [contract:: cn_salary_spring; “”]

Also, I have a calculation showing if the contract is active or not, based on the start & end dates of the contract period

Field is c_display_cn_active --- calculation is

If(cn_pk="";""; Case(Appt::appt_end_date ≥ Get (CurrentDate);"Active"))

But if we are revising the contract, even if the end date is in the period, I need to show that contract is Inactive because the revision has taken its place. I don’t know how to override the Active Display calculation for revised contracts.

Your suggestions are greatly appreciated.

[Brian C]

You should probably create a new field (example: cn_act meaning: contract active) which will flag your old contract as "Inactive". I prefer to use binary results (0,1) since they take up the least amount of space and are easy to remember.

0=No, Empty, Negative, Etc

1=Yes, Full, Positive, Etc

So for example, if you click the duplicate button, just use your script to flag your the field on the existing contract with a "1" so that it is deemed as being inactive, and then create your new record and make sure the new field for that record is set to a 0 or is empty.

Now you can just wrap a new if statement around your old calculation:

if(cn_act = 0 or isempty(cn_act) ;

If(cn_pk="";""; Case(Appt::appt_end_date ≥ Get (CurrentDate); "Active"))

; "Inactive")

Edited June 26, 2006 by Guest
Added more detail

[agtjazz]

Thanks that works nicely!

[agtjazz]

I just ran into an issue- when I added the cn_active field…. I can’t automate the contract being active or not. (well I probably can- I just don’t know how to)

I would like to automate it so the users don’t have to go back into the records and check the active box to inactive or 1, that was intent behind the current date portion of the script.

So how can I have my cake and eat it too? We have a cn_active field. Can it be a calculation but if a user (or the revision script) overwrites and makes the field inactive , disregard the calculation? Is that possible?

Or should I have an exit script- to go back daily and mark all records that are less than current date as inactive?

Your suggestions and advice are truly appreciated.

[agtjazz]

Hi there,

Is this possible or a dream?

Thanks bunches

[agtjazz]

I am trying to make a script for this, since I can't find any other method. If I change the CN_Status to a calculated field, then the user or any script can't overwrite it when doing a revision or something. (correct?)

So every morning, I think when the user opens the database, a script will run

I am stuck a bit at syntax

I have IF (CN_Status=Active) <

If you know of a better alternative or can help with the script, I would really really be grateful.

Thanks

[agtjazz]

I think I got it… but (again) if there is a better alternative, I would be very interested.

Set up script to go thru and compare Appt End dates to current date

Script goes like this:

Go to layout – cn_table_view

Perform Find (Cn_status=”Active”)

If [Get(CurrentDate)>Appt::appt_end_date]

Set Field [cn_status:”Inactive”]

Go to Record/Request/page [Next; Exit after last]

End If

Your feedback and thoughts are welcome

[agtjazz]

If interested... I tweaked it a bit for better performance

Go to layout – cn_table_view

Show all records

Perform find (cn_status = "Active")

Go to Record/Request/Page [First]

Loop

If [Get(CurrentDate)>Appt::appt_end_date]

Set Field [cn_status:”Inactive”]

End If

Go to Record/Request/Page [Next; Exit after Last]

End Loop

Now- to just make this a weekly or monthly procedure .....

[Søren Dyhr]

But by and large is this flagging, which means storing redundant information since cn_status JUST is another aspect of Appt::appt_end_date. This ought to be solved by relations instead by having Get(CurrentDate) as primary key in the relation and Appt::appt_end_date in a non equijoin, from where you can make say a GTRR and a perhaps a Show omitted step to establish both found sets.

This is by all means faster than flagging, and relations as such were meant to deal with syncronization issues.

It annoys me slighly to see that members who, regards themself as advanced, not cautions the weakly founded approach.

--sd

[agtjazz]

I will attempt the method of relationship.... and post back if successful or not

Thanks for the perspective

[T-Square]

It annoys me slighly to see that members who, regards themself as advanced, not cautions the weakly founded approach

Søren--

There are different approaches that yield similar results. While in many respects you are right in this situation, there are many circumstances under which a "non-optimal" approach might be used.

For example, I have found that (for whatever reason) a relational/GTRR navigational approach performs significantly slower in practice than a scripted Go To Layout/ Find specific records one, and so I continue to script these.

Similarly, it may be that in a given db environment, maintaining a flag calculation based on a relationship is too processor-intensive, and it works better from a practical perspective to store the information rather than calculate it on the fly.

So, although your solution may be purer, there are many variables involved, and not every "pure" solution is the right one.

BTW, that script would run much faster if the Get(CurrentDate) were stored in either a variable or a global, and the stored value used.

David

[agtjazz]

Thanks David.... I was thinking of putting the Get(current date) into a global... but was a little leary that it wouldn't update daily.... I will do some testing and post back what worked for my solution.

Thanks again

[T-Square]

Well, if you're scripting the setting, just be sure to set the field in the beginning of the script. Then the stored value will be the day the script is run.

This speeds things up because FM doesn't have to re-calculate the current date each time through the loop.

The more I think of it, though, the more I think Søren is right--you could build a relationship between a stored global date and the end date using a < operator, follow with a GTRR, and finish with a Replace Contents on the flag field.