Line Items with Multiple Parents

[jasonwood]

I work with FileMaker solutions which have Invoices, Quotes, and Service Orders.

They are all set up to share the same line items database, so that Quotes and Services Orders can be quickly made into invoices.

Rather than relating the line items by invoice number, quote number, or SRO number, I am using something I call a "Universal ID". Every time an Invoice, Quote, or SRO is created, a new Universal ID is created and assigned to that record. Any line items created then relate through that ID.

If you invoice a Quote, the script will use the same Universal ID on the invoice as was created for the Quote (rather than creating a new, unique number).

This has been working very well, and I am curious to know if others are using this method....

But... it does not enable you to invoice *Part* of a quote, then invoice the rest later.

So I was thinking about this... and all of a sudden it seems so simple... put an invoice number, quote number, and SRO number in the line items. Each of the three databases can still relate to the same line items database, but they do so using different fields. If you invoice a quote, the script would simply create an invoice, then copy the invoice number to each of the line items you want to invoice.

To accomplish the "partial invoicing" I was talking about... you could have a button in "Quotes" that says "invoice checked", and only the line items with a checkbox checked will ge given to the new invoice.

It seems so simple now that I have thought of it... does anyone foresee any problems? Is this a "normal" method of doing this? What methods are other people using?

Thanks!

--

Jason

[Kurt Knippel]

The concept seems sound to me.

[falkaholic]

If the previous method was working, then that should work fine.

The only problem I can see if it you change items in a quote after you've invoiced it, then the invoice will have different line items. That can be avoided by not letting the user change a quote after its invoiced.

I've done similar things with a quote/invoicing system. I have quotes/invoices share a database since they store all the same info, I just had a transaction type field so you label what kind of transaction it is. Then when/if a quote turns into a invoice, you I'd dupicate the record and line items (run a search on duplicating line items in this forum) and then go from there. That way you can add/remove items and change other variables if you need while keeping the original quote on record.

[jasonwood]

Yes! You're absolutely right... presently I have the Quotes database set up so that if the line items on the quote also have an invoice attached to them, a flag goes up "Invoiced--No Modifications", and the record is locked so that changes cannot be made.

This no longer works now that I want the ability to invoice particular line items (such as only those in stock... leaving the rest to be invoiced *at a later time*)

I have been trying to think of a work around but the best I can think of is to settle with just a flag on each line item that would warn if the line had been invoiced... nothing prevents the user from ignoring the flag!

I want to keep my quotes/invoices/sro's in separate databases for various reasons... but that shouldn't stop me from duplicating line items as you suggest. This is probably the safest way to go, though it results in more records than necessary.

Anybody have any other suggestions?

[Ugo DI LUCA]

BRILLIANT

I turn around for almost two years with a database and you just pointed out my needs.

Thanks to this forum, I am remodeling my DB now, making it more relational, but the scheme is still the same with a DB for Supplier, Products, Clients, Proposals, Customer Orders, Invoices, Purchase Orders.

Well, all the datas are in the same line items in a Central DB, but the Universal ID you suggest is really a brilliant idea.

I deal with Ceramic Tiles, and often, one client order is delivered in 2 or 3 months depending on the work advancement, then I can have from 1 to 6 invoices for the same Customer Order, but also from 1 to 5 Purchase Order as I do not enter all the products in on time.

Now, for the stock problem, I was thinking of creating a StockIn DB with a N

[Ugo DI LUCA]

There is something curious about your post :

- I'm using (for 2 years now) the database structure you are willing to use.

- You are actually using the database structure I am willing to use.

I'm sure there should be a middle structure from yours to mine...

What do you think of creating multiple Line Item Databases (i.e. one for Quotes, one for CustOrder, one for Purchase Order) and use a relational ID to link all these database to a Main Line Item Db.

It would therefore be possible, for example, to use a portal in CustOrder with "Allow creation of related records", even if there was a prior Quote with the same records. All these records would be "stored" in the Main Line Items, then it would even be possible to invoice part of a Cust Order...

What do you think of this solution? Would like to have your feeling as it seems your database is really close to mine (at least the goals are...)

[jasonwood]

Sorry... I am having trouble understanding your post.

You're talking about having a separate line items database for each module, then having a "Main" line items database which could relate back to everything? I don't understand how that would work.

I don't like the idea of having more than 1 line items database for similar modules (Invoices, Quotes/Orders, Service Orders), (but I'd always want a separate line items database for Purchase Orders, which are quite different in nature).

The two scenerios I talked about both involve a single line items database for Invoices/Quotes/SRO's, but there are two ways to relate to them.

One is by using separate key fields in the line item for each of the 3 modules, which allows you to decide on a line by line basis which lines will show up in which modules, and the other is to have a universal ID.

The biggest problem I have with universal ID (besides the line by line advantage) is that the user MUST create the database through a script. If the user uses the new record command, no new Universal ID will be generated.

I know I haven't answered your question directly, but maybe I have given you some ideas?

[Ugo DI LUCA]

OK Jason, be prepared for a lon post. I don't know how you guys (Vaughan specially impress me!!) can make efficient answers with only 2 lines...

So,

I do use a single line item (even for purchase orders as in my business there is always a kind of relationship from cust order to purchase order. For stock operation, I defined myself as Customer N