6 Replies Latest reply on May 13, 2009 4:07 PM by mrvodka

    List Reporting Issue

    carlz_1

      Title

      List Reporting Issue

      Post

      FM 10

       

      I have a main table called Applicants and 4 other tables, Financials, Realty, Vehicles and Life Insurance all related to Applicants in a Star shape.

      I want to be able to list on one page, a listing of all Financials, All Realty, All Vehicles and all Life Insurance.

       

      What I have been doing is inserting 4 portals onto the report.  If possible, I would like to simply show a listing of all of these assets in a streamline report. 

       

      Asset              Type                      Amount

      Cadillac            Vehicle                  $15,000

      Checking           Bank                     $5,000

      AIG                  Annuity                  $50,000

       

      Etc.

       

       

      I was considering teh creation of a join table to do this but that makes the other items I need more complicated.

       

       

      Any ideas?  Thank you.

       

        • 1. Re: List Reporting Issue
          philmodjunk
            

          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

          Realty::

           

          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.

          • 2. Re: List Reporting Issue
            philmodjunk
              

            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.

            • 3. Re: List Reporting Issue
              carlz_1
                

              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. 

              • 4. Re: List Reporting Issue
                philmodjunk
                  

                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.

                • 5. Re: List Reporting Issue
                  mrvodka
                    

                  "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.

                   

                   

                  • 6. Re: List Reporting Issue
                    mrvodka
                      

                    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.