Establishing 'Rules' for Value Lists

[LindaG]

HI all,

boss wants to set the values on several of our value lists so they change automatically. example: Customer Status field contains 'Active' 'Inactive' 'dead'. He wants to enter a number smewhere (rules) and it be something like this thinking

If Customer Invoices = 0 (they never ordered) or haven't ordered within ____ years, call them dead. Between 5 years and 2 years, inactive; and if they have ordered within two years call them active.

I have calcs now that do it (unstored) but the determining time-length is set.

How would you allow boss to enter these numbers? Would I write onto layout (use text tool) the questions and place fields where he should put the proper numbers? He wants the ability to change these settings. There are many such examples - probably 15 in which he wants certain things considered. I need to give him a screen with these rules so he can plug in the numbers when he needs to change them.

Should I create a rules table? Would the field names be the questions - or would records? I have preference file for graphics and it contains one record - for the company record. Would you use that or something separate?

These rules may also include things like - COD payments reflect overdue after 30 days but they don't come in until 45 or so. He wants to be able to specify a number here (and change it) so my calculation on Overdue usings that number before they appear on the 'Needs followup' list. I hope I'm making sense. my calcs can use his numbers in them to give him more control over the entire system. Thoughts on what to do? Records or fields? Global (put there by the server?: or regular fields? I'm afraid they'll get lost if globals - never understood for sure when they stay.

The list of 'questions' may change. afraid to put them as field names but can't figure how to relate to ONE record for my unstored calcs. this needs to be very easy for him to view and use. oh. we have one file with several tables. most of the calculations and list involve our customers in one way or the other but not necessarily.

he might want a rule like - if staff works here more than ____ days, they are no longer on probation. I can write the calc but he wants to have flexibiltiy afterwards.

Lin

[QuinTech]

Hi, Lin. You seem to be on the right track. Also, you are right to be suspect of globals in a networked setting--those are stored locally, so the values of the globals on your machine may be different than they are on his.

I suggest creating a file called "Criteria". This will have for its fields all the questions you want him to be able to answer to do his "what-if" scenarios. NONE of these fields should be globals, and there should always be exactly one record in this file. Also, this file (and your main file) should have a calc field called "ONE" which is a number always equal to 1. Relate this field in the main file to the same field in Criteria, and now the values pulled through this relationship look for all the world like globals, but they are consistent from system to system.

Put those on the desired layout, and let him answer away. I'd probably use a case statement to produce the result, depending on complexity; something like,

Case ( 

  Customer Invoices = 0 , 

    "Dead" , 

  ( Status ( CurrentDate ) - InvoiceDate ) / 365 >= 5 , 

    "Dead" , 

  ( Status ( CurrentDate ) - InvoiceDate ) / 365 >= 2 and State = "PA" , 

    "Inactive and In-State" , 

  ( Status ( CurrentDate ) - InvoiceDate ) / 365 >= 2 and State <> "PA" , 

    "Inactive and Out-of-State" , 

  ( Status ( CurrentDate ) - InvoiceDate ) / 365 >= 0 and State = "PA" , 

    "Active and In-State" , 

  ( Status ( CurrentDate ) - InvoiceDate ) / 365 >= 0 and State <> "PA" , 

    "Active and Out-of-State" 

)

I just put the State thing in there as an example of how i would handle multiple criteria. You'll notice that "State <> "PA" " is redundant in both cases, as is the part about ">= 0", but i put that in there just for clarity (doesn't make any difference to the case statement).

If it gets much more complex, you may have to do some redesign so that the result is calculated through a relationship rather than a case statement, but this adds a lot more difficulty to your coding.

HTH.

Jerry

[LindaG]

Makes sense, and I see your logic in the Case(). Thank you so much, Jerry!