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

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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.

Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb
[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.
Photo of dwhawe

dwhawe, Champion

  • 744 Points 500 badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
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)
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb
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
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
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]
[Address]
[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.
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb
Good idea. Some help needed on the script to automatically select the most recent record if you have a moment.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
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.