Just for clarity, I've reproduced your example report with table names inserted. Let me know if I'm right or not:
Asset Type Amount
Vehicles::Cadillac Vehicle $15,000
Financials::Checking Bank $5,000
LifeInsurance::AIG Annuity $50,000
You'll either have to set up a Join table where one record = 1 row and sophisticated relationships and calculation expressions pull the data from the correct field and table or:
You'll need a "scratch table" to where you temporarily copy the data for the sake of this report. Both approaches have different strengths and benefits.
What'll decide between the options is the size of your data set, how frequently the data will change and how often you need to generate the report.
Others, of course, may think of an approach that I didn't.
I just thought of another approach. I can't tell from your post if it's practical or not. You'll have to let me know.
Notice that each row in your report is listing identical types of information. You could build a table listing just these values, include a key field to link to Applicants. Include a field to identify the type of record (Financials, Realty, etc.) and then a key field that links to whichever table is appropriate given the type of record. This is still a type of Join table but less complex than I envisioned in the first post.
Thank you for your help.
I have been working on the Scratch table approach. I created a new table with description type and value fields only. Those are all I really need for the report. I then when to import from the other separate tables, vehicles, financials, life insurance and real estate. I created globals for each of those separate asset tables that imports into the type field of the new table, the description and value are also imported. Then I have a new table for just that particular applicant and can generate a report from it. I can do this dynamically each time by simply having the first line of my reporting table script delete the current records before importing.
Now my problem is (I'm weary right now) importing does not work. I am trying to import from the same database; from one table into another. It was working but now it does not. I may have been importing from another table and not realize it.
Are you able to import from one table to another within the same database? It seems like you can but ..... I noticed that the vehicles table has 6 records. When I try to import those to the Reporting table, I get 0 records imported.
Thansk for your guidance.
Interesting, I hadn't tried using import records to move data from one table to another of the same file. It DOES work, but with one interesting quirk.
When you import records into your temp table, you first need to navigate to a layout that refers to it. For some reason, I can't specify any table as the target table except the current table or a brand new table.
When you import from a file that is already open, which is certainly the case here, the records you get are the current found set for that import. Whether you use a script or do this by hand, you'd thus select a layout were you can find the records from which you want to import them and perform a find to pull up a found set of the desired records, then you'd switch to a layout that refers to your temp table and do the import records operation.
If that's what you think you are doing but it's not working then you'll need to carefully check each step of the operation to see where it's failing. The import records dialog gives you a lot of options that need to correctly selected in order to get what you want and thus that could be a factor as well.
"When you import records into your temp table, you first need to navigate to a layout that refers to it. For some reason, I can't specify any table as the target table except the current table or a brand new table."
In a Scripted Import you can. You dont even have to go to the layout of the target import first either.
First, I would check your FoundSet of your source first as Phil mentioned.
Second, I would check your import mapping in your script if you have it scripted. Import mappings are a huge weak spot with FileMaker. They can break rather easily; especially on a constantly changing database where the schema is changed often.