You can add a calculation field that uses If or case to calculate this additional amount only if the line item is for labor. A calculation field in the invoice table can then use sum to calculate the total.
Thanks for the very quick response, I was trying to do this but am not having any luck with the syntax. I am looking a buying a good book on FileMaker calculations and scripts as I have no programming skills but will learn as much as I can. I figured out a few calculations and simple scripts already but I am banging my head on my desk with this one.
As I recall, Invoice Data is the name of the line items table in the starter solutions. When you add a Labor line item what distinguishes it from other line items that are not labor charges? What data in what field identifies them as labor charges?
Yes you're correct Invoice Data is the table the Item field contains "Labor" the Amount Field contains the amount for the item (calculated by unit price times quantity). It really doesn't matter but FYI labor is the only product that we allow more than once in the product table as all parts in the product tablet are unique item numbers but Labor has different descriptions to describe what labor was done to the car with the time allotted for different tasks. Thanks again for your time and your help I really appreciate it!
1 of 1 people found this helpful
Contains "Labor" or has the exact text "labor" in the item field?
Assuming that it has the exact text "labor":
Simple but brittle form:
If ( Item = "labor" ; cost * 0.03 )
Define that as a number field named ShopSupplies with the above expression as an auto-enter calculation and with the "do not replace..." check box cleared.
In Invoices, add a calculation field, TotalShopSupplies defined to do this calculation:
Sum (Invoice Data::ShopSupplies)
Less simple but more flexible:
If ( Item = "labor" ; cost * $$ShopSupplyRate )
With this expression you set up a field in a 1 record preferences table where you enter 0.03 as the shop supply rate. A script, performed by OnFirstWindowOpen, would use the value in this field to set $$ShopSupplyRate. This way, if you later need to modify the rate charged, you edit the field in the preference table instead of redefining a calculated expression.
Awesome I will try it in the morning. My logic was flawed I was trying to create a Labor Total field in Invoices as a calculated field pulling the information from the invoice data table into the calculation. Adding two fields one in each table makes sense. Thanks again and have a nice night. I am buying the FileMaker Advanced Training series book as soon as they update it for 16 I hope to get competent enough to make a few slick apps soon.
1 of 1 people found this helpful
Using ExecuteSQL you could use a single calculation field in Invoices to compute this total, but the method that I've posted here is easier for someone still learning to implement it.
Thanks works like a charm. I have a few more things to add to the database for my mechanic and he'll be able to test it soon I'll be asking a couple more questions in the near future. I need to figure out how to select one record from a different table and add it to the working table,
currently I am reading up on it if I can't figure out will post again.