From Dwayne Wright - Certified FileMaker 9 Developer
WEB:
www.dwaynewright.comEMAIL:
info@dwaynewright.comTWITTER:
dwaynewright CHAPTER 10: Troubleshooting Portals
Previously I chatted about a portal sorting via hidden tabs that I encountered in a clients database. In the same database, there was another portal that would show product sales or purchase totals for a number of years. Now the portal only went to 2008 and they wanted a new portal row for the 2009 year. This was a very different implementation because there was a value list next to the portal. Using it, you could dynamically show every sale or purchase history. So we must be using some sort of join table and I'm thinking it must be pretty large in size.
Currently, the customer has 1,324 records in the products table and the portal shows years 2003 to 2008. So we have six years, then multiply that by 1324 records and then multiple that by 2 (for purchases or sales). So that gives me a grand total of 15,888 records.
Now I was wondering how they handled the join table for new records, so it was going to be interesting to look under the hood. It seems quite nimble and I'm wondering how FileMaker is doing these aggregate totals so quickly. After taking a peek, I became bewildered because the join table in use, the total field is a straight number field. So it is getting stamped somewhere. I wonder if this might be part of some nightly routine using the FileMaker Server scheduling. The entire office is updating to FileMaker 10, so this might be a great place to start introducing script triggers.
Sure enough, I'm finding a number of maintenance scripts but none of them are pointing me in the direction I was hoping. Wow, as I continue to look under the hood, there are hundreds of scripts here. They are nicely organized into folders but the sheer number of them is out there. I'm not sure I'm going to be able to find them via a seeking and discover manual process. After spending a hour, going to try and see if I can get a DDR or
BaseElements to help me out here.
If you haven't checked out BaseElements, do yourself and give it a go!Well, I know now that I would probably never have found this without Goya
BaseElements. It looks like the value are set via a stamping process from a report run from a Vendors perspective. The script goes from one vendor to the next manually setting the total field for the join table equal to the aggregate total of the sales order and purchase order line items. So it looks like the very cool feature I'm interested in is just a by product of running the master report. How absolutely weird.
So I had to take a week or so away from this project and knock out some other project work. Getting back to work on a complex and undocumented database you don't have much experience with is hard. I was able to import the data into the join table and all looked well and good. So I'm three steps into the myth of the separation model allowing you to leave the data file intact. So far I have had to add 4 fields and import 2,400 records to build a join table for 2009. I take a look at the reporting script that
BaseElements leads me to believe is the one stamping the data. Towards the top of the script, I see that it is setting a variable to a carriage return list of years and 2009 is absent. It looks like this list is parsed later on, so I add a 2009 value to the list and move one. I ran the report that I thought would stamp my annual value data for 2009 but I was mistaken. My imported 2009 value in the portal is empty for something I know was sold in 2009! Darn!
I then try to add a new sales order and see if I can reproduce the stamping event. Twenty minutes later, I cannot figure out how to add a line item to a new order. It has an elaborate selector field where you have to pick a supplier from one field, then a product from another field and apparently some action is required to add the item. However, I cannot find any button anywhere that will execute the action. So I have to downshift to looking at the scripts in ScriptMaker and I found a promising candidate. I decided to execute it with the script debugger running. This script "Add Item To Order" did absolutely nothing at all. Then ran a script underneath this called "Add Item To Order - New" and this did add a line item but without any product code or description information. Very frustrating and not sure how much longer I can continue to throw darts at the board with a blindfold on.
So I'm going to try and see if I can get this tagging done onsite or have the clients authorization to research it further. It may very well take me a few hours or more to hunt down this process. In the meantime, I'll go ahead and do a work around for the stamping process. I'll try to make it so that it fails gracefully and only runs on 2009 transactions. Of course, this is going to require even more fields for me to track.
About 30 minutes later and I'm still making little or no headway ... when ... my eye catches something. There are two 2009 records and one has data. So I deleted all the 2009 join files, ran the script again and 25 minutes later (yeah, it is NOT a barn burner), the correct 2009 totals are there! So I had stumbled upon the solution but missed it because my earlier attempts had sabotaged me!
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.
© 2009 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit
http://www.dwaynewright.com/donate.html
Posts
