Create Invoice Report with Data from Multiple Tables

[j4jason83]

Hello all,

I am working on designing a report that I can print out and send to each customer. On this invoice report, I want to display related records from 2 related tables. I had thought about using portals but they don't print well.

The first table will display the time sheets entered in for this particular project. The second table will contain any materials used that were used on this project. I am not sure how to go about getting this to work with data from 2 different tables. Some projects might only have records in one table, so I need the other section to disappear when there aren't any matching records. I want to have a subtotal for each section if there are records to be printed.

My database structure is:

Invoice ---< Timesheets_LineItems

Invoice ---< Material_LineItems

I thought about using parts, but I can't seem to figure out which parts I would use to accomplish this task. Any ideas would be greatly appreciated.

[LaRetta]

This is not an easy task nor will be easy for future whenever you need to combine the tables for reports, summarising etc.

 

Really, the tables should be a single LineItems table.  Labor and materials are BOTH 'products' and can be configured as such with the hours as quantity and the Price as your hourly rate.  Your life will be so much simpler.   So single product table with labor and materials, and a single LineItems table listing all the 'products' from your sale.   :-)

[LaRetta]

BTW< if you structure it like this then you generate your invoice from your LineItems table where you can sub-summarise by both Invoice Number and Product type (whether labor or material).  Most reporting should take place in the single LineItems table.

[j4jason83]

What if I link my Timesheets table to my LineItems table as well? Then everything would be in one table. So my structure would look like:

Invoice ---< LineItems --- Products

--- TimeSheets

Then on my LineItems portal I could select either Product or TimeSheet and then have it show the appropriate records in the value list so that I am only presented with the records that apply to a specific customer.

If everything is in one table, how would I go about separating the LineItems so that all the Timesheets are grouped total with a subtotal and then the materials are grouped together with another subtotal?

[LaRetta]

If everything is in one table, how would I go about separating the LineItems so that all the Timesheets are grouped total with a subtotal and then the materials are grouped together with another subtotal?

 

You include the field Item Type (whether Material or Labor) which is looked up from your Products table when you add an item.  You can then create a standard sub-summary report grouped and sorted by specific order of:

 

LineItems::InvoiceID

LineItems::Type

 

In LineItems, create a summary field which is Total of your Amount field then place that summary field into both of these summary parts.

[j4jason83]

I worked on this last night and finally made some progress. I am now storing everything in my LineItems table. I added a few fields to deal with the time records. I also added LineItem_Type so I can filter for specific types of records later on. I created 2 portals on my Invoice Layout that show my LineItems table. One shows the fields for Time, the other shows fields for Materials.

 

Now what I need help with is creating my Invoice Layout so I can print it out. I want to have all the "TIME" records lumped together with a subtotal below those records. Then I want the "MATERIAL" records grouped together with another subtotal below for those records. At the bottom of the Invoice, I want a subtotal for all the items being billed. Where I am having issues is that I can't have more than 1 BODY section. So I am not sure how to go about this. If the invoice spans more than 1 sheet, I want it to say "Continued on Page 2" or whatever page is next. Then at the bottom of the last page, I want the grand totals.

 

Do you have any recommends or examples I can look at for implenting this?

[LaRetta]

I created 2 portals on my Invoice Layout that show my LineItems table. One shows the fields for Time, the other shows fields for Materials.

 

Now what I need help with is creating my Invoice Layout so I can print it out. 

 

I am currently on iPad so I can not produce a sample or look up my favourites where Comment's invoice example is on my MBP.  Your invoice should be printed from your LineItems table.  You need to read up on creating a report in FM Help.   Here is step by step.

 

Wait ... are you still on version 11?  I ask because my step by step would change a bit if so.  Oh well, see how you go with this; I'll try to remember them both and produce a generic instruction which should work for both:

1. Create new layout  REPORT (with sub-totals and totals)
2. Include all totals when asked
3. Select all of the LINEITEM fields you want, including the LineItems InvoiceID and new LineItems Type.
4. In organising them for grouping to summarise them, select first your InvoiceID from LineItems and then second the Type.   Both will produce a sub-total.
5. Sort in exact same order as #4.  This is important that your report always be sorted by your 'break fields' - the fields specified in 4.
6. Then on sub-totals, select your summary field and place it ABOVE AND BELOW for your InvoiceID.  Do the same with Type.
7. Step 6 can be handled later if you get confused here.  At any time you can choose Layouts > Part Setup and see a replication of your report listing its parts and how they are defined.  Again, read up in Help on reporting concepts.
8. Set any header or footers you want... can be done later as well.
9. Let FM create the script for you as it will include the critical sort step.
10. Your logo and information goes in the Header
11. Now drag your LEADING InvoiceID part to increase its size.  This part is where you will put information from your Invoice Table such as ship method, terms.  Also place your customer's information here (from your Customers table) such as Customer Name and Address.

On #11, I do not know your structure.  It should look like this:

 

Customers --< Invoices --< LineItems >-- Products

 

You can cross-place your fields.  This means that, since Invoice is the ONE side to your MANY LineItems, you can simply place fields from Invoices onto your LineItems layout so you do not have to include those fields again in LineItems.  This elimination of redundancy is the power of relational.  And since Customers is ONE to Invoices which is ONE to LineItems, you can place Customer fields directly onto your LineItems layout as well.  Place them up in the InvoiceID leading part.

 

So your summary field from LineItems will go in both parts below the body so it will sub-total your Type and your Invoice.  This sub-total on your Invoice will actually be the Invoice Total.  We do this so that you can select to print many invoices at once and, when sorted by both break fields, it will sub-total both.

Create your report from LineItems.  This way, it will expand to two pages.  You do not want to use portals.  By generating it from LineItems, it is a straight report and simple to group and sub-total.  :-)