This shouldn't be too hard to resolve for you. Can you give us a little more detail on what fields you have already and what they do?
I have two fields
c_BuildersRiskAmount= I give up on what should go here. (in excel this would be (c_TTLBasebid/100)*0.10
c_Prime Cost is a total of all of the individual estimate items. it totals correctly.
This is a three table setup as I see it so please correct me if I am wrong.
1. Individual Builders
2. Individual Bids from Builders
3. cost of Items within the bids.
actually, it is a two table set up.
EstDetail (contains all the estimate item details and totals)
EstMaster (contains totals of all of the estimate items, and general information about the estimate)
c_BuildersRiskAmount, c_BaseBid, c_PrimeCost all reside in the EstMaster Table.)
So a real world example would be.
c_BuildersRiskAmount SHOULD NOT = $999.00
This is so easy for me in excel, I don't know why i am so stumped in FM. I do appreciate your help.
You do have circular logic here. It's not clear how you can resolve it.
Why not calculate the Risk Amount on the Prime Cost instead of the Prime Cost + Risk Amount.
Our Insurance policy bases their fee on the total contract amount which includes there fee unfortunately.
It works the same as if I want to make 5% of the selling price instead of making 5% of my cost, if that makes sense.
In FileMaker speak, this doesn't need "recursion". Don't get sidetracked by that term...
Hmmm....It sounds like the premium is .1%. Is that right? One tenth of a percent ( Base / 100 * .1 = Base * .001 )?
Let ( [
x = TTLPrimeCost ;
y = .001 ;
z = xy / ( 1 - y ) ;
total = x + z
Where y = premium percentage, z = RiskAmount and total = Base Bid.
Ya the reason you cannot make it work the way you are trying to is the circular logic issue. Meaning that you have one calculation "Base" that is using "Risk" but "Risk" cannot calculate without knowing the "Base".
Does that make sense?
That makes complete sense. I suppose I over thought it. However, should this be a custom function? Or should it be in the calculation of the c_TTLBaseBid? Either way I set It up I get “Sorry, there is not enough memory to compete this operation” Here is the calculation as I entered it.
King Construction Group, Inc.
image003.jpg 3.2 K
I wouldn't use a custom function. It's probably overkill.
EstMaster::c_TTLPrimeCost = Sum ( EstDetail::ItemTotal)
If that's true, then
EstMaster::BuildersRiskAmount = ( c_TTLPrimeCost * .001 ) / .999 ;
EstMaster::BaseBid = c_TTLPrimeCost + BuildersRiskAmount
But if the risk amount percentage varies, you'll want to store that percentage in its own field and change the RiskAmount calculation a little.
If you're still getting an error message about memory, then something else may be going on...I've only seen that when viewing an improperly written custom function. Maybe you are still referencing the wrong version in your Data Viewer?
Do you know the number used by the Insurance company to determine their Fee? I've been in business a long time and it doesn't make sense to me that they can charge you a fee based on their fee.
It appears from your example the calculation only takes two iterations to get the correct amount.
Excel you set a max iteration or max change so I don't know what you have excel set to. If it is more than two iteration then you would need a custom function.
Based on the example of 2 iterations
create a number field called fBaseBid
create a calculation field called fBuildRisk with the calculation fBaseBid/100*.1
create a calculation field called sBaseBid with the calculation fBaseBid+fBuildRisk
create a calculation field called BuilderRisk with the calculation sBaseBid/100*.1
create a calculation field called BaseBid with the calculation fBaseBid+BulderRisk
I created an Example Dropbox - TestCalculationDb.fmp12