SeedCode Support Forum

I am looking for a way to create a complete status layout that contains basic summaries and totals for the following:

Total Unpaid invoices (YTD)
Total Paid invoices (YTD)
Total invoices amount (YTD)
Total number of invoices
Number of Events (YTD)
Number of Progress Reports (YTD)
Number of active clients
Number of inactive clients
Total number of clients
Total number of Projects

The idea is to put these results on the main startup layout (Home). I have tried pulling the data from the Rows and Globals table but that didn't work.

Any help would be appreciated.

Thanks. Ken

Good question, Ken. You have a few options...

Using Rows won't work directly as those relationships are predicated upon you setting the IDs of all the records you're interested in into the globals in the Selector. In your case the would mean the IDs of all the unpaid invoices YTD--you'd have to find them and then get all their IDs...but if you've gone to the trouble of finding them then using Selector gets kind of redundant. =) The Selector isn't the most efficient way to do this kind of reporting. I can suggest two approaches:

SQL

At first glance, this is what SQL was made for: you want to aggregate data from a bunch of tables (invoices, clients, projects) from a context that isn't really bound to any one of them: the dashboard or Home layout. SQL lets you make these kind of queries out of context (regardless of what layout you're on) AND includes functions to sum or count the results in one step. So you could write single line of SQL that would get you the sum of the unpaid invoices YTD and could even put that *right in* an unstored calc in the Home table. (If you need help writing SQL calcs like this I strongly recommend SQLexplorer; makes this kind of thing MUCH easier: http://www.seedcode.com/filemaker-sql-explorer/ )

But...

Since you want to write over ten of these aggregate functions, you're talking about asking FileMaker to do a lot of work. And if these are just unstirred calcs in Home then you'll do that work *every* time you arrive at the Home layout. That brings up the first suggestion I'd make: however you end up calculating these stats, have a script set them into *global fields* on demand instead of using live fields. This way you can set them when the user first opens the solution (if you want) and the values will just hang out in those globals as the user returns to home over and over, never asking your solution to do any more heavy lifting until the user runs your script to refresh the dashboard. That may not sound as fun as "live" calcs, but this is how you keep this thing from ruining the performance of your solution. =)

The second suggestion I'd make is not to use SQL unless you're using FileMaker Sever. The reason is that each of these SQL calls will need to pull the whole index of each relevant table from the server to the client in order to complete the query. As you get more invoices, clients, and projects your users will really start to feel that. If you have server you can run these queries in a script using Perform Script on Server (PSOS) and then only bring the *results* back to the client across the wire. (For a great example of how PSOS can speed up this kind of thing, check this out: http://www.seedcode.com/chasing-filemak ... rformance/ )

Perhaps a better idea is not to use SQL at all...

Relationships

The other approach is to build dedicated relationships for these dashboard queries. That means you'd have a table occurrence hanging off Home called "DashboardInvoicesPaid" and this relationship would only show Paid invoices for the user selected Date Range (once you're going to the trouble of this, why not make your date ranges global fields in home so you can report these values YTD, or LastYearTD, or any other range you need?) Then you'd have a script that set your global dashboard value to the sum of those invoices, something like Sum ( DashboardInvoicesPaid::TotalGrand ), and other line in the same script would set the total number of those invoices in your dashboard to Count ( DashboardInvoicesPaid::id )

This is the same idea as I mentioned above, where you don't show live calcs but use a script to refresh the dashboard as needed.

Aside from being "simpler" (unless you're a SQL wiz), this relationship approach has one other big advantage over SQL: once you have a relationship to Paid Invoices you can use the GoToRelatedRecord script step to go see those Paid Invoices YTD. This makes it version easy to but a view button on your dashboard to jump to a found set of relevant invoices. That would be a whole separate scripts if you were doing SQL reporting AND since the script wouldn't use exactly the same code as the SQL query to find the records, you could accidentally use slightly different criteria in your aggregates as in your finds: that gets confusing very quickly.

So while relationships aren't as - as doing this with SQL, I think you get more bang for your buck. And you can still run your "refresh dashboard" script using PSOS if you want to.

Let me know if making a relationship like I described for DashboardInvoicesPaid is new to you and I'll post an example.

- John

Thanks for the detailed info John. Good information either way whether SQL or native scripts.

I agree that using native FM scripts is a better option. My idea for a dashboard is to have useful data that is fairly current. It doesn't need to be live data at all. Providing a refresh button is enough.

I am knowledgeable enough about FM scripting to understand what needs to be done but having an example would make it much easier. The relationships are a bit tricky using the selector model.

Would you please give a brief sample of how this can be done? I can expand your example to other data points and do the calculations as needed.

Again, thanks for your help with this. I will send you screen shots of the finished dashboard when I figure it all out. Should be pretty cool.

Ken

Sure thing, Ken. And I'd love to see some screenshots of yours when it's all done.

In this example we'll just do unpaid invoices. To get started I created new a new table occurrence of invoices in the SeedCode Complete file called "Dashboard_PaidInvoicesPerDateRange".



I then created a few new fields global in Home to use for the date ranges. It turns out I just needed "Dashboard_DataRangeStartGlob" and "Dashboard_DataRangeEndGlob". I'd set these on start up with the dates I'm interested in--say the last 30 days--but you could make a nice interface to let folks set all sorts or ranges into here including YearToDate and LastYearToDate. I also created two new global fields in Home to store the values: Dashboard_UnpaidInvoiceCount and Dashboard_UnpaidInvoiceAmount

I then wired up our new table occurrence to Home like this:



Once that's done--and you have values in your start and end date range globals, you can create a script that sets these two values like this. I'd set the values as part of start up as well or you could wait until someone clicks your "refresh" button on your dashboard:

Code: Select all

SetField [  Dashboard_UnpaidInvoiceCount ; Max( 0 ; Count ( Dashboard_PaidInvoicesPerDateRange::id ) ) ]
SetField [  Dashboard_UnpaidInvoiceAmount ; Max( 0 ; Sum ( Dashboard_PaidInvoicesPerDateRange::TotalBalance ) ) ]

That's it =)

Wow! You are the best John. Wish I could tap into your knowledge 24/7.

I will use this example to build a great dashboard. Should have some results to show soon.

Many thanks as always.

Ken

=) Looking forward to seeing them Ken!

Hi John.

I followed your example faithfully I think. But my fresh script returns zero in the fields. What did I do wrong? Here's what I have:

I added 4 global fields to the Home table. Dashboard_PaidInvoicesPerDateRange is tied to Invoices from the model file.



This is the relationship layout.



I added a calc field to Dashboard_DataRangeStartGlob and Dashboard_DataRangeEndGlob with start and end date. Here's my calc: Date ( 1 ; 1 ; 2015 ) Date ( 12 ; 31 ; 2015 ) one per glob field.

I then added Dashboard_UnpaidInvoiceAmount and Dashboard_UnpaidInvoiceCount as fields on the Home layout. The refresh script returns zero when I run it.

I feel that I am so close. Please help.

Thanks. Ken

Those date range globals need to be of the type "date", not text. (Thanks for the screenshot!)

Going further, I think you'll have better luck with them if they are either calculation fields set to global storage (and returning the type "date") or global date fields that you set with your own values on startup: global auto-enter calcs can be hard to reason about and refresh.

I also think the amount and count globals you added for your result might be better as number so you can more easily format at least the one of them as currency.

Aside from that, it's gonna be cool!

Thanks for the quick reply.

Still no joy. Keeps returning zero for both count and sum fields. What am I doing wrong? Everything makes sense but doesn't work out that way.

Here's what I have. Can you spot a problem?

Refresh script tied to button for testing:



Here's the date range layout:



Relationship between Home and Invoice table from Model:

Hmmmm. Well it could be that you have un paid invoice records from 2015 (your relationship name says "paid" but your relationship where "constant ≠ paid" is looking for unpaid).

It could also be that those two date calcs are still not returning the type "date". The way I troubleshoot this stuff is to create a portal from Dashboard_PaidInvoicesPerDateRange on the home layout and see if invoices show up in there. Then you can tweak the relationship until then *right* records show up in there.

Good idea John. I will setup a portal and begin troubleshooting.

Almost there.

Thanks.

Ken

Got it!

My start date range global was equal or less than but should have been equal or greater than. Same for end date range global.

Your idea of putting a portal on the layout was ingenious. I will always test my ideas this way. A very useful method.

I will now go on to build my dashboard.

Thanks so much for your help with this.

Ken

My first Dashboard item. Only 9 or 10 more to go.

Many, many thanks John for helping me get this going. I learned a great deal from this experience. Mostly confidence that I can navigate this solution and pull out the data as needed.

Only a couple of issues left to resolve. How do I calculate YTD current year and YTD last year?

Will show off the finished dashboard soon.

Take care, Ken

Looking good! Can't wait to see the finished dashboard; I'd love to post this to our blog so other folks can see what you've done here. That work for you?

Oh, so YTD for last year is done by changing the values in those two global date range fields. I know you made yours as calcs but this is one reason they're better as global date fields: your script can set them to the current YTD and update all the YTD values, then it can set them to Last YTD and update last year's values.

Just be careful if you're also using these globals for a GoToRelatedRecord to jump to the YTD or LYTD invoices: you'll want your GTRR script to set the date range again before using the relationship as you won't be sure if you left it on YTD or LYTD.

And since they are globals you can make a whole bunch of buttons to set them to any ranges you want to compare: this month vs last month, this qtr vs last, etc.

=)

Thanks for your help and kind words John.

I would be thrilled for you guys to showcase my modifications to your solution.

I just sent you a small media kit that has some of the key images. Use them as you wish.

Thanks again.

Ken