Summarizing Data in A Portal
I'm currently redesigning a database to work across multiple locations. As part of this redesign, I've moved to a data separation model, where most of my data is held in a "global" database, and most of the user interface happens in a "site" database. Records in the global database now include a "location ID" field, and the site databases each have a global "site data" record that includes a matching "location ID"
The problem I'm running into comes in trying to redesign on of the dashboards in this application. One part of my database has information about products we carry. These products are grouped into categories. In the old version, I was able to display aggregate information about the products we carry by using the category table. So, for example, to display how many products were in each category, i had a portal pointing at each category, and each category record had a calculation field summing the related products.
The problem now is that I only want to include data that's relevant to the site in question. I can no longer go through the "categories" table to get at the products because "categories" is linked to *All* products not just the ones at the appropriate location. This problem is easy enough to solve as a report layout with subsummary sections, but I can't figure out how to fit this nicely onto a dashboard.
The best thing I could find so far was this link: but that method seems rather clunky requiring that the script be periodically rerun. Is there a better solution?
Thanks for your ideas!