There isn't much information on what fields you have in the portal tables (Real Estate, Insurance, Personal Property, Stocks and Bonds) but you do mention they all have a Total field. I suggestion you incorporate all of this data together in one Asset table and differentiate them by a Type field (ASSET::type). You can use that Type field as your break field.
What's the difference between these tables? Are there differences?
Actually, let me update this... Each table is very different. I would need one massive assets table with detailed names of columns like:
So, if that's the only way to create a report based on asset_type, I can start creating that table and change the fields to the Asset table in all my portals.
Actually, let me update this... Each table is very different.
Your example fields don't support that stetement; they seem to be identical.
I would need one massive assets table with detailed names of columns like:
If you simply recreate all the fields from the other tables, you don't gain anything.
The idea is to have a generic asset table, where you have only one field per attribute (owner, balance, date), and one field (like assetType) that determines the type of the asset
If the asset of a given record is of type bank_acct, then that makes it clear that the balance field in that record represents a balance for a bank_acct – etc. etc.
Now you can use that AssetType field as your first level break field in a report.
Trying to explain better.
The fields I listed were not examples of "different" fields.
However, I do have many different fields which is one problem. The other problem is, the way I have this laid out currently is that each asset type is a separate table with a separate portal to enter the multiple rows for each client.
If I use one table for all these portals, then any fields I consolidate, like "owner" and "balance" will show up in the other portal rows.
The fields are different enough for each table/portal that I couldn't fit just one portal in a layout.
I created an Assets table with all the fields from all the tables. 72 fields were necessary.
So, my remaining problem is, how to populate the "asset_type" field for each row. I don't want to put a drop down in my Real Estate portal that asks "What type of asset is this", when it's obviously Real Estate.
However, I do have many different fields which is one problem.
Can you take screenshots of the field names so we can see the all the different fields in each table?
Our suspicion (if I may speak for you as well, erolst ) is that the fields are not as different as you think.
Here's a couple of the layout with two portals from 4 tables. The first two are quite different, the second two are fairly similar.
What I need to do is create a report that is populated by some key fields in each portal, plus some additional "for office" use only fields that will be drop downs indicating the current status for each asset.
For instance the report would look like this:
Address Equity Status
37 Fowler Ln $400,000 Client to do
23 Summer Ln $300,000 Firm to do
Bank Name Account Balance Status
wells fargo Partner 1 $7777 Client to do
elevations Partner 2 $8888 Firm to do
I guess I can just create a portal with all these fields and print it out, but I read that portals with printing doesn't work so good if the rows exceed the size of the portal. So, if I have 5 rows of bank accounts, it won't print any rows that have to be scrolled down to.
Thanks, the screenshots and report samples are helpful. A few more questions:
1. Does the Stocks and Bonds summary report look like the Bank and Savings Account summary report?
2. What does the Life Insurance and Annuities Policy report look like?
3. How often is the report run: Daily, Weekly, Monthly, Quarterly, or Annually? Or anytime someone wants to see the report data?
4. What is the goal of the report: to show the bottom line (e.g. the subtotal) or to show the bottom line AND how it got there (i.e. the line items)?
1. Yes, the reports are similar. Really the report only needs a few fields from the other tables. Here’s an example of what the report should look like. I created this last night.
Note that these are portals, not a report, so ideally, I my wish is to have this data in report format. Notice how only 2 fields are populated from the Properties Table, Address and Equity. The other fields are on all the tables so they can be added in this report.
2. Report is run anytime someone wants to see the data. It’s only ever run for one client at a time. So, one agreementID value has multiple assets.
3. Exactly. Bottom line, and how it got there.
The easiest thing to do is change the four different tables and combine them into one table. Create a new field called "type" and use that as the break field.
This was resolved with table occurances, with a relationship to a temp create column.