3 Replies Latest reply on Oct 4, 2015 12:31 PM by erolst

    Create report for specific months

    lcottingham

      Hi - I am relatively new to file maker and this is my first database from scratch.

       

      I have worked with the invoices starter solution quite a bit but I now require a small, but bespoke database creating to fit my need. I have worked with MO Access in the past but I know FMP is not the same.

       

      In short, the purpose is the following:

      To create a record of various sales, categorise them by sale type, sale value and then calculate the commissions due on each sale (20% of the sale). I then need to be able to create a printable monthly report adopting an invoice style showing a list of all the sales that month and the grand totals of the net sales and total commission. Easy right!

       

      Currently I have the following:

      • 1 Table called 'Sales' with the fields of
        • Sale ID (unique)
        • Date
        • Customer
        • Invoice ref
        • Amount Received
        • Fulfilled (a boolean checkbox about if the sale was fulfilled and paid for e.g. fulfilled)
        • Commission (calculation = if (fulfilled = 1 [yes] (amount received * 0.2) ; 0 )
      • 2 Layouts for the 'Sales' table
        • 1 List view of the records
        • 1 edit / create new view

       

      That is as far as I have got. Would someone be able to assist me or walk me through my next steps in order to create a printable report. I am not sure if a portal is my correct path to read into here? Any assistance would be great.

       

      What I would like

      I would like if possible be able to select a month and year, perhaps in a dropdown list or in a popup box prompt, and for it to run a script to generate a printable report outlining the selected month/year's sales in a list with the two totals calculated.

       

      I am using FMP 14

        • 1. Re: Create report for specific months
          erolst

          Let me answer your question and drop in a few comments:


          lcottingham wrote:

          Currently I have the following:

          1 Table called 'Sales' with the fields of

          Sale ID (unique)

          Date

          Customer

          Invoice ref

          You should have at least two tables, since a Customer is not a Sale, and vice versa. Each is an entity of its own and should have its own table.

           

          If you're selling whatever it is you're selling in different quantities and/or combinations, you also should have a LineItems table (and, most likely, another one to define your Products/Services and their prices). But since this is not (really) pertinent to your question, let's not go into that ATM.

           

          I am not sure if a portal is my correct path to read into here?

           

          It's not; while a portal is a great UI device for adding/editing related records/data, for a report you want something more flexible in terms of page design and flow: a (dedicated) layout in List view.

           

          lcottingham wrote:

          I would like if possible be able to select a month and year, perhaps in a dropdown list or in a popup box prompt, and for it to run a script to generate a printable report outlining the selected month/year's

           

          Yes – by writing a script that uses your input as search criteria. If you add two global number fields for year and month selection, the basic script could look like:

           

          # [ add error trapping here, i.e. check globals on plausibility ]

          # [ e.g. month could be empty, but year must be specified ]

          Set Error Capture [ on ]

          Enter Find Mode

          Go to Layout [ Report ( Sales ) ]

          Set Field [ Sales::theDate ; Let ( [ m = Sales::gSearchMonth ; y = Sales::gSearchYear ] ; Case ( isEmpty ( m ; y ; Date ( m ; 1 ; y ) & ".." & Date ( m + 1 ; 0 ; y ) ) ]

          ]

          Perform Find

          If [ not Get ( FoundCount ) ]

            Go to Layout [ original ]

            Show Custom Dialog [ "No matching records " etc. ]

            Exit Script

          End If

          Sort

          # [ other stuff ]

           

          An alternative is o open a new window to do all this in; that allows you a quick look without altering the found set in your “main” window, and you could even leave it open side by side, or have multiple windows open for multiple reports.

           

          As for your totals: look at summary fields. You'll probably want to define two summary fields as TotalOf: AmountReceived, and TotalOf: Commission. A summary field aggregates the found set, so these two fields will automatically display the correct results for the records in your report.

          1 of 1 people found this helpful
          • 2. Re: Create report for specific months
            lcottingham

            First of all, thank you for taking your time to reply to my question. I really appreciate all the help and guidance so I can properly complete the task and ultimately learn more of this program.

            erolst wrote:

             

            You should have at least two tables, since a Customer is not a Sale, and vice versa. Each is an entity of its own and should have its own table.

             

            If you're selling whatever it is you're selling in different quantities and/or combinations, you also should have a LineItems table (and, most likely, another one to define your Products/Services and their prices). But since this is not (really) pertinent to your question, let's not go into that ATM.

            In response to this, this is very valid. However in this case the customer is very generic and I require nothing more than a name as a reference. I have other systems (like the ecommerce system or accounting system) in place to keep all these records. I am simply making this basic system for ease of use - to bring together basic info from 3 different sales sources and generate a commissions report for each month.

             

            Once I learn, I may expand the 'invoices' starter solution I have modified to be able to have this functionality.

             

            So bearing the above in mind.. what would you suggest my next steps should be??

            • 3. Re: Create report for specific months
              erolst

              lcottingham wrote:

              So bearing the above in mind.. what would you suggest my next steps should be??

               

              Not sure what of the above I should bear in mind, since it simply explains why the data model described in your initial post is what it is.

               

              What you wanted were suggestions on how to create a report; now that you received them, I'd think your next step should be to try and implement them.

               

              Feel free to ask any follow-up questions that may (will? ) come up.