8 Replies Latest reply on Jun 7, 2016 5:05 AM by williamrollo

    "Create a conditional format based on created records count"

    williamrollo

      Quite  a tricky one and not entirely sure how do work it out.
      I want to have a different colour layout depending on whether or not there have been more sales in the current month than the same month in the previous year. Eg. Green background in my Invoicing layout if there were more invoices created this June compared to last year in June. I have created count fields as I have report layouts with graphs to show the total sales per month/year.

      I Also want to do the same for number of customers who enquire with us . Is this kind of thing doable and if so how should it be executed?

      Thank you

        • 1. Re: "Create a conditional format based on created records count"
          siplus

          Actually I would think that the total invoice AMOUNT, not the number of invoices, should count.

           

          You can compare 2 executeSQL's and decide the color based upon that, but that color will cost you a bit in terms of performance.

          • 2. Re: "Create a conditional format based on created records count"
            electon

            It's definitely doable.

            You can do it by creating extra relationships via global fields with which you set up start end end date filters.

            There's also ExecuteSQL that can do this without these.

             

            Layout parts have no conditional formatting but you can use a text object as overlay and set it up there.

             

            eSQL example:

             

            Let ([

              ~curDate = Get ( CurrentDate ) ;

              ~month = Month ( ~curDate ) ;

              ~year = Year ( ~curDate ) - 1 ; // previous year

              ~start = Date ( ~month ; 1 ; ~year ) ;  // first day of the month

              ~end = Date ( ~month + 1 ; 0 ; ~year ) ; // last day of the month

              ~sql = "SELECT COUNT (*) FROM Invoice WHERE invoicedate BETWEEN ? AND ? " ;

              ~count = ExecuteSQL ( ~sql ; "" ; "" ; ~start ; ~end )

              ];

             

              ~count

            )

             

            The example is put in a Let statement and split into separate parts to make it human readable.

            Will return the record count for last year's same month.

            • 3. Re: "Create a conditional format based on created records count"
              MaxEh

              You could use a text box that has just a single space in it. Set the default fill to empty (or no fill) resize it to fit your layout and add your conditions. Place it as the lowest layer (move object to the back). Set the box to be green when your  condition is true. Since all other fields would be on top of the background it will be seen as changing colour. Any field  fill will also display normally. There might be some screen flicker.

              • 4. Re: "Create a conditional format based on created records count"
                erolst

                williamrollo wrote:

                 

                Is this kind of thing doable and if so how should it be executed?

                Thank you

                Sure it's doable – this is FileMaker, after all. ;-)

                 

                Simplest solution (assuming you need this based on the current invoice):

                 

                Let ( [

                   d = Invoice::dateInvoice ;

                   m = Month ( d ) ;

                   y = Year ( d ) ;

                 

                  sql =

                    "SELECT COUNT (*) FROM Invoice

                    WHERE Year ( dateInvoice ) = ? AND Month ( dateInvoice ) = ? " ;

                 

                lastValue = ExecuteSQL ( sql ; "" ; "" ; y - 1 ; m ) ;

                thisValue = ExecuteSQL ( sql ; "" ; "" ; y ; m )

                  ] ;

                thisValue > lastValue

                )

                 

                and use that in your Conditional Formatting calculation: if True, make some background object turn green (with envy … )

                 

                Unfortunately, “simplest” doesn't necessarily mean “best”; ExecuteSQL can be a performance hog if you have open records in the target table.

                 

                Another, more native idea would be to create

                one calc field as Year ( invoiceDate ) * 12 + Month ( dateInvoice )

                another one as ( Year ( invoiceDate ) - 1 ) * 12 + Month ( dateInvoice )

                 

                and create two relationships where you a) match the first against the second (this month one year ago), and b) the second against itself (this month), then use Count () with those relationships and compare.

                 

                (Naturally, it's the same approach as in the SQL code, except that you cannot use ad-hoc functions in relationship predicates, but need helper fields.)

                 

                Note that, it would be a good idea to use pre-summarized values, i.e. create a table with just a month and a year number and a count value that you maintain via script whenever you set or change an invoice date or delete an invoice record. Or, if that is too much of a bother, update the values frequently (every night / every week … you decide).

                • 5. Re: "Create a conditional format based on created records count"
                  erolst

                  siplus wrote:

                  that color will cost you a bit in terms of performance.

                  TANSTAAFL

                  • 6. Re: "Create a conditional format based on created records count"
                    siplus

                    there's one thing that might cut the penalty in half: while the current month's invoices (and their sum) changes, the corresponding ones from last year don't. Having a reference table in which you create a record with the needed totals of the just elapsed month on every 1st of a month can help.

                     

                    (I still think that 10  x $10 invoices are less meaningful than a single $200 one, but that's just my swiss handicap)

                    • 7. Re: "Create a conditional format based on created records count"
                      dtcgnet

                      You're getting great advice on ways to accomplish the ACTUALITY of how to do what you want to do.

                       

                      Though you CAN do what you've asked to do, I'm not at all sure that you SHOULD do what you've asked to do.

                       

                      On June 1 of this year, you will almost certainly have billed less than you did in June of last year. 1 day so far in June this year versus all 30 days of June last year? That's not a fair comparison. It wouldn't be fair for June 1, or June 2, or June 3 or a lot more days after that.

                       

                      I could see you creating an "Amount left to charge before equalling June of Last Year" or "Percentage of Last June's Billings" or any of a lot of other options. Comparing Month-End counts to Month-Beginning or Month-in-Progress counts is too apples to oranges. Also, siplus makes a good point about using just a Count versus total amounts.

                       

                      If the goal of this is to motivate employees but you end up slowing down the performance of the database they rely on, maybe the best thing you can do as a developer is to say "this is why we shouldn't do what you've requested".

                      • 8. Re: "Create a conditional format based on created records count"
                        williamrollo

                        Thank you all for your helpful advice on this matter - I understand the point that total value is more important  than invoice count, in most cases - however our products are normally all the same value and only one is ordered by any client (on average unless we are lucky). It just  gives us an indicator on how we are doing compared to the previous year. I think  that you are  correct that the processing power involved when any user selects the relevant layout may be too prohibitive and so I shall leave this feature for now and  keep it in my reports layouts as a graph....