+ Start a Discussion
snippets@sfsnippets@sf 

Schema Design - Simple Calculation But very Complex implementation

 

 

Is There a better Solution ? .

 

Custom Object : Math

    Custom  Field : N1

    Custom  Field : N2

 

Calculated fields/Formula fields : ((n3*n2__c)+n1__c) Where n3 is a range of value 10 to 500 in multiples of 10.

 

Sol 1 :

====

Each record having 50 ff fields with n1 and n2


 
 Click to View sample structure

==== 

 Problems :

1. Increases the count of fields 50 // downside is if we forsee range in multiples of one. we would have 500 fields.

2. n3 is a range entered by the "end user"  So system should be able to query shown below

 

Scenario 1 :

End user  searches for n3 = 30 

Search_Value < 3800  // This could be any value

 

 [Select n1__c, n2__c WHERE FF30 =:Search_Value] ; // Note FF30 is Dynamically Placed as n3 = 30

 

 

Scenario 2 : 

End user  searches for n3 = 40 

Search_Value < 5000 // This could be any value

 [Select n1__c, n2__c WHERE FF40 =:Search_Value] ; // Note FF30 is Dynamically Placed as n3 = 40

 

Search field should be dynamically changed based on the users input which is not a good practice too.

 

 

Sol 2 :

Create a child object for Math Say Math_subset__c

====

 

  Click to View sample structure
=====
 

Problems :

1. This will increase 50 Child records for each Math Record. if we foresee  this n3 in multiples of 1 then 500 records.

Imagine we have 2000 Math records this will increase the record count of subset 2000*500 ie. 2,500,000 records which is not a feasible solution seen.

 

2. Will reduce the performance of search.

 

 

Sol 3 : Hackers Solution

create a string field which holds those precalculated values as shown below.

=====

 

Click to View sample structure
====  

Search as :

[Select n1__c, n2__c WHERE String_Field__C LIKE '%Search_Value%'] ;

 

It has its own code complexity.

 

You may wonder why we need to store n3 as precalculated value. to get an answer please check More Info

 

 

 

 

 

 

 

 

 

 

DevAngelDevAngel
Hrmmm... Cant you use a picklist field for n3?
Jake GmerekJake Gmerek
I would also look at custom settings. That could give you one list of n3 records across all math records in essence sharing them.
snippets@sfsnippets@sf

Thanks 

snippets@sfsnippets@sf

has anyone come accross simillar scenario. put there valueble inputs. 

 

Will be considered as the major break over for us.

 

 

 

 

GlynAGlynA

Create a second object, Coefficient, that has one field, N3.  Add a field to the Math object, which is a lookup to Coefficient.  Add a formula field, Value, to the Math object that computes the formula using the N3 value from the Coefficient object.  Create exactly one Coefficient record that ALL Math records lookup to.  Before you do your query on the Math object, query and update the Coefficient record to set N3 to the user's value.  Then, when you do the query, the Value formula field will have the value you want.

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator