Creating an Efficient Script [ Struggling!]. Please Help.

[allan_2009]

Hi All,

I've been working on this code and it seemed very redundant for me. But I cant seem to create a short but efficient code.

Here's the scenario;

1. I have Employees(EMP) and Human Resources(HR) Table.

2. EMP contains the personal info and HR contains all the requirements which includes (11 status (text) field, 11 exp or renewal (date )fields and 11 days to expire (calc - renewal_date - get(currentdate)) field.

3. I developed a long repeating script that will trigger and remind if requirements are going to expire "EXPIRING", EXPIRED or CURRENT.

Here's the Code:

Requirements [ Tracking]

Perform Script [ “Allow User Abort” ]

Perform Script [ “Set Error Capture” ]

Enter Browse Mode

Set Variable [ $Red; Value:TextColor ( "Expired" ; RGB ( 220 ; 0; 0 ) ) ] Set Variable [ $Green; Value:TextColor ( "EXPIRING" ; RGB ( 0 ; 128 ; 0 ) ) ]

#License Tracking

Set Variable [ $Lic; Value:HUMAN_RESOURCES::xlic_days_exp ]

If [ IsEmpty ($Lic ) ]

Set Field [ HUMAN_RESOURCES::license_status; " "]

Commit Records/Requests

[ Skip data entry validation; No dialog ]

Else If [ $Lic ≥ 32 ]

Set Field [ HUMAN_RESOURCES::license_status; "CURRENT" ]

Else If [ $Lic > 0 and $Lic < 32 ] Set Field [ HUMAN_RESOURCES::license_status; $Green ]

Else If [ $Lic ≤ 0 ] Set Field [ HUMAN_RESOURCES::license_status; $Red ]

End If

#Annual Eval Tracking

Set Variable [ $APE; Value:HUMAN_RESOURCES::xapr_days_exp ]

If [ IsEmpty ($APE) ]

Set Field [ HUMAN_RESOURCES::apr_status; " " ] Commit Records/Requests[ Skip data entry validation; No dialog ]

Else If [ $APE ≥ 32 ]

Set Field [ HUMAN_RESOURCES::apr_status; "CURRENT" ]

Else If [ $APE > 0 and $APE < 32 ]

Set Field [ HUMAN_RESOURCES::apr_status; $Green ] Else If [ $APE≤ 0 ]

Set Field [ HUMAN_RESOURCES::apr_status; $Red ] End If

#Auto Insurance Tracking

Set Variable [ $Auto; Value:HUMAN_RESOURCES::xauto_days_exp ]

If [ IsEmpty ($Auto) ]

Set Field [ HUMAN_RESOURCES::auto_status; " " ] Else If [ $Auto≥ 32 ]

Set Field [ HUMAN_RESOURCES::auto_status; "CURRENT" ]

Else If [ $Auto > 0 and $Auto < 32 ]

Set Field [ HUMAN_RESOURCES::auto_status; $Green ]

Else If [ $Auto≤ 0 ]

Set Field [ HUMAN_RESOURCES::auto_status; $Red ] End If

#CPR Tracking

Set Variable [ $CPR; Value:HUMAN_RESOURCES::xcpr_days_exp ]

If [ IsEmpty ($CPR) ]

Set Field [ HUMAN_RESOURCES::cpr_status; " " ] Else If [ $CPR ≥ 32 ]

Set Field [ HUMAN_RESOURCES::cpr_status; "CURRENT" ]

Else If [ $CPR > 0 and $CPR < 32 ]

Set Field [ HUMAN_RESOURCES::cpr_status; $Green ] Else If [ $CPR ≤ 0 ]

Set Field [ HUMAN_RESOURCES::cpr_status; $Red ] End If

----and so on(11 times)----

The CODE looks COUNTER PRODUCTIVE and am seeking an expert advise.

Thank you very much!

[mr_vodka]

Why arent your just using calcs and conditional formatting?

Case ( license_status ≤ 0; "Expired";

license_status < 32; "Expiring";

"Current"

)

Then you can use conditional formatting to color it.

Edited September 10, 2009 by Guest
added calc

[allan_2009]

John,

Thank you very much for your help. Wow! That's just tell you I am a newbie.

Follow up question;

1) Is there a way I can eliminate "daystoexpire" calc fields or it is necessary to have a boolean calc result container ).

2) If NOT, should I make it a global field to decrease the load in my solution?

Thank you very much again, John! :

Allan

[allan_2009]

Hi John,

From your recommendation, here's what it ends up with ( still with the "daystoexpire" field.

Case (

xlic_days_exp ≤ 0;

TextColor ( "Expired" ; RGB ( 220 ; 0; 0 ) ); //Red

xlic_days_exp < 32; TextColor ( "Expiring" ; RGB ( 0 ;128; 0 ) ); //Green

"Current"

)

I didn't have use the conditional formatting anymore.

It didn't work when i placed the calc script inside the license_status. Or did I place it in the wrong field?

Allan :

[mr_vodka]

Try this:

Let ( d = renewal_date - Get( CurrentDate );

      Case ( d < 1; "Expired";

             d < 32; "Expiring";

             "Current"

           )

    )

I would still use conditional formatting on the status field as its a better option. You can use calcs such as this for it.

Self = "Expired" // red

Self = "Expiring" // green

[allan_2009]

This works great! Thank you very much!