Complicated Beginner Calculation

[pandsmarine]

Hi guys,

I’m brand new working with FM and would like your help with a calculation if I may. I want a calculation to display an estimated number of paint tins needed to paint a boat based on it’s length and width. With this information I would also like the cost of the estimated paint. I'm assuming IF can not be used as there are too many variables, can CASE?

With the boats width, I've got anything over 7 classed as wide, so I've been using Jobs::BoatWidth > 7 to define narrow and wide (only two classes of widths).  I'm using the table below to give our customers a rough idea at the moment but would like information to be displayed in FM specific to boats length and width for each customer after entering that data: Jobs::BoatWidth and Jobs::BoatLength. Cost of each tin is £26.32. Hope I haven't left anything out and thanks in advance.

[Steve Martino]

I would think you would want to calculate length & width to get square feet (sq meters) and base your price and calculations on how many cans of paint are needed to cover a certain amount of area.

So if you know length and width, the first calc would be area.

The next calc would probably be based on a hard coded value for coverage by area divided by the actual area, and rounded up, for number of tins.

Then the third calc being just being number of tins X cost of Tins.

I would put Tins in a separate table, and set it up like an invoicing solutions:

Customers-------<Estimates(Invoices)>-----------<LineItems>------------Products(Tins)

[pandsmarine]

Thanks Steve,

Working out price by area is always a bit of a problem for us as the hull sides of canal boats vary. Film thickness would also vary when customers paint their own boats which will effect coverage. I'm just looking for a estimate and over the years the table in my first post has served us well. In light of this would you suggest another way?

[Steve Martino]

My suggeston to set up your tables would be a good start before you do too much DB building only to realize you have to start all over.  But more specific to your problem:

Quote

With the boats width, I've got anything over 7 classed as wide, so I've been using Jobs::BoatWidth > 7 to define narrow and wide (only two classes of widths).

I see nothing in your spreadsheet that mentions boat width.

Also how do you come up with the Narrowboat Tins and Wide-Beam Tins numbers?

Also I assume each row is one boat.  

I think again length and width, even though it's not the area number you can use, would at least give you a number you could base the number of tins needed.  Then once that number is calculated, it's simple that number times number of tins.

[pandsmarine]

Quote

My suggeston to set up your tables would be a good start before you do too much DB building only to realize you have to start all over.

So you would suggest having a completely separate table similar to the table in my first post and then reference that in calculations? Sorry if thats a really newbie thing to ask.

I've come up with calculations for simpler prices where only the boat length is a variable and I was assuming something similar would be achivied:

If (

Jobs::BoatWidth > 7 ; Jobs::BoatLength * 2.8 ; Jobs::BoatLength * 2.5

)

 

The number of tins has come about from years of experience of actually supplying the paint.