What match fields do you currently have linking dash_day_of_Week to dash_Invoice||id_day_of_week? I can see that you have more than one but not what they are.
One option is to replace the calculation field in dash_day_of_Week with a summary field in the far right table. You can then specify a portal filter in Dash that specifies a particular location. You could set up different portals for each location where the only difference is teh location specified in the filter. (This produces "display only" sub totals. You can't use them in other script steps or calculations.)
Other options are also possible--including the use of ExecuteSQL if you are using FMP 12 or later.
The match fields for the relationship between dash_DAY_OF_WEEK and dash_Invoice are:
id_day_of_week = ID_DAY_OF_WEEK
Date_Start <= Date_Invoice
Date_End >= Date_Invoice
I put the Totals summary fields from the dash_INVOICE on the dashboard portal, the portal is based on dash_DAYS_OF_WEEK, it shows the correct totals by day of the week from the dash_INVOICE occurrence.
I tried to filter the portal by Project_Name (location) but I didn't get a change in Totals sum, when I used one particular Project Name and got nothing when I tried two other Project Names.
The filter expression in the portal
I have not changed any of the relationships from the original.
Just to be clear this is what I want on the portal.
Do I need to set up the relationships differently?
Is dash_INVOICE||id_day_of_week::Project_Name="Gallery 2015" the correct way to filter the portal?
Thanks for the help.
Finally got the portal to work, going back to problem periodically while doing other stuff, looks like I got a field mixed up, names too similar.
Ran into another problem moving forward, I am summing the daily amounts in the INVOICE_SELECT_LOCATION TO by location and as mentioned get the correct results for a location fine on the portal.
I need to chart the portal data by days of the week, I am using Google charts for charting because the native FM charting doesn't work in runtime. I have set up 7 unstored calculation fields in the Dash TO to store the sums from the portal using GetTheNthRecord function, I then use the 7 unstored calculation fields that have the 7 daily sums in them in the Google charts html code. The GetTheNthRecord function does not retrieve the correct data, each of the 7 DOW_Location fields has the total for all sales for the selected location.
I used the same approach on another set of similar TO's and the GetTheNthRecord function worked fine. However in that case the summing was done in the middle TO to sum all locations by days of the week.
Unstored Calculation, the sum_subtotal_price is the same field as on portal.
GetNthRecord (dash_INVOICE_SELECT_LOCATION||id_DOW|::sum_subtotal_price;2 )
Relationships between dash_DOW_SELECT_LOCATION and dash_INVOICE_SELECT_LOCATION
ID_Day_Of_Week = id_day_of_week
DateStart <= DateInvoice
DateEnd >= DateInvoice
Filter_ID_ProjectLocation = id_project