Yes this can certainly be done. The details of exactly what you want to do will need to be worked out but here are some examples.
Most developers would create some kind of Dashboard table. That table will have certain fields to enable relationships to other tables and calculations of various results.
Example: display total number of active accounts
We would assume here that there is a way to determine if an account is active - let's say that it has a status field with the word 'active'. So in the Dashboard table we create a calculation field, constant_active, with a text result = "active". Then we create a relationship to the accounts table where Dashboard::constant_active = Accounts::status. Then we create another calculation field in the Dashboard table with a number result = Count ( Accounts_active::AccountID ). Display this field on your Dashboard layout.
Example: display the current quarter forecast
I have no idea how you are deriving that so cannot help on that one!
Example: display upcoming events
We would assume here that there each event has at least a start date field. In the Dashboard table, we create a calculation field, today, with a date result = Get ( CurrentDate ) , and make the result UNSTORED. Then we create a relationship from the Dashboard table to the Events table where Dashboard::today ≤ Events::StartDate. Then we create a portal on the Dashboard layout using that relationship and displaying event information.
Effectively, you are setting up ODBC type queries using relationships and then calculating results or displaying records from those relationships.
Simple? Any questions just ask.
Great info, thanks! I never considered creating a separate table to aggregate information from other tables. I think I understand this concept, although I'll have to play around with the relationships a bit.
Another question - is there a way to graphically display some of this information? For example, if I wanted to show a pie chart with the number of accounts that meet a specific critieria (let's say forecast value 0-100k, 101k-250k, 250k and up) where the total is 100% of the accounts.
Yes of course. ;)
Each figure will be a calculation result, which will be a count of records through a specific relationship.
First you need fields in the Dashboard table to store your two cutoff values - 100,000 and 250,000 - I will call these cutoff1 and cutoff2.
Next you need a relationship for your lower range (0-100K). Something like:
Dashboard::cutoff1 ≥ Account::forecast
Create a second relationship (new table occurrence) for the middle range:
Dashboard::cutoff1 < Account::forecast
Dashboard::cutoff2 ≥ Account::forecast
Then a third relationship (new table occurrence) for the upper range:
Dashboard::cutoff2 < Account::forecast
Looking through each of these relationships will show all accounts in each specified range.
Now you need the calculations to count up the account records for each:
countLowerAccounts = Count ( lowerAccount::AccountID )
countMiddleAccounts = Count ( middleAccount::AccountID )
countUpperAccounts = Count ( upperAccount::AccountID )
With those three figures in hand, you can pass them to whatever graphing tool you use and produce your pie chart.
The technique is somewhat dynamic in that you can adjust the cutoff field values and alter the counts.