13 Replies Latest reply on Dec 5, 2015 2:29 AM by macwombat

    CREATING MONTHLY SUMMARY

    pinerman

      Hello Everyone, I hope you´re well.

       

      I am trying to make a layout where I can instantly choose a month of the year and it will display all the data from some tables using Portals from that month. I have different tables in different files, I want to display the info through portals in a layout. I am having difficulty on the month field that it is going to be a dropdown menú of the months of the year, so when a month is selected al the data appears in the portals of each table.

       

      Example.

       

      I have a table with Sales, a table with expenses, a table with consumptions of raw materials, etc...

       

      In a layout I will be inserting portals of all these tables, I will configure each portal to show certain data. I want a month field where I can choose the month of the year and in the portals will appear everything from that month. How do I setup this month field, I have tried and what it does is that it shows me a specific day of the month so in the portals only shows the data of that specific day of the month.

       

      I would really appreciate your help.

       

      Thank you.

       

      Pinerman

        • 1. Re: CREATING MONTHLY SUMMARY
          macwombat

          Assume that each of your tables has a field for the date of the sale, date of the expense etc.

           

          1.  Create a stored calc field in each table for "MonthYear" with the calc as follows:

          MonthName ( dateofthesale ) & Year ( dateofthesale ).  The calc field should be indexed.

           

          2.  Create a variable field (eg. zv_monthyear_gt) in the table that the layout is based on.  Place this field on the layout in the header.  Set this to populate from a dropdown list.  You can populate the valuelist with manual values "January 2015", "February 2015".  You only need to update this value list once a year with the month year values.  Users can then select the Month Year from the dropdown.

           

          3.  In your relationships that the portals are going to be based on add a predicate for zv_monthyear_gt = "MonthYear".

           

          Once a user selects the Month Year from the dropdown it will filter the portals based on that.

          • 2. Re: CREATING MONTHLY SUMMARY
            dtcgnet

            You're using a type "Date" for the field in your relationship. As a result...things default to just one specific day. Use a Number field instead. You need to relate the number of the month from one table to the number of the month from another. 10 = 10 (October = October).

             

            After that, it depends on if you're always looking at ONE field, or you have a lot of different date fields upon which you might want to base this.

            • 3. Re: CREATING MONTHLY SUMMARY
              macwombat

              Clarification ... make sure the calc field in the target table exactly matches the variable field value list values in the originating table - re: spaces etc.

               

              ie.

              either have your calc like this:  MonthName ( dateofthesale ) & " " & Year ( dateofthesale ) and your value list with a space between the month and year

               

              or have your calc like this:  MonthName ( dateofthesale ) & Year ( dateofthesale ) with your value list with no space between the month and year

              • 4. Re: CREATING MONTHLY SUMMARY
                dtcgnet

                My favorite way of sorting for these sorts of things involves concatenating fields into text strings in a new field.

                 

                Year first.

                Month number second, always as a two-digit value which is created as text.

                 

                Then you have: 201601, 201603, 201501, 201511, etc.

                 

                Sort by that field, ascending:

                201501

                201511

                201601

                201603

                 

                Sort by that field, descending:

                201603

                201601

                201511

                201501

                 

                Select Fiscal year 2016, which might be July 1, 2015 to June 30, 2016:

                201511

                201601

                201603

                (201501 would be from fiscal year 2015)

                 

                Think all the way through what you want. A great presenter at DevCon said, "Make them write it on a napkin".

                • 5. Re: CREATING MONTHLY SUMMARY
                  pinerman

                  Thank you guys for your input, @Chris, what do you mean by a "variable field"? how do you specify it to be variable?

                   

                  Thanks.

                  • 6. Re: CREATING MONTHLY SUMMARY
                    macwombat

                    Hi Pinerman

                     

                    My bad - describing it as a variable field probably isn't the most helpful way of describing it, as variables are a different element used in FileMaker generally in scripting.  By variable I meant that it is a field that users input the month/year that they are searching for.

                     

                    In Manage Database create the field as a text field, with global storage.  This field will be placed in the header of your layout.  It will be for users to select the Month & Year they are searching for.

                     

                    I've attached a simple sample file that shows the method.

                    • 7. Re: CREATING MONTHLY SUMMARY
                      pinerman

                      Chris, thank you for help, unfortunately I couldnt open the test file because I am running an earlier versión of filemaker, which is versión 11 advanced. Anyway I think I understand everything, so this is what I have done. I created a calcultaion field called MonthYEAR in my expenses table just as you suggested, so on every record now I have my Monthyear calcultaion which I suppose needs to be a text field. On the main layout I created the global field MonthYear with the value list so that it matches exactly the calcultaion field in the expenses table. I put a portal of the expenses table in my main layout, the relationship was MonthYear with MonthYear, but it doesnt seem to show any records in the portal...  I don´t know if my relationship is the coorect one or not.

                       

                      Any thoughts?

                      • 8. Re: CREATING MONTHLY SUMMARY
                        pinerman

                        It is working now Chris, thanks for all your input, I really appreciate it.

                         

                        Cheers,

                         

                        Pinerman

                        • 9. Re: CREATING MONTHLY SUMMARY
                          pinerman

                          Hello Chris, sorry for the inconvinience, so everything Works perfect thanks tou your advice, I am just having problems displaying the data from the total invoices and I think it is because it{s a calculation field. I have my invoices Table and I have an invoice ítems portal where I add the ítems for the invoice. I have a subtotal field in the invoice table that is a summary of the subtotals of the ítems table, I have a Tax field that is a calculation field of the total tax of the subtotal field in the invoice table, and I have a grand total field in the invoice table that is a calculation field of the tax field plus the subtotal field... I want to display the sum of the grand totals of the month, so I made a summary field of the grandtotals and it Works but it won´t show in the portal of the montly report layout.. I am guessing that it is because it is unstored...

                           

                          What do you think? Thanks in advance.

                           

                          Pinerman

                          • 10. Re: CREATING MONTHLY SUMMARY
                            macwombat

                            Hi Pinerman

                            I'm at a client today and can't access FM11.  I'm also in Australia so might be a different timezone to you.  I'll mock up a sample file in FM11 at home tonight (my time) for you.

                            Chris

                            • 11. Re: CREATING MONTHLY SUMMARY
                              macwombat

                              Hi Pinerman

                              You will need a table occurrence of your InvoiceItems that is linked to your Invoices table occurrence via the invoices ID primary key field.  In the Invoices table create a calculation field with this calc:  Sum ( InvoiceItems::ItemCost ).  Make sure that the calculation is set to return a number value.  Use the same method for calculating the invoice tax.  These fields can then be displayed in your portal.  Then in the Dashboard/Menu table create similar calculation fields with this calc: Sum (InvoiceTotalCost) and Sum (InvoicesTotalTax).  The invoice totals are in the portal.  The total across multiple invoices is outside the portal.

                               

                              Attached is a sample file showing this setup (in FMP11).

                               

                              Chris

                              • 12. Re: CREATING MONTHLY SUMMARY
                                pinerman

                                Thanks very much for everything Chris.

                                 

                                Cheers.

                                • 13. Re: CREATING MONTHLY SUMMARY
                                  macwombat

                                  Pleasure to help.