3 Replies Latest reply on Oct 4, 2016 1:22 PM by HortGroup

    Calc vs Scripts vs Tables


      I am beginning the process of building a file for landscape estimating. We are a middle man, so our client would pay us $100 for a service, and we would pay our sub-contractor $90, as an example. I will need to estimate essentially every kind of landscaping job possible. For each type of job (Mowing, Tree Pruning, etc...) there will need to be a minimum of 5 fields: Contractor Hourly Rate, Man Hours, Contractor Total, Client Total, and a site factor, such as Square Feet of lawn, or Number of trees.


      My questions (pros I could think of are in parentheses):

      1: Would you create this as one table for estimates/lineitems (ease of development)? A bunch of separate tables for each landscape type, such as Lawns, Trees, and Shrubs (more speed)? Or a bunch of tables for each possible job, such as Mowing, Lawn fertilization, and Tree Pruning (more speed, but harder to maintain)?

      2: With this many calculations, would you use calc fields (easier to see results, and cannot be easily modified)? Or would you use scripts to perform the calculations and set the fields (file size, and speed)?


      Any suggestions or comments would be greatly appreciated! Thanks!

        • 1. Re: Calc vs Scripts vs Tables

          Sales Invoices, Estimates and Purchase Orders generally all require the same basic data model--only the names change. Since you are discussing an Estimate, I'd set it up like this:




          Estimates::__pkEstimateID = LineItems::_fkEstimateID

          GoodsAndServices::__pkGASid = LineItems::_fkGASid


          This assumes that you have standard rates/prices for each good or service you might list on an estimate. If these are values that you negotiate for each estimate, you wouldn't need the GoodsAndServices table as each record in GoodsAndServices documents a single item or service that you might list on an estimate or invoice including the hourly rate or item price charged for it.


          You'd use a layout based on Estimates with a portal to LineItems to create an invoice and list the items required for that estimate. A value list formatted _fkGASid field can be used to select a Good or Service from the GoodsAndServices table with rates/unit prices looked up (copied) from that table into the line item record when you select something in the _fkGASid field.


          A list view layout based on LineItems makes a good layout for printing your estimates as it will make for more flexible printing than an estimate printed from the Estimates layout. You can pull up a found set of the LineItems for a single Estimate and include fields from the Estimates table in the Header and Footer in order to produce the needed printed Estimate.

          1 of 1 people found this helpful
          • 2. Re: Calc vs Scripts vs Tables
            With this many calculations, would you use calc fields (easier to see results, and cannot be easily modified)? Or would you use scripts to perform the calculations and set the fields (file size, and speed)?

            I'd use look ups or auto-enter calculations to pull data from GoodsAndServices into Line items. I'd then use auto-entered calculations (with the "do not replace... check box cleared) to compute the line item cost with aggregate functions in Estimates to produce totals based on the listed line items.


            Whether scripting or using calculations, the file size is going to be nearly the same. Speed will usually favor calculations over scripts in this context and is much simpler to set up and get correct results. You'll only encounter speed issues if you work with very large numbers of records--such as the line items for say 5,000 customers all at once in one big found set and then only for unstored calculations that have to re-evaluate each time you pull up a different found set.


            Auto-entered calculations (with the check box cleared) and Stored Calculations are nearly identical in how they function as long as the referenced fields are all in the same record as they would when used to compute a line item cost in the line item record from looked up values. I prefer auto-entered over calculation fields for two rather technical reasons:


            If you have to import this data into a new copy--to recover data from a damaged file or to deploy an upgraded version of your solution, 1) the auto-entered line item calculations will not re-evaluate and this makes for a faster import and 2) by not recalculating, if the current version of Filemaker is different from the one used when the line item was created, any bugs in the calculation engine that might exist in one version and not the other won't result in the value changing due to the import causing the field to recalculate.


            Many versions ago, we saw historical data in calculation fields change on us when importing data created using an older version back when FileMaker 10 was the latest release because a rounding bug in a much older version had been corrected--and I've kept that memory in mind during my design work ever since.

            2 of 2 people found this helpful
            • 3. Re: Calc vs Scripts vs Tables

              All very good suggestions. I will implement all these! Thank you very much!