Invoice, combine two tables

[moon0129]

This is kind of hard to explain, but I will do my best. I am working on a job tracking system. It allows me to track time entries and expense entries. I have a table for Job Information, one for Invoices, one for Time entries, and one fore expense entries.

The Time Entries and Expense Entries are related to the Invoices table by the Job# and Inv#. This works very similarly to the Job Time Tracker that comes with FM7 Creative Starter Pack, except I have added expense tracking, thus adding another table.

I am having trouble when it comes to generating a printable invoice. Because I have created time entries and expense entries in separate tables, I need two portals to show all the information. This makes a good printable invoice difficult because I am so limited to the number of line items that can be displayed. (i.e. 10 or so for time and 10 or so for expenses no matter what)

I have considered creating another table between invoices and time entries/expense entries called "line items", which would combines all off the related entries. To me this seems like more than is necessary because I would end up with multiple entries of data.

Is there a way that I can dynamically change the number of rows in a portal?

Or what is the best way to do this?

Does this even make sense?

[Vaughan]

Time entries and expense entries are really a similar thing... if they were in the same table you wouldn't need to use the portals at all.

[Cin]

I'm relatively new to filemaker but I do have an idea.

Make sure there is a relationship to both the time entry table and the expenses table (i.e. job or whatever) Create two portals, one for the expenses with the fields from this table that you want on the invoice. Create a second portal from the time entry table with the fields you want on the invoice in the portal. Stack the portals in a column format in the body section.

Expense Portal

Time Entry Portal

Select each portal and remove borders if desired and then change the sliding/printing option for each portal (located under format) as sliding based upon all above and check reduce the size of the enclosing part.

In the Invoices table create a calc field that sums the amount from the expenses portal and another field that sums the amount from the time entry table. Create a third field (i.e. Total) that adds these two sums together.

Create a subsummary part based on job (or whatever field you use as a relationship)and add the "Total" field to sum both portals.

The Invoice might look something like this:

INVOICE

Date: [invoice date]

Invoice Number: [invoice number]

[Job]

[Address]

[City] [state] [Zip]

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

{Expense Portal}

[Description ] [Vendor ][Amount]

{Time Entry Portal}

[Description ] [Employee ][Amount]

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

[Total ]