One Report Pulling from Multiple Tables
Well I've run into a blockade that I cannot seem to find a way around and am hoping that someone out there with more experience knows the answer.
What I have is a project management/time tracking/inventory/invoicing database that has quite a few tables for the multiple entities I'm dealing with. What I want to do is click a button and print and invoice similar to what is in the Invoicing Template for FM11. But, I don't just have inventory that needs to be on the invoice I also have labor which is in two linked tables. Labor and Labor_Line_Items. The reason for the dual tables here is that I am dealing with multiple labor rates on multiple days attached to one project that may or may not have more than one invoice.
I know how to create report layouts that will have the fields for one table in the body layout part if that information is directly from the Material_Line_Items but I have two tables with line item info, Labor_Line_Items and Material_Line_Items and these are both related to the Invoices table back through a chain of related tables.
I read some old posts that referred to making an additional table but I am not seeing how that is going to work since the fields are quite different between Materials and Labor with only a Project_ID that links them together and there could be many more material records for a project than labor or vice-versa.
To make things more complicated, on the invoice, the labor and the materials are in different sections, not all across the same line so putting the field names in the header and the fields in the body will only make sense for one or the other, unless there is a trick someone knows.
I hope someone can help. If my explanation isn't clear enough, ask and I'll try and clarify.