Jump to content

Recommended Posts

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.


Share this post

Link to post
Share on other sites

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:


Share this post

Link to post
Share on other sites

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?

Share this post

Link to post
Share on other sites

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:


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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Richard Carlton
      FileMaker Coaches' Corner - Tip 11 - Improve Performance - FileMaker - FileMaker Experts
      Get up to speed with the FileMaker Pro 17 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 27 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference ,Richard will teach you all the ins and outs of building FileMaker Solutions.  The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker 
      developers and supporting web designers. He has offices in California, Nevada, and Texas.

      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving 
      FileMaker for Startups and Entrepreneurs, and client server integration.

      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.

      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.

      RCC and LearningFileMaker.com are headquartered in Santa Clara, CA.

      Please feel free to contact us at support@rcconsulting.com
      If you want to explore building I.O.S apps for I Phone or I Pad and deploying those out to the Apple App Store.
      Here is a video introduction to our iOS App Training https://www.youtube.com/watch?v=cVxQe_yAshw
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      For More Free FileMaker Videos Check out Http://www.filemakerfree.com
      Visit http://www.learningfilemaker.com for all facets of FileMaker Award Winning Video Training.
      Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.

      Please Comment, Like & Share All of Our Videos.

      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media

      Filemaker Pro 17 Training Videos
      FileMaker 17 Videos
      Filemaker Pro 17 Video Course
    • By H
      i have a table with 4  fields
      field 1 =  number result 1
      field 2 =number result 2
      field 3= percentage result
      field 4 = calculation by taking in whichever is lower field 1 or 2 and multiplying by field 3
      the question now is how do i script the calculation it should pick up which ever is lower but ignore if field is empty.
      i hope i am clear , i would appreciate if someone could help me out with this calculation. 
    • By userfmp
      I have the following:
      Table 1 – Expenses
      Field A: Date
      Field B: Concept
      Field C: Amount
      Table 2 – Flight Time
      Field D: Date
      Field E: Flight Time (in hours)
      I need a report that will give me the expense cost per hour, i.e. the total of Field C divided by the total of Field E.
      Table 1 and Table 2 are not related. What relationship must I establish between them to obtain my desired result?
      If establishing a relationship between the tables is not the answer, how else can I achieve the figure I need?
      Thank you for any help that can be provided. 
    • By crazybake
      There are three tables:
      Services:materialName::ServiceTransactionsLineItems:Material_FK and Services:MaterialYear::ServiceTransactionLineitems:TransactionLineItemYear
      (Services may change from year to year, and within one transaction you might line items for both current and future years.)

      :Services:MaterialBudgetAllocation is a user entered number.
      What I am hoping to do is: Get the yearly total amounts of each material (Material_FK) and subtract that from Services:MaterialBudgetAllocation to get a remaining amount.
    • By the Otter
      As most people here probably know, the Let ( ) function can be used to define a Local variable. As such, it is possible to build a custom function that defines such a variable, and it is further possible to set said variable to a value including itself. An example would be the following custom function, ErrorList, consisting of the following calculation:
      Let ( $ErrorList = List ( $ErrorList ; Get ( LastError ) ) ; "" )
      If a Set Variable script step sets the same variable as a custom function like the one above, e.g.
      Set Variable [ $ErrorList ; Value: ErrorList ]
      …the script step will run appropriately, so long as the contradictory variable—in this case, $ErrorList—is not yet defined. However, once this variable has been defined, executing the preceding script step will cause FileMaker 14 (and perhaps other versions) to suffer an Error #1213 and crash the application. The workaround for this behavior is to have the Set Variable script step set a dummy variable, e.g.
      Set Variable [ $x ; Value: ErrorList ]
      Even if $x is not referenced anywhere, having a script call the ErrorList function passes the variable $ErrorList to the script’s own context, thus allowing its value to be accessed by later steps in the same script (including subsequent calls to the ErrorList function itself).
      In FileMaker 15, this behavior has been changed: local variables defined within a custom function are now valid only within the scope of the function itself, including any recursions. While this alleviates the problem of application crashes, it also results in unexpected behavior when scripts written in earlier versions of FileMaker rely on custom functions to set local variables. When migrating to FileMaker 15, each affected script step must be updated to set the target variable explicitly instead of relying on the custom function to do the work. In other words, the code:
      Set Variable [ $ErrorList ; Value: ErrorList ]
      …which proved fatal in FileMaker 14, is now required grammar for FileMaker 15: FileMaker 15 believes that what happens in the function stays in the function, instead returning the result of the calculation to the variable defined in the Set Variable script step. The FileMaker 14 grammar,
      Set Variable [ $x ; Value: ErrorList ]
      …thus sets $x to the intended value of $ErrorList while leaving the value of $ErrorList as null.
      Unfortunately, this cannot work effectively in a mixed-installation environment: the FileMaker 14 grammar leaves FileMaker 15 clients with unintended null values; the FileMaker 15 grammar causes FileMaker 14 to crash. When upgrading all users to FileMaker 15 is not feasible, the best workaround is to use the FileMaker 14 grammar, then once all relevant script steps are complete, check the value of the intended variable (e.g. $ErrorList) and, if empty, set it to the value of the dummy variable (e.g. $x).

Important Information

By using this site, you agree to our Terms of Use.