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.
[Exclusivity Score]=[Exclusivity Weighting]+[Exclusivity Rating - Outof5]
[Location Score]=[Location Weighting]+[Location Rating - Outof5]
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.
Then on 9/1/2016 changed to 1.3:
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)
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:
[-] <- the image on load field = [iol] & "moduleLocations.js" & [/iol]
[-] <- 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.
If you want the two most oldest or smallest of the same you add these parameters:
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.