12 Replies Latest reply on Sep 17, 2013 12:07 AM by SimonSherwood_1

    Displaying totals from automated searches

    SimonSherwood_1

      Title

      Displaying totals from automated searches

      Post

           Hi I've been building a database with 2 main tables of customers and transactions.  All is working well so far, I have set up various layouts with buttons to navigate between them and have the 2 tables interacting nicely with portals and conditional formatting.  All in all I'm pleased so far.

           I need a helping hand though in creating either a layout page displaying search results or a reports page.  Basically I want to display totals from certain fields in the transactions table depending on the dates in the transaction table.

           Customers and Transactions are related by an ID field not that that really has a bearing on it.

           Each record in transaction will have ID Date Price Tips Extras Total Payment Balance, (Total  is a calculation of Price+Tips+Extras) (Balance is a Calculation of Total-Payment).

           Basically all I want to do is have a layout where I have a box displaying for example a SUM of all payments records on today's date, then another box with a SUM of all payment records last week, then the same for financial year.  Hopefully you get the idea.

           Your help is greatly appreciated, I'm starting to realize just how power full this app is, and you've all been a great help already.

            

           Simon.

        • 1. Re: Displaying totals from automated searches
          philmodjunk

               To bet your "totals in a box", you'll need some relationships that match to transactions by Date or a range of dates. That resulting set of related records can then be used to compute an aggregate value such as a sum or average and there are aggregate functions of that name.

               Sum ( RelatedTable::Field ) , for example, will total up Field for all the related records in RelatedTable. You can also refer to a summary field in the related table defined to compute the total of Field and it will also return this value. You can also get these results with a summary field placed inside a one row filtered portal and then you get the total of those related records that can pass through the filter. Finally, Execute SQL can use a SQL aggregate function and a relationship pluse WHERE clause to produce such a total if you have FIleMaker 12.

               A possible relationship that matches by year.

               ReportLayoutTable::gYear = Transactions::cYear

               where gYear is a global number field into which you enter the desired year an cYear is a calculation field that returns number: Year ( Date ). And note that the above expression could be a filter expression for a filtered portal also.

          • 2. Re: Displaying totals from automated searches
            SimonSherwood_1

                 Hi I've given the method of having a single row portal a go with some success, I have a Reports table with a date field set into it that is, this is related to the date field on the Transactions Table.  All my data (SUM for the relevant fields) for the requested day is displaying perfectly , I've even scripted the date to auto enter the current date in as that is what I will want most of the time.  All good so far.

                 I then added a Fiscal week field to the Transactions (source date is date)  and Reports tables (works from get currant date, plus a value list in another field with -1,-2 ect to display last weeks data and so on) this works ok.  I then created a relationship between the 2 and this is the question.

                 I want to display a second SUM Portal based on the addition Fiscal week relationship that I have created but the second Portal just mirrors the info in the first one.  Can I display the 2 portals on the same layout or do I need to make a separate table occurrence?

                 Another question, I can see the Fiscal week being a problem in the long run in that after a year I'll have duplicate fiscal weeks with separate dates, is there a standard work around such as a calculation field with the year as well, it would need to work with sorting records as well.

                  

                 Simon.

            • 3. Re: Displaying totals from automated searches
              philmodjunk

                   You can put as many portals as you need on the same layout, but to see different data in each, you will need either a different relationship and/or a different portal filter expression.

                   Yes, a calculation can be set up for fiscal weeks, but I can't tell you what to use for that calculation from the info you have shared at this point. I'd need a much more complete understanding of how you are using them here.

              • 4. Re: Displaying totals from automated searches
                SimonSherwood_1

                     Between the 2 tables I now have 2 relationships 1, Reports::date = Transactions::Date 2, Reports::FiscalWeek = Transactions::FiscalWeek. 

                     In the transactions table I also want to display a separate layout to display All transactions then subsummeries by Week, Month, Year, so this will need to be sorted correctly.  In order to do that I assume I'll need to create some kind of formula for fiscal weeks including the year but the problem I see is that when you add fiscal week to year the result might not always be as expected, eg  week 1 of Jan 2013 = 12013 or 20131 however last week of December 2011 = 522011 or 201152. The result won't sort properly according to date so I was wondering if there was a tried and tested work around such as a formula or if there was a even a function that would tweak the calculation so that it would sort properly.

                      

                     Simon.

                • 5. Re: Displaying totals from automated searches
                  philmodjunk

                       Between the 2 tables I now have 2 relationships 1, Reports::date = Transactions::Date 2, Reports::FiscalWeek = Transactions::FiscalWeek.

                       That's a bit confusing. Either you have:

                       Transactions>------Reports------<Transactions 2

                       Or you have just one table occurrence "box" for transactions and then only one actual relationship that matches fields like this:

                       Reports::Date = Transactions::Date 2 AND
                       Reports::FiscalWeek = Transactions::FiscalWeek.

                       One method for producing a sortable value for identifying a week is a date calculation that returns the date for the first day of the week for all dates of that week:

                       DateField - DayOfWeek(DateField ) + 1

                       A similar calculation can produce a common value for all dates of a given month:

                       DateField - Day ( DateFIeld ) + 1

                  • 6. Re: Displaying totals from automated searches
                    SimonSherwood_1

                         This is the relationship between the 2 tables, I only have one table occurrence of transactions.  I'm still having hassle getting the second Portal to display different data.

                    Reports::Date = Transactions::Date 2 AND
                    Reports::FiscalWeek = Transactions::FiscalWeek.

                    Thanks for telling me about the DateField DayOfWeek function, it's just what I need.  Saves taking the long walk around.

                          

                    Simon.

                    • 7. Re: Displaying totals from automated searches
                      SimonSherwood_1

                           Sorted it, I needed an extra table occurrence

                      • 8. Re: Displaying totals from automated searches
                        SimonSherwood_1

                        Hi 

                        I wonder if you can help a bit more, I've used this formula in a Field in my transactions table.

                        DateField - DayOfWeek(DateField ) + 1

                        I've related it to this in a reports table in a field called week search

                             (Get(CurrentDate) - DayOfWeek(Get(CurrentDate)) + 2)+(Week Choice*7)

                             Week Choice is a value list field so that I can search transactions from past weeks.

                             My question is that all the transactions for a week are returned with a date for a Monday, however the transactions returned are actually Sunday through Saturday.  Is it possible to tweak the calculation and relationship so the filemaker looks at a week as running from Monday to Saturday and returns the date for Monday as well?

                             Simon.

                        • 9. Re: Displaying totals from automated searches
                          philmodjunk

                               If it runs Monday through Saturday, what should happen with dates that fall on Sunday? (Even unlikely or incorrect data entry needs to be handled in ways that doesn't cause an issue.

                               The calc can certainly be adjusted to return the date for Monday instead of the date for Sunday of that Week. I'm not sure that I understand how changing things to run from "Monday to Saturday" will change your results.

                          • 10. Re: Displaying totals from automated searches
                            SimonSherwood_1

                                 Hi Phil

                                 It's not that data is rarely entered in on a Sunday, it's just that the Boss (my wife) views Monday as the start of the week. So at the moment all records have a DateField and a WeekField, the WeekField has this formula

                                 (Date) - DayOfWeek(Date ) + 2

                                 So any record with a date between Sunday - Saturday will always return Mondays date, this is pretty good as it allows me to view transaction weekly totals, however I'd rather the transaction be grouped from  Monday to Sunday and return Mondays date.  It might sound petty, but we are speaking about the wife, and in her words, "if this program is as good as I say it is, it should know the difference between a Sunday and a Monday." 

                                 All records will still be accounted for, it's just that the Sunday will fall in the week before.

                                 I wondered if something like this would work, but am having little success.

                                 (Date+1) - DayOfWeek(Date+1 ) + 2

                                  

                                 Simon.

                            • 11. Re: Displaying totals from automated searches
                              philmodjunk

                                   Thanks for clearing that up. Your preceding post stated:

                                   

                                        Is it possible to tweak the calculation and relationship so the filemaker looks at a week as running from Monday to Saturday

                                   That completely omitted Sunday from the week and I needed to know what to do with a Sunday date should it occur. But what you actually want is the dates from Monday to Sunday, not Monday to Saturday.

                                   Datefield - DayofWeek ( DateField ) + 2 - ( DayOfWeek ( DateField ) = 1 ) * 7

                                   should do the trick.

                                   Notes: Dates in FileMaker are stored as the number of days from the date shown in the field to 12/31/0000. You can add and subtract date fields as though they are number fields and your result will be either in days or a date.

                                   DayOfWeek ( DateField ) = 1 is a boolean expression. It returns a 1 (True) if the day of week is a Sunday and 0 (False) if it is not. Thus the value of 7 (1 week) is subtracted only if the day of week is a Sunday as a way to include Sunday dates as part of the preceding week that now starts on Monday.

                              • 12. Re: Displaying totals from automated searches
                                SimonSherwood_1

                                     Thanks, it seems to be doing the job, sorry for the confusion, must have been a typo.

                                      

                                     Simon.