6 Replies Latest reply on Feb 9, 2010 12:59 PM by philmodjunk

    Report from Related records

    donjuan1498

      Title

      Report from Related records

      Post

      Hello, 

       I am some what of a filemaker newbie so bear with me. I am trying to run a report in filemaker in which I can summarize a several related tables. My setup is this

      I have an agent table which contains agent contact information that has a relational table called agent numbers which contains agent numbers that are specific to each agent and company (each agent can have many numbers). I also have several different company tables which need to be separated as I am importing invoice from each company and they al have there own unique label for records as well as some of the companies provide more information then others. I do however have about 4 fields in common between all 4 companies although they may be named diffenetly in each table. The basic common information is this. 

       

      company table common fields

      ---------------------------------- 

      Agent Number

      Commissions

      Paid Amount

      company name

      transaction date 

       

      I am able to relate these company tables with agent numbers to the agent and agent number tables. I am looking for a way to summarize the data into one table without having to reimport everything over and or using several different portals as we are growing and adding more and more companies.

       

      Is there a way to create a table which will look at all the company table and bring in the companies data so I can summarize all in one portal row? Or does anyone have any other possible solutions? 

       

      Again I apologize if I am unclear. I am still fairly new.  

        • 1. Re: Report from Related records
          philmodjunk
            

          So you have a company table where 1 record exists for each company? If not, you'll need to create one. Your imported data would then be imported into a different, related table.

           

          I can only give you the general idea without knowing more about what you mean by "bring in the companies data so I can summarize all in one portal row"

           

          Basically, add one more field to your company table: CompanyID and define it as an auto-entered serial number field.

           

          Define a matching CompanyID field in each table where you need this link back to the company information.

           

          Link your tables by this CompanyID field. (If you already have other fields for linking the same two tables, you can create a second table occurrence of one of the tables if needed.)

           

          Once you have such a link between the company table and another table, you can define fields in the company table that use aggregate functions (look up this term in help for a list of available functions) such as Sum, average, max, count to compute summary type totals for the data in the related table.

          • 2. Re: Report from Related records
            donjuan1498
              

            Thank you for your fast response. If I can elaberate a little further I think you have started me on the right path. 

             

            "I can only give you the general idea without knowing more about what you mean by "bring in the companies data so I can summarize all in one portal row" 

             

            What I am trying to do is view all of one agents business though a portal record (or whatever means possible)

            So Agent being the main layout table and agent numbers being first child (related record?)

             

            e.i.

            Agent Bob has 4 agent numbers

            agent number 1 = 1432

            agent number 2 = 5322

            agent number 3 = 1245

            agent number 4 = 3444

             

            These 2 table are related buy agentID 

             

             

            Each agent number is specific to a company. I receive invoices from several companies containing agent number and information spacific to each company with some common data that data being...  

             

            Agent Number

            Commissions

            Paid Amount

            company name

            transaction date 

             

            So in summary what I want to do is pull up Bob in a database and view all the business he has done in a portal row if it is possible. I would like something like this in the layout. 

             

            Bob  

            Agent number  Commissions  Paid Amount  Company Name Transaction Date

            Records 1...........

            Records 2...........

            Records 3...........

            Records 4...........

            Records 5...........

            Records 6...........

            Records 7...........

            etc.... 

             

            Totals ----------- Commission --Paid

             

            Once again I apologize if I am not clear or am not making sense.  

             

             

            • 3. Re: Report from Related records
              philmodjunk
                

              Agent Bob has 4 agent numbers. Why 4 numbers? Does each number refer to a different company that agent can represent?

               

              If Acme Supply has two agents, do they have different agent numbers or the same number?

               

              You will need a special join table to link agents back to the correct company if you answer the above questions like I think you will.

               

               

               

              • 4. Re: Report from Related records
                donjuan1498
                  

                That is exactly correct. 

                 

                So Bob has 4 agent numbers 

                Acme - 1245

                Spalding - 3233

                Joe Crab Shack - 3409 

                Last company - 2435

                 

                The Acme invoice data comes over with

                Agent number

                commission amount

                paid

                etc.

                 

                Acme has 1000 agent numbers each one is unique to an agent. However an agent (Bob) can have 2 different agent numbers from acme (if it already wasn't confusing enough.

                 

                What is a special join and how does it work? 

                • 5. Re: Report from Related records
                  philmodjunk
                    

                  donjuan1498,

                   

                  Thanks for following up with me on this in a private message. (You can also "bump" a thread to the top of the forum by replying back to your own thread.)

                   

                  I thought that I had posted a response to this, but must have closed my browser without clicking the Post button.

                   

                  After further thought, you don't have to have a join table for your report, though you may need it to better manage other parts of your system. A "join" table is a child table linked to two different Parent tables so that a "many to many" relationship is possible. In your case, you have many agents representing many companies. A join table to establish this link would look like this:

                   

                  Agent_Company

                  AgentID

                  Agent No (Make this an auto-entered serial number if you can).

                  CompanyID

                   

                  AgentID should also be an auto-entered serial number in your agents table and CompanyID should also be an auto-entered serial number in the Companies table.

                   

                  You link your three tables like this:

                  Companies::CompanyID = Agent_Company::CompanyID

                  Agents::AgentID = Agent_Company::AgentID

                   

                  Now, on your Agents layout, you can place a portal to Agent_Company and use it to list each Agent No and the name of the company it represents. (Add the name field from Companies to your portal row.)

                  In like manner, you can place a portal to Agent_Company on your company layout to list all the agents and their numbers assigned to that company.

                   

                  In the next post, I'll spell out a summary report that should do the trick for you...

                  • 6. Re: Report from Related records
                    philmodjunk
                      

                    You can create a summary report and place this line:

                     

                    Bob  

                    Agent number  Commissions  Paid Amount  Company Name Transaction Date

                     

                    In a sub-summary part with "when sorted by" Agent number (or by agent name if you prefer an alphabetical order) selected.

                     

                    You can define summary fields and place them in the same layout part to compute sub totals such as total commissions etc.

                     

                    Then, sort your records by the same "sorted by" field and you're done if you are using Filemaker 10. If you are using an earlier version,  you will need to preview or print your report to see its sub-totals.

                     

                    Here's a link to a simple tutorial on setting up summary reports that you may find useful:

                    Creating Filemaker Pro summary reports--Tutorial