
1. Re: I need help writing a recursive function for a "retail calculation"
mgostovich Apr 4, 2016 12:58 PM (in response to tigerdad)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?
Michael

2. Re: I need help writing a recursive function for a "retail calculation"
tigerdad Apr 4, 2016 1:04 PM (in response to mgostovich)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_TTLBaseBid=c_PrimeCost+c_BuildersRiskAmount
c_Prime Cost is a total of all of the individual estimate items. it totals correctly.

3. Re: I need help writing a recursive function for a "retail calculation"
mgostovich Apr 4, 2016 1:11 PM (in response to tigerdad)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.

4. Re: I need help writing a recursive function for a "retail calculation"
tigerdad Apr 4, 2016 1:22 PM (in response to mgostovich)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_TTLPrimeCost=$999,000.00
c_TTLBaseBid=$1,000,000.00
c_BuildersRiskAmount=$1,000.00
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.

5. Re: I need help writing a recursive function for a "retail calculation"
DavidJondreau Apr 4, 2016 1:28 PM (in response to tigerdad)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.

6. Re: I need help writing a recursive function for a "retail calculation"
tigerdad Apr 4, 2016 1:33 PM (in response to DavidJondreau)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.

7. Re: I need help writing a recursive function for a "retail calculation"
Doug Staubach Apr 4, 2016 1:40 PM (in response to tigerdad)Hi tigerdad
I created a demo file to teach the basics of recursion. Hopefully it would be of help to you?
Thanks,
Doug

8. Re: I need help writing a recursive function for a "retail calculation"
DavidJondreau Apr 4, 2016 1:47 PM (in response to tigerdad)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 )?
How about...
Let ( [
x = TTLPrimeCost ;
y = .001 ;
z = xy / ( 1  y ) ;
total = x + z
];
total
)
Where y = premium percentage, z = RiskAmount and total = Base Bid.

9. Re: I need help writing a recursive function for a "retail calculation"
mgostovich Apr 4, 2016 1:58 PM (in response to tigerdad)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?

10. Re: I need help writing a recursive function for a "retail calculation"
tigerdad Apr 4, 2016 2:08 PM (in response to DavidJondreau)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.
Let([x=c_TTLPrimeCost;
y=.001;
z=x*y/(1y);
total=x+z
];
total
)
Tobin Stevens
King Construction Group, Inc.
P.931.614.0782
F.931.909.0032
C.931.260.8327

image003.jpg 3.2 K


11. Re: I need help writing a recursive function for a "retail calculation"
DavidJondreau Apr 4, 2016 2:18 PM (in response to tigerdad)I wouldn't use a custom function. It's probably overkill.
I'm guessing?
EstMaster::c_TTLPrimeCost = Sum ( EstDetail::ItemTotal)
If that's true, then
EstMaster::BuildersRiskAmount = ( c_TTLPrimeCost * .001 ) / .999 ;
EstMaster::BaseBid = c_TTLPrimeCost + BuildersRiskAmount

12. Re: I need help writing a recursive function for a "retail calculation"
DavidJondreau Apr 4, 2016 2:23 PM (in response to DavidJondreau)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?

13. Re: I need help writing a recursive function for a "retail calculation"
mgostovich Apr 4, 2016 2:44 PM (in response to tigerdad)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.

14. Re: I need help writing a recursive function for a "retail calculation"
schamblee Apr 4, 2016 3:04 PM (in response to mgostovich)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