Fixing the output of a formula field once the record is created.

• 0
• Question
• Updated 3 years ago

We have a formula field that calculates per record a the weighted score against certain metrics entered into other fields.  Other over time the metrics and th weighting change depending on commercial realities. However I do not want the historic weighted scores to change.   The changes should apply to new records going forward.

• 150 Points

Posted 3 years ago

• 0
• 150 Points
[Weighted Score]=[Exclusivity Score]+[Location Score]+[Product Score].......
where:
[Exclusivity Score]=[Exclusivity Weighting]+[Exclusivity Rating - Outof5]
[Location Score]=[Location Weighting]+[Location Rating - Outof5]
...etc etc
All the [**** Weighting] fields will add up to 100%.  Over time the distribution of the weighting will change.
The issue I have in how we have set it up, is that if tomorrow I change the Weighting, all the records change.  I have found a work around which is to use the form to populate the default value of the weighting and to make it uneditable. However  it seems messy.

dwhawe, Champion

• 908 Points
Can you make your weighting fields formulas?  For example if [Exclusivity Weighting] is 1 up until 7/15/2016 then became 1.1, its formula would be:

If(
[Date field]>ToDate("7-15-2016"),1.1;
1)

Then on 9/1/2016 changed to 1.3:

If(
[Date field]>ToDate("8-31-2016"),1.3;
[Date field]>ToDate("7-15-2016"),1.1;
1)
• 72,448 Points
I suggest basing the formula on the [date created] field

var number WeightingOne = ... formula here;

var number WeightingTwo = ... formula here;

var number WeightingThree = ... formula here;

IF(ToDate([Date Created]) <=Date(2016,06,15), \$WeightingOne,ToDate([Date Created]) <=Date(2016,09,20), \$WeightingTwo,\$Weighting Three)
• 150 Points
I guess the issue I have with a date based approach within the formula, is that it:
A. falls back on me to update the formala when changing the weighting (I may as well just go back and change the default filed values)
B. Overtime the formula could get very long

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
This is what I would do:

I assume you are modeling something like the potential location of a new facility or retail store and that the three metrics [Exclusivity Score], [Location Score] and [Product Score] somehow grade the desirability of the opportunity using three weighting factors that can change over time.

I would use two tables in a parent child relationship with the following fields:

Locations Table:
[Name]
[Weighted Score]
[Exclusivity Score]
[Location Score]
[Product Score]
[Related Weights]
[-] <- the image on load field = [iol] & "moduleLocations.js" & [/iol]

Weights Table:
[Effective Date]
[Exclusivity Factor]
[Location Factor]
[Product Factor]
[-] <- the image on load field = [iol] & "moduleWeights.js" & [/iol]

In the Weights entry form I would place script in the moduleWeights.js file so that when two of the factors are selected the third is automatically set to total 100%.

In the Locations entry form I would calculate [Weighted Score] whenever any of the fields [Exclusivity Score], [Location Score], [Product Score]  were modified. Most importantly I would use the appropriate Weight Factors related through the parent child relationship. However, I would arrange for the related Weight record to be automatically calculated based on which [Effective Date] is the most recent but before the current date. In other words, I would not normally allow the user to select any Weight record randomly but rather use script to select the most recent record which was on or before the current date. This scheme would allow you to have a historic record of which weighting factors were use to calculate any particular [Weighted Score] while entering data in the most convenient manner with the least ceremony.

And when I was finished I would hold a reflection and marvel at how powerful using script is.
• 150 Points
Good idea. Some help needed on the script to automatically select the most recent record if you have a moment.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
To get  the most recent or largest of an "anything" field in a table you do normal API_DoQuery and add these parameters:

&slist=8
&options=sortorder-D.num-1

If you want the two most oldest or smallest of the same you add these parameters:

&slist=8
&options=sortorder-A.num-2

In both cases slist=8 indicates you are sorting on fid=8 and the sortorder parameter indicates ascending or descending order. The num parameter indicates how many records to limit the response to.