Hi,
I'm new to Filemaker & this group so excuse me if I'm repeating old
topics.

I'm trying to finalize a solution for invoicing.

Basically -
a project has one customer
a project can have many tasks & many invoices
an invoice has many tasks

I have tables for -
customer
project
task
invoice


The project layout uses portals to set the customer, and add tasks (to
a related task table). The tasks have a 'status' to indicate whether to
invoice, not invoice or if they are processed. The invoice only
displays tasks with a status of invoice.
The problem I have is when I create an invoice (via a script that
places the project id into the invoice) how do I set the 'status'
fields of the related tasks to become processed. I want to use lookups
so I can 'freeze' the data on the invoice but it seems like I'm
duplicating most of my invoice & task tables. Is this the correct way
to do it?

The second problem I have is how to prevent duplicating invoices, how
can I find if the invoice exists?
Finally, should the project to invoice be a many to many relationship?
I currently have a one to many relationship.

Help is appreciated

Drew Reece

Drew,
(You didn't tell what version of FMP you're using : I presume FMP6)
Without reading nor digesting all what you said, I can still say 2
things :
First remark :
I think you need an extra file "Details" with one record per couple
'invoice-task'.
If you forget for a moment the projects and focus on invoices and tasks
: the 'Invoices' file has one record per invoice number, and the 'Tasks'
file has one record per task description. Say for the invoice I001, you
want to have 3 tasks T001, T010 and T100, the new file 'Details' will
have then 3 records I001-T001, I001-T010 and I001-T100. You'll edit your
invoice from 'Details'.
Now, Projects adds a new layer. I suppose it does not have tasks that
are not part of an invoice. Then that 'Projects' file is only a list of
the invoices and their attached tasks that pertain to that project. Then
the project P001 ID is just to be added to the 'Details' and 'Invoices'
files. The rest is only a question of presenting the records from
Details with summaries and sub-summaries sorted by
Project/Invoices/Tasks.
Second remark :
You should read the text below that was posted on the forum a few hours
before yours.
Remi-Noel

-----Message object : "Re: Relationships? - help" by Bill Collins----
----------------Your original message----------------
"drew reece" <drewreece (AT) mac (DOT) com> a écrit ...

Thanks for the reply. Sorry it's FMP 7, I forgot to mention. I'll try
to understand it before replying.

PS I have been using a unique serial for each table record if thats
what you mean.

Drew Reece

You can improve your database by changing your hierarchy. Make it so
that each project relates to many invoices and each invoice relates to
many tasks. An 'invoice' doesn't mean an actual bill (though obviously
you need to have a layout that does that) but a collection of tasks.
-G

I think currently have what you suggested Grip.
At the moment I have projects that have tasks assigned in a portal,
some tasks won't be invoiced depending on their status. On the invoice
I currently show related tasks that have the same project ID and a
status of 'invoice'.

Remi-Noel how can I filter the tasks that need to be invoiced from the
ones that are not to be included?
I presume I need to loop through the 'Invoiceable Tasks' setting the
invoice ID field & task ID field in the details table.
I seem to be approaching it from the perspective of projects first
being assigned tasks, then an invoice being created for a group of
tasks.

thanks
Drew Reece

Drew,
I understand now better what you are at for.
So, assuming you have a 'Tasks' table with a record per possible task
specified by a Task_ID, I agree that you start with a project in the
'Projects' table and its Proj_ID. Now in the 'Details' table, you'll
create one record per couple Proj_ID and Task_ID, and an empty field
'ToBeInvoiced'.
What I don't understand is when - at what stage - you decide to bill
such and such tasks. To do that you'll have to put a "X" in the above
'ToBeInvoiced' field in the proper record of the 'Details' table.
Another thing I don't see is when you decide to consolidate such and
such tasks (having the same Proj_ID) into an invoice, and other tasks
(say with the same Proj_ID) into another invoice. Anyhow to do that
you'll have to fill another field 'Invoice_ID' in the proper records of
the Details table.
At that stage you'll have everything you need in the 'Details' table to
make any report of any kind with a set of scripts to pickup (find) the
adequate records and issuing reports by sorting and using sub-summaries
in the Details table or/and portals in other layouts (like the Invoice
one).
In short you have now to set up criterias to fill up those 'ID's fields
and make the needed scripts to apply those criterias, or else to fill
them by hand (?).
I can't say more at this stage.
Remi-Noel


-------------Original message----------------
"drew reece" <drewreece (AT) mac (DOT) com> ...

Remi-Noel,

At the moment I create tasks via a relationship using the 'Project ID'
via a portal which seems easier than the 'Details Table' method you
suggest. What would I gain by using the details table here?

I want to add a button to process the tasks into an invoice, but my
attempts keep failing. I was hoping to be able to change the status of
the task when I have invoiced for it. My problem is how do I set a
field 'Task Status' in multiple related records. I can get all the
tasks with the same 'Project ID' & a 'Task Status' of invoice via a
filter using the word 'invoice'.

I'm certain its all possible using FileMaker but I must be coming from
it at the wrong angle.

Thanks for your help, I'll keep on with it.

Drew Reece

A 'relationship' and a 'portal' means 2 tables or files : one is
"Projects", what is the second one ? If it is "Projects" again it is a
self relationship, and it means that you have one record per task in
which you have written the appropriate Project_ID. This is exactly what
is my 'Details' file.
Then you miss a pure 'Project2' file with one record per project, which
makes easier to create automatically a sequential Project_ID and is a
place where you can put any information on each project : name,
location, purpose, sponsors, duration, status, inscription price, etc.
The same with a pure 'Tasks' file, with one record per task. In each
record you put info like : Task_ID, name, purpose, start date, end date,
participants, prices, etc...
The same with 'Invoices' file : you may legally have to have a "book"
(this file) with one page (one record, one Inv_ID) per invoice.
These 3 files hold somewhat permanent data.
'My' 'Details' file gather all the events relative to what happens in
the span of time, with one record per event. One event (one record) is
basically a Project_ID, a Task_ID, an Inv_ID (from which you get by
relationships to their respective files all needed fixed data) and all
'volatile' data like, status (planned, approved, started, current, done,
paid, ...), dates, times, to be invoiced or not, reasons, Inv_ID,
comments, etc.
That's - to me - the needed files (tables) design which is sound and
flexible.
But, of course you may do otherwise like having everything in the same
table (file) and managing it by self relationships and adding all
permanent data in each record. For example you may have several tasks
for the same project, and in each of those records you'll have to put
not only the 'Project_ID' but also its name, its location, its dates,
its responsible man etc.

As to your second paragraph, either you fill it by hand in each needed
location (or - which is equivalent - by a simple script that puts the
word 'invoiced' in the proper record), or else you define the
appropriate 'criterias' that make such Task in that Project billable or
not. Then you script your criterias and apply it to a bulk of records.

All this is a bit theoretical, as you did not tell us what is all that
:what is a Project, what are the tasks, etc.

Remi-Noel




"drew reece" <drewreece (AT) mac (DOT) com> a écrit dans le message de news: ...

Remi-Noel,
I think I should list the tables I have, each record has its own ID

Companies
(Company ID, Address ID,Company Name, Contact Name)
Address ID links the 'Addresses' table

Addresses
(Address ID, Address details, postcode)

Projects
(Project ID, Company ID, Project Title, Description, Company)
Projects span different time scales and may have more than one invoice.

Tasks
(Task ID, Project ID, Description, Rate, Duration, Task Total, Task
Status)
The Task Status is from a value list (Invoice, don't invoice,
processed)
Tasks are work done in a project, sometimes I work for free so I need
to exclude tasks from invoices, but want a record of the work done.

Invoices
(Invoice ID, Project ID, Invoice Number, Invoice Date, Invoice Total,
Global_filter_invoiced ="invoice", Global_filter_processed="processed")

The two global filters allow me to view tasks that have a status of
either invoiced or processed.

Layouts
Company - to add name & address
Project - to add project details & tasks for that project
Invoice - to make the invoice to be able to print for the customer. I
want to use lookups to 'freeze' the related data.


I will also add a method to add expenses but I want to get the
invoice's looking up data correctly first.

I have added the 'Details' table to connect an alias of tasks &
invoicing but I cant figure out how to do the bulk adding of records.
So that one invoice includes all tasks not just the first.

I think its with the scripting I need most help.

I hope this is clear
Thanks for your help