8 Replies Latest reply on May 14, 2012 3:11 PM by philmodjunk

    Sales Tax Report for Date Range

    ebrind_1

      Title

      Sales Tax Report for Date Range

      Post

      Hello,

      Can anyone shed some light on how to create a report of Sales Tax Collected for a Date Range?

      Thanks,

      ebrind

        • 1. Re: Sales Tax Report for Date Range
          philmodjunk

          Yes, if we knew the structure of your database. Can you provide a description?

          What I can tell you now is that if you enter find mode and enter search criteria in a date field like this:

          5/1/2012...5/31/2012

          FileMaker will find all records from the first date to the second.

          • 2. Re: Sales Tax Report for Date Range
            ebrind_1

            Thanks for the response! It's the default Invoice Template in FMP12. Where do you enter the search criteria?

            • 3. Re: Sales Tax Report for Date Range
              philmodjunk

              First you need a layout for computing and displaying this information. What do you need to see on this report? Do you just need the total sales tax for a given date range or do you need to list additional information from each invoice from that date range?

              You'll also need to define a summary field to compute the total sales tax.

              You'd enter the date range into the Invoices::Date field and perform your find once you have created that layout and placed this new summary field on it.

              • 4. Re: Sales Tax Report for Date Range
                ebrind_1

                Hello,

                I would need the following information listed in one report for each invoice in a date range:

                Invoice Date

                Invoice Number

                Company Name

                Invoice Subtotal

                Sales Tax Collected

                Invoice Total

                Then I would like the Total Amount for the following at the bottom of the report: 

                Invoice Subtotal

                Sales Tax Collected

                Invoice Totals

                Thanks!

                 

                • 5. Re: Sales Tax Report for Date Range
                  philmodjunk

                  Open manage | database |fields and select Invoices from the table drop down if not already selected.

                  Create 3 new fields: sInvoiceSubTotal, sSalesTaxTotal, sInvoiceTotal. Select "Summary" as the field type for each of these and use the "total of" option to define them as the total of Invoice Subtotal, Sales Tax, and Invoice Total respectively.

                  Click Ok until you have dismissed the Manage | database windows.

                  Enter Layout Mode.

                  Use New Layout to create a new list type layout.

                  Specify the fields you have listed in the first section as fields for your layout. If the new layout wizard doesn't put them where you want them, rearrange and resize them after dismissing the wizard. You want a narrow body layout part, one field high with each of these fields in a horizontal row.

                  Use Part setup to add a trailing grand summary layout part.

                  Use the part tool to add the three summary fields to your trailing grand summary part. Arrange them where you want them. You can use the Layout text tool to add or edit the field labels to be what you want and with the appearance that you want.

                  Now save your layout changes, enter find mode and use the date range criteria I described in my first post to enter a date range in the Invoice Date field. Perform your find. Use Sort from the Records menu to order your records if needed and you should be ready to print or save a PDF or your report.

                  Be sure to view this report in List View, not form or table view modes.

                  Print and PDF this report using the "records being browsed" option or you'll just see one line of data on your report.

                  • 6. Re: Sales Tax Report for Date Range
                    ebrind_1

                    Worked like a charm... I owe you lunch! Thanks for the help!

                    Thanks Again!

                    ebrind

                    • 7. Re: Sales Tax Report for Date Range
                      ebrind_1

                      Hello Again!

                       

                      Do you know how I can get a Total of Invoiced Items I did not Charge Sales Tax on? An Exaple is Service or Labor Items... We do not charge sales tax on those items however I have to report the total amount so my report totals would be:

                       

                      Total Invoice Totals

                      Total Non Taxed Items

                      Total Taxed Items

                      Total Sales Tax

                      Thanks!

                      • 8. Re: Sales Tax Report for Date Range
                        philmodjunk

                        Total Non Taxed Items can be computed in a calculation field that subtracts Total Taxed Items from Total Invoice Totals.