8 Replies Latest reply on May 19, 2017 10:36 AM by bob_w

    Invoices Starter Solution Items Amount Total By Item Reference

    bob_w

      Hi,

       

      I am new to FileMaker Advanced and have a question I hope someone can help with. I have modified the Invoices Starter Solution to be a database for my mechanic and have figured out a lot of it but there are a few items that I am struggling with. I current question is regarding line items on an invoice. I need to get a total dollar amount of all the line items "Labor" some invoice will have Labor listed more than once to break down different tasks performed. Then I need to calculate 3% of the total and add it to the Invoice Total as "Shop Supplies". (eg. Labor is one item as Oil  Service .25 hours at $98.00 / Hour = $24.50 and another line item Labor as Replace Fuel Pump 1 hour at $98.00 so ($98.00 + $24.50 )*.03 = $3.67 The Amount For Shop Supplies to to be added to the Invoice Total.

       

      The next question is regarding adding the vehicle information to the Invoice by selecting a record from the automobiles table (I added) that is linked to a customer and adding it to a record on the invoice. I have a temporary work around for the but the Labor issue I have not even been able to bandaid until I learn more about the scripts and calculations.

       

      I greatly appreciate any help!

       

      Thanks, Bob

        • 1. Re: Invoices Starter Solution Items Amount Total By Item Reference
          philmodjunk

          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.

          • 2. Re: Invoices Starter Solution Items Amount Total By Item Reference
            bob_w

            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.

            • 3. Re: Invoices Starter Solution Items Amount Total By Item Reference
              philmodjunk

              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?

              • 4. Re: Invoices Starter Solution Items Amount Total By Item Reference
                bob_w

                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!

                • 5. Re: Invoices Starter Solution Items Amount Total By Item Reference
                  philmodjunk

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Invoices Starter Solution Items Amount Total By Item Reference
                    bob_w

                    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.

                    • 7. Re: Invoices Starter Solution Items Amount Total By Item Reference
                      philmodjunk

                      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.

                      1 of 1 people found this helpful
                      • 8. Re: Invoices Starter Solution Items Amount Total By Item Reference
                        bob_w

                        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.