2 Replies Latest reply on Jun 17, 2015 5:38 PM by MichaelB_1

    Portal: Sales summary per user, per day



      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.

        • 1. Re: Portal: Sales summary per user, per day

          If I decoded that written description, you have or need these relationships:


          This detail:

          (It is possible for one invoice to include items sold by different salespeople.)

          Is both a design complication and a business management headache (but you aren't the manager probably so it's not likely to be your headache. wink)

          If you set up the match fields between Employee_location_Days and Invoice Line Items like this:

          Employee_Location_Days::_fkEmployeeID = Invoice_Line_Items::_fkEmployeeID AND
          Employee_Location_Days::Date = Invoice_Line_Items::SaleDate

          An aggregate function in Employee_Locatoin_Days or a summary field in Invoice Line_Items can show your sub totals.

          SaleDate can either auto-enter a date from Invoices or the creation date to autopopulate this field.

          An alternative method that can be used with versions 12 or newer is to use ExecuteSQL to compute such aggregate values.

          • 2. Re: Portal: Sales summary per user, per day

            Phil, thanks.  That was very helpful.  I set up the relations as you suggested, and used the Sum function in a calculated field in Employee_Location_Days.  Works great.

            In my 4D days, it was preferable to use automatic relations only in a very limited fashion, and to accomplish the more complex things programatically.  In Filemaker, the relations are much more central to the operation of the database, so it's taking me a while to understand and embrace the utility of compound keys, table occurrences, etc.