Need some help getting started

[stevewillis]

Hello,

I'm working on moving my personal time invoicing system from Excel spreadsheets to FileMaker Pro 9. I have had some experience with Microsoft Access, but the lack of query objects in FileMaker is a bit confusing. I would very much appreciate some suggestions for how I should get started.

I have defined two tables: (1) Projects, which contains only a unique numerical id and a textual name field (2) Billables, which contains a date field, textual description field, and numerical field to represent hours. Each record in the Billables table is related to one Project id. The idea is to use the Billables table as a time sheet. Every time I complete some work on a project, I enter the project's id, date, hours worked, and a description. This works very well.

At the end of each month, I want to create an invoice for each project. This is where I am confused. I want another table, Invoices, where each record is related to one or more Billables for a given Project. How should I accomplish locating all Billables that have not yet been invoiced and relating them to a new record in the Invoices table? Then, how do I select only those new Invoice records to generate PDF reports for to mail to the client?

I've read the User's Guide and Tutorial, but I'm not clear on which of the topics I should use for this task. Any advice is very much appreciated!

[bcooney]

Your data model sounds fine.

If you specify a ProjectID when you create an Invoice, then you can show a portal to the Billables table of all records that match the projectID and have not been applied to an invoice.

Add a button that appears in each row of the Billables portal that simply sets the InvoiceID (thus turning this Billable record into an InvoiceLineItem record as well).

Have a second portal from Invoice to Billables by InvoiceID, to see the Billable records for this Invoice.

To only see the Billables that do not have an Invoice ID, create a calc field in Billables

flag_Invoiced = if (not isempty (InvoiceID), 1, 0)

Create a calc field in Invoices, Constant = 1. You will use this in the relationship to find uninvoiced Billables.

Define the relationship btw Invoice and Billables for the uninvoiced Billable records to ProjectID=ProjectID and Constant < > flag_Invoiced. This will show all uninvoiced Billable records for this project.

PS: I don't know Access, what's a query object? I'm sure that FM has an equivalent.