Convert Proposal over into Invoice

[Dr. Evil]

Hello!.. looking for a simple starting script/relationship foundation to convert proposal records over into invoice records.

Would be working with 4 tables:

ProposalTable > TaskTable

~ duplicated into ~

InvoiceTable > ItemTable

Any ideas?

[LaRetta]

Hi Randy,

1. How many total likes lines would be transferred at any given moment for a Proposal? Maybe 20 lines?
   
   
2. Would you want possibility of setting SOME of those proposal Tasks to invoice but not other Tasks on the same proposal? Ability to invoice those other Tasks later?
   
   
3. If so, you want ability to also, when viewing an invoice, to see directly into the proposal side to see where it came from and same from Proposal side ... you'd like to see if it was invoiced.
   
   
4. Most importantly, why two tables instead of one and just marking one as Invoiced? Is a Proposal more like a Project where it can be on-going with multiple invoices generated at different times? I am not saying you SHOULD only have one table (and one Tasks/Items) table as well (including payments) but you could.
   

It also depends upon the volume of your transactions. Since you do not need Invoices to view Tasks nor Proposals to view Items, using four tables isn't such a bad approach. After all, smaller 1:1 tables will be faster than a single table with more fields ( when served) because less will be downloaded from server ). So if Invoices doesn't usually care that much about all that Proposal data ( and same with it's brother/sister relationships with Tasks and Items ) then I would use four tables.

Also since this is critical financial data, I would use direct relationships and write directly from Tasks to Items using Allow Creation. Here is screen shot to give you basic. Barbara's picky about layouts - I'm picky about writing financial data, LOL. If you wish for script, I can provide that as well although it may not be until tomorrow.

BTW, I've ran many solutions using a single LineItems table because that means the information was centrally located; it seems everyone wants everything in LineItems in every way. But it also means self-joins and I'm not convinced it is THAT much slower unless the lines are containers, long text fields etc (which Tasks may be). Anyway, the green are duplicate TOs (Items~create to allow Tasks to create their invoice lines when time and Tasks~view so that lineitems (and Invoices) can view the originating information for the charge line ... used by writing directly through 1-to-1.

Wow, I'm back at a regular computer and my fingers can fly! I hope my brain kept up! I hope this made sense and at least get things rolling!! Also, I need a good graphics program - I couldn't get anything but a small screen to capture, LOL.

EDIT: COMPLETELY changed item #2 wording - I guess my brain wasn't up with my fingers on this one!! So sorry!

ADDED: So you just decide what to 'invoice', create a new invoice via script over, create invoice ID, grab invoice number, set with ClientID, come back (under freeze window). Set a global with InvoiceID and write it through to Items or wait and invoice at a 'group' point when there are enough of those Items to invoice.

Edited October 6, 2012 by LaRetta

[bruceR]

Applescript to dup parent and child records.

If your layout is set up properly (see article details)

Set sourceRec to (get current record)

Create new record with data sourceRec

Can't find my original file at the moment but there is a good write up here resulting from my technique:

http://tokerud.typepad.com/filemaker/2006/06/new_record_with.html

[Matt Malyschko]

Why do you wish to duplicate the records to a new table, instead of just generating an invoice from the proposal?

I have done something similar, where data entry is done in a one table, and then (as some of the lines in the data entry need to be repeated) I have it all bulk copied across to another table - duplicating the records that need be.

I do this with a pretty simple script that finds all the records required - in your case I assume ProposalID, and then loop through them all - setting a variable for each field, going to new table, create new record, set appropriate field from variables.

[Dr. Evil]

BruceR, THANK YOU!

TO: LaRetta (MattyM this may answer your question as well)…

1) Yes, on average about 20 lines would be transferred.

2) Yes, the ability to invoice part of proposal, then invoice other part(s) later would be a useful feature.

3) Yes, would want to see from Task view if has been invoice and see source Task from Item view.

4) This is the biggest question! Why 4 tables instead of 2? My instinct is to make this simple and keep it down to 2 tables and just mark task(s) invoiced, simple. But I think this disables feature #2 mentioned in this post. Proposal is more like a project, where it can be on-going. Plus, I feel, a 2 table system would work well for a very small team of users, say, 1-4 persons. But I feel for a larger company, many Projects/Proposals could be created that… A: get bloated, complex and are on-going, B: some or all parts never will get to an "invoice'able" state, C: all of the above.

I'm convinced (at the moment), in this case, in order to keep AR (AccountsReceivable) section of business solution clean/proper, a 4 table system is the route to take. LaRetta, your comments all seem to back up the 4 table approach.

Also by having a proposal/project management section in your data solution, it allows you to build out a single full blown proposal to client in which may span several months. Invoicing sections of proposal as work is completed or agreed upon with client. In a 2 table set up, you would have to break up into several proposals, invoicing them in their entirety, one at a time. This is a business logic decision I guess.

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

Just for another point of view on this topic:

The company I work for is currently running a fmp 3 table solution!

Projects -< Tasks >- Invoice

When deemed appropriate (typically when work is completed), task is labeled via field "approved", this label populates an approved-relationship which feeds the InvoiceScript. If "approved" task(s) exist (approved-relationship = true), create invoice record, grab invoice id, set approved task with invoice id (which marks task invoiced, removes it from approved-relationship and populates invoiced-relationship). Also locks the task from further accounting related edits.

This set up allows task to stay out of AR until invoiced and projects to be on-going. Multiple invoices can be generated from a single project.

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

With all that said, and points stated thus far, lets talk more about the 4-table system;

ProjectsTable -< TaskTable

InvoiceTable -< ItemTable

How does user mark task approved to invoice?

What happens to the state of Task once it has been invoiced and moved over to Item?

Can Task be invoiced multiple times, or does it become locked?

Etc…?

Sorry for my long winded jabber,

THANK YOU ALL for your continued help on this topic!!!