1 Reply Latest reply on Mar 2, 2009 5:37 PM by FentonJones

    Combining Fields from Multiple Tables onto One Report

    carlz_1

      Title

      Combining Fields from Multiple Tables onto One Report

      Post

      Using FM 10.

       

      I have been owrking on this project that involves a government form where people aply for a benefit program.  The form has one section for Bank Accounts, Another one for Investments, one for vehicles, one for real estate and another for Life Insurance, among other matters.

       

      I have separate tables and portals for these tables.

       

      On anothe form that is related, they request that all of these bank accounts, investments, vehicles and real estate be listed.  The form is coomprised of 15 rows and about 5 columns. 

       

      I am trying to recreate that form and have all the bank accounts listed, followed by the investments, etc.

       

      I am having difficulty doing this.  Because there are a number of separate tables I cannot use portals.  I guess I could but there would be blank spaces between the end of one portal and the beginning of another and I would rather find another way.

       

      I can't use list view either.

       

      I suppose I could combine all the assets onto one table with an identifier field to osay what type it is but it might make the job of listing them separately on the first form more difficult and the first form is far more important.

       

      I also thought perhaps I can copy the relevant fields from the records of wach table to a separate table and go fom there.

       

      Can anyone tell me from their experience which might be the better way to go, or if you have any other ideas?    I simply want to get a report that lists all of the related records from sepaate tables ont one columnar report.

       

      Thank you.

        • 1. Re: Combining Fields from Multiple Tables onto One Report
          FentonJones
            

          If the area does not break across a page (and I doubt it does), then you should be able to use multiple portals. You just need to set Format, Sliding/Printing to Slide up, with [x] Also reduce size of the enclosing part. You must do this for the entire portal(s) and its contents. Blank rows at the bottom will be removed. You have to go into Preview mode to see the result.

           

          Sliding sometimes seem a bit of a black art, which can be pretty frustrating if things go wrong, but pretty cool when it works. 

           

          There is another method, which requires FileMaker Pro Advanced, as it uses a custom function (actually 2), GetRows, available at Brian Dunning's site (he hosts a master list of many people's uploaded custom functions). 

           

          GetRows produces the data that is in the portal within a calculation field (of the parent table), as text; you can insert tabs (into the calculation) to separate it, then format the tabs on the layout so it looks good (also a bit of a black art). If you stack the calculation fields you get the same thing as multiple portals, but as text; can be inserted as a Merge field(s), which will even break across pages without getting their little heads chopped off :-]