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.
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.
~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 )
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.
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.
Is this kind of thing doable and if so how should it be executed?
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 ) ;
"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).
that color will cost you a bit in terms of performance.
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)
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".
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....