11 Replies Latest reply on May 28, 2015 4:03 PM by philmodjunk

    Invoice Dashboard Setup

    laguna92651

      Title

      Invoice Dashboard Setup

      Post

      I have a straight forward Order Entry application. I want to summarize products sold by Code Size and Type, Customer data is entered into a Invoice header with child Line Items. I established a relationship to the global table called Dash with the Products table. I can summarize the product Code, Size and Type (in the product table) in 3 different portals, by quantity and amount (in Line Items) just fine .

      But I cannot figure out how to filter the data for a preselected yearly interval, the selected year field is a global field in the Dash global table, but I can't figure out how to get to the Invoice header to set up the relationship for the date interval, do I store the invoice date in the order lines?

      I tried going the opposite direction but then couldn't sum the product info. Not sure which way is correct.

      Dash----<Invoice---<Lines>----Products

       

      Screenshot_Dash1.png

        • 1. Re: Invoice Dashboard Setup
          philmodjunk

          Try linking Dash directly to line items and then use a portal filter that references the date in Invoice to filter out line items for other years than the year required.

          • 2. Re: Invoice Dashboard Setup
            laguna92651

            Thanks for the help, I was able to filter all of the Invoice lines by year wanted, but no summary.

            What I am trying to get is this summation of total quantity sold and to amount sold for each Code for the year selected, first table shown. The Code list is not fixed and will be added to and deleted. The second table is what I got by linking the lines table to the dash table. Both the invoice and product table are connected to the lines table, this did filter the data by year fine.

            Dash----<Lines>---Invoice

                                    L----Products

             

            • 3. Re: Invoice Dashboard Setup
              philmodjunk

              I can think of two ways: Execute SQL or a relationship that puts the specified year in a global field in the products table:

              Here's the second method:

              Dashboard::anyField X Products::AnyField

              Products::ProductCode = LineItems::ProductCode AND
              Products::gYear = LineItems::Year

              gYear would be the global field. LineItems::Year would be a number field that auto-enters the correct year. It might be set up as:

              Year ( Invoices::SaleDate ). Note that for existing line items, you'd need to use replace field contents to initialize this new field. Also note that if you change SaleDate in an invoice after creating line items, the line items Year value won't change. That's pretty unlikely for the year, but you can set up a script trigger on SaleDate that can check for related LineItems and update them to be absolutely sure that all line items have the correct year.

              A calculation field in Products or a summary field in Line Items can compute the product code/year specific sub totals.

              • 4. Re: Invoice Dashboard Setup
                laguna92651

                That worked great, thanks. I also used the Replace Field Contents in the script to update the invoice lines, is that the approach?

                What would you recommend as an approach to sum each months total sales from the invoice header for a selected year?

                • 5. Re: Invoice Dashboard Setup
                  philmodjunk

                  A relationship that matches by month to invoices (if you want a grand total) or to line items (if you want subtotals by product) are both possible. So is a summary report--based on Invoices if you want monthly totals over all sales and based on line items if you want to break it down by product for each month.

                  • 6. Re: Invoice Dashboard Setup
                    laguna92651

                    I have done a summary report layout, that breaks things done and sorts. But I also wanted to put a portal on the dashboard that sums sales by month for a selected year, the dashboard also includes the portal I just did for the product codes. Would I need to do 12 tables related by monthly intervals or is there a more elegant way to do it?

                    • 7. Re: Invoice Dashboard Setup
                      philmodjunk

                      A table with 12 records, one for each month might be used as a kind of "filter" to do this with a single portal.

                      Dashboard X Months

                      Months::cMonth = Invoices::cMonth

                      cMonth in Months might be defined as an unstored calculation field with:

                      Date ( Get ( RecordNumber ) ; 1 ; Year ( Get ( CurrentRecord ) ) )

                      it might also be written as:

                      Date ( Get ( RecordNumber ) ; 1 ; Globals::gYear )

                      so that the user can specify the year

                      Other variations here are also possible that generate dates for the first day of the month for different months and years-such as a simple date field and a script that adds a new record with a new first of the month date with every new month...

                      cMonth in Invoices can be defined as:

                      SalesDate - Day ( SalesDate ) + 1

                      so that this indexed and stored calculation can compute the date of the first day of the month for the same month, year as salesDate's date.

                      • 8. Re: Invoice Dashboard Setup
                        laguna92651

                        Okay thanks, I will give that a try. You'd had mentioned earlier about using Execute SQL as a way to sum records on a portal. I've never used SQL, can you point me to some basic and simple tutorials/examples on learning Execute SQL?

                        • 9. Re: Invoice Dashboard Setup
                          philmodjunk

                          Don't have any tutorials available. You can web search SQL, but then compare the examples in such a generalized examples to the specific syntax found here in FileMaker Inc.'s reference doc: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                          SeedCode has a handy tool for writing queries to use with ExecuteSQL that will have correct syntax: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                          And you may find this thread showing one example query for getting sub totals for different groups all in one field of interest: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                          Do you have FileMaker Adavnced? It's extremely useful to test ExecuteSQL results in Adavnced's data viewer and I discovered a custom function that can be used with a monitored watch expression to return actual error messages from FileMaker in place of the dreaded ? result that you otherwise receive.

                          • 10. Re: Invoice Dashboard Setup
                            laguna92651

                            I will check these references out. Yes, I have have FM Advanced, what is the custom function you mentioned? Thanks again.

                            • 11. Re: Invoice Dashboard Setup
                              philmodjunk

                              This is the most unusual custom function that I've seen, but it works:

                              If (

                              //the sql call results in an error, return empty so the error will be returned
                              _executeSQL = "?" ; "" ;

                              //the sql call is executed correctly, just return the result
                              _executeSQL
                              )

                              // ===================================
                              /*

                                  This function is published on FileMaker Custom Functions
                                  to check for updates and provide feedback and bug reports
                                  please visit http://www.fmfunctions.com/fid/335

                                  Prototype: sql.debug( _executeSQL )
                                  Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
                                  Last updated: 28 July 2012
                                  Version: 2.2

                              */
                              // ===================================

                              You set up the query you are testing in the data viewer as a watch expression like this:

                              SQL.debug (
                              ExecuteSQL ( " //Your query goes here  )
                              )

                              Click evaluate and if you get the dreaded ? result, you click monitor, then re-open the watch expression for editing and and down where you first had a ? result you now see plain text explaining the SQL error. That plain text might just be "syntax error", but at least some of the time it's something more informative. Note that the CF is not generating the messages, it's just getting FileMaker to display text already returned by the query but not normally visible.

                              I have not tested this CF in FMP 14 yet...