Portal: Sales summary per user, per day
Hi, I'm a former 4D programmer who is getting better at Filemaker, but for this one I think I could use some help.
I need to display a Location_Days record which represents information pertaining to a day of sales at a single retail location. (We consider this our "daily report.") It includes fields for noting the weather that day, summaries of payments received, etc. Within that layout, I also need to display a portal that lists one row per employee, with an aggregate total of that employee's sales for the day at that location. This row will also be used to enter that employee's hours for the day. I have set up a table called Employee_Location_Days for this.
There is an Invoices table which can have either a Location_ID or a Location_Day_ID foreign key, as needed. And there is an Invoice_Line_Items table that has the Salesperson_ID. (It is possible for one invoice to include items sold by different salespeople.)
Ideally, the aggregate dollar amount representing the employee's sales for the day at the location would be displayed on the fly in the portal. (This is how I would do it in 4D.) But in Filemaker, I suspect that I'll need to pre-populate some records and programatically adjust them whenever there is a sale…?
Incidentally, for the rows in the portal (Employee_Location_Days), I will need a row for any employee who had sales that day, at that location, OR any employees who worked any hours, even if they did not sell anything. So I guess that those rows will be a combination of automatically generated and manually added records.
If you have suggestions on how to accomplish this, they would be appreciated! In particular, if there is a way to generate the aggregate sales amount for each row at the time of display, that would be great.