Reliable Softworks

Tweet
Before the glory days of script triggers and transactional editing, us old-timers used to have to deal with something truly ugly: calc fields which included related records. You young whippersnappers won’t know what I’m talking about, but there was a time when the total on an invoice, for example, was sometimes a LIVE CALCULATION which SUMMED OTHER LIVE CALCULATIONS!!

More than a little horrifying, I know. There were some advantages, though. For example, you never had to worry about how many different layouts contained records which went into that calculation – if a user changed data anywhere, the calc would just update when needed…you didn’t have to make sure the “update” script was tied to every possible layout or any new layout the user might create.

The obvious disadvantage of this, though, was reporting. Most of the suits who sign your check on the front want to see things like “KPIs” and “Dashboards” and other fancy reports with names invented in the 80′s, and those kinds of things depend on totals of totals of totals. Totals of BIG groups of records: year over year comparisons, for example, or 10-year trends. This could involve thousands – sometimes hundreds of thousands or even millions – of records. Think about that for a second: it’s really not much work for a computer to add up thousands of numbers. But it gets a little hairy when it has to combine the results of thousands of little (or not so little) math problems. If you’re talking about a big system, better plan on going to a movie…maybe a movie marathon or two.

Enter the “robot” report. This when you have a robot system (or, these days, the server – you youngsters don’t know how good you have it!) run reports in the background, or maybe overnight, where all those math problems get done at once and assembled into a “reporting” table or pre-made versions of the reports themselves. This way, when Mrs. Boss comes in and needs to see her numbers, they can be whipped right up – fresh figures, no waiting!

Well – maybe not so fresh. If it’s an overnight report, and we’re dealing with a fast-paced business, overnight might not be quite good enough.

So what do you do? Us lazy old folk, who haven’t found the gumption to rewrite our 300-table/6000-field/500-layout systems so they take advantage of all these whiz-bang new features, still have to report on this stuff. Here are a few tips I’ve collected, which just might help, next time you find yourself with a KPI breathing down your neck:

1) Exports/imports are significantly faster than portal row creation. If you have to write a bunch of reporting records, you will save a lot of time by exporting the source and importing it into the destination, rather than looping through the source and writing to the destination.

2) Less is more when collecting data. For the layouts you run around to when setting up your exports, leave them blank, with no fields. You can still do scripted finds and exports without the fields actually being on the layouts, and layouts load much faster when they don’t have to worry about displaying anything. Also, set them to form view – that makes a difference, too.

3) Speaking of less is more, only include the fields you need when you do your exports.

4) Don’t try to rewrite the whole thing every time. Remember – the only thing that really matters is what has CHANGED. Just look for records modified “today” (or since the last report run), include keys in your export, and “update matching” in your import. You can include changes to related items in your search (i.e., search for sales orders where the lines were modified “today”). That does slow record collection a bit, but not nearly as much as relying on related calcs when reporting. If you narrow your search to only recently-updated records, you will always be dealing with a comparatively small (faster) data set. For many reports, I do this once an hour – that’s usually often enough for even the most particular CEO’s – but you can do it more often.

5) In your reporting table, don’t reintroduce the problem by having a lot of calcs & summaries. If you have to include calcs, make sure they’re local. The whole point of creating this table is to include all necessary pieces in one place, so related info isn’t an issue. Remember, you’re updating often based on changed local and related data – don’t worry about missing something – if you structure it right, that will be impossible.

All of these ideas fit very well into the current trend of “letting FileMaker be FileMaker” and capitalizing on its strengths. A simple list of records is very easy for FileMaker to search, sort, and “scriptify” quickly. Plus, without a lot of related data slowing you down, and all the heavy lifting being done on the server, this approach is FMGo, WAN, and CWP friendly – and that’s where we’re all headed. Have fun!
Tweet