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.
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.
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.
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?
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.
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?
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.
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?
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.
I will check these references out. Yes, I have have FM Advanced, what is the custom function you mentioned? Thanks again.
This is the most unusual custom function that I've seen, but it works:
//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
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
You set up the query you are testing in the data viewer as a watch expression like this:
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...