5 Replies Latest reply on May 8, 2014 8:29 AM by philmodjunk

    Creating a Report from 2 Tables

    OtisReeves

      Title

      Creating a Report from 2 Tables

      Post

           Hi, I am fairly new to FMP and this is my fist post here. So please be kind :)

           I need to create a report from 3 different tables. I have imported these tables from access and have made Data entry layouts that work wonderfully with the relationships i have established. However when I try and make a report to show Job recap of what I spent on a job i am failing.

           Attached is a screen shot. Top is the table and relationships. Bottom is a rough idea of what i am wanting for a report.

           Table one is JobOrder. This contains information about where the jobs, When, Person of contact, etc.

           Table two is Job Expense. This contains expenses types such as fuel, lodging, misc

           Table three is Employee expenditure this hold the info of who worked on the job what their pay was and their per diem.

           When I find a specific Job I want to print a Job Recap report showing what I spent on labour and Expenses and total it.

            

            

      ScreenshotFMP.jpg

        • 1. Re: Creating a Report from 2 Tables
          philmodjunk

               Your report is actually based on three tables, JobOrder, tblJobExpense and tblEmployeeExpenditures.

               It would be easier to report this data if all expense data was recorded in a single table rather than two tables. A field in such a table can be used to distinguish between Job Expenses and Employee Expenditures.

               Then your report can be set up as a summary report with sub summary layout parts providing the "Employee Expenditures" and "Job Expense" sub headings with the expense groups for each category grouped underneath them. Additional sub summary parts set up to print below the body can use summary fields to show a sub total expense for each category.

               Without making that change, your options get a lot messier. One method is to put portals to both expense tables on a layout based on JobOrder. The portals can be sized to be many rows taller than you expect to need them to be and then set them to "slide up" and to "Resize enclosing part".

               A compromise between these two options is to set up a list view layout based on one of the two expense tables with info from Job Order added to the header and a portal to the second expense table used to list expenses from that table. Such a portal would be placed in either a leading or trailing grand summary part depending on which table you base your layout.

               PS the relationship from JobOrder to tblEmployeeExpenditure does not look like it should match records by both Order and Order Date. The date field in the expense table would be redundant if it is supposed to show the same date as the date of the single related JobOrder record linked to it.

          • 2. Re: Creating a Report from 2 Tables
            OtisReeves

                 Thanks for your response.

                 I really don't know how I can cram all that different information into one table with out it being a mess. I have over 100 employee/contractors and anywhere from 1 to 20 of them can be assigned to a job. The other table is used for other expenses that are not Payroll related such as fuel and lodging. I also have to produce reports at the end of the year showing how much a person made and a report showing what I paid in other cost for itemizing my taxes. This is another reason i have separated them. 

                 I also would rather find a solution that doesn't not net portals. I have portals set up on my layout now and they work great but not so good for printing..

            "PS the relationship from JobOrder to tblEmployeeExpenditure does not look like it should match records by both Order and Order Date. The date field in the expense table would be redundant if it is supposed to show the same date as the date of the single related JobOrder record linked to it."

            I linked the to so when the start date of the job was entered it would also place it in the employee table. Its much easier so see how many jobs BOB worked on when it has a date :) Should I use a Lookup value here instead? 

                  

            • 3. Re: Creating a Report from 2 Tables
              philmodjunk

                   I have over 100 employee/contractors and anywhere from 1 to 20 of them can be assigned to a job. The other table is used for other expenses that are not Payroll related such as fuel and lodging. I also have to produce reports at the end of the year showing how much a person made and a report showing what I paid in other cost for itemizing my taxes. This is another reason i have separated them.

                   None of this sounds like something that requires using two separate tables for your two separate types of expenses. Each expense logged will still be a separate record in such a combined table. Those records that log expenses by an employee can still be linked to records in the employee table. Job expenses would not be linked to an employee record.

                   When working with such data, there is often two basic approaches to choose between:

                   Set up a single table with all the needed fields, but leave fields unused for a specific record if it is of a type that does not need to record data in that field.

                   Set up a table where each field store data common to all records, such as an expense type and an amount, but then link records to "detail" tables that have the fields specific to a particular record type. Thus, such a central table could link employe expense entries to a table with fields such as your "per diem" field and a different related table could have fields specific a Job Expense record.

                   With either approach, you can still set up layouts dedicated to recording data of just one type--such as a layout for logging job expenses and a different layout for logging employee expenses.

              • 4. Re: Creating a Report from 2 Tables
                OtisReeves

                     So what your saying is have one table with all the fields I need in it.. the Records that has the employee expenses the Job expense fields would be blanks and the records with the Job Expense info in it would leave the employee expenses blank?

                • 5. Re: Creating a Report from 2 Tables
                  philmodjunk

                       That is one of two options that I described in my last post. The field that stores the dollar amount would be used in both cases.