Line Items/Invoices Relationship

[KathySL]

I have created an invoices file for inputing and viewing customer invoices, using portals, and a line items file for printing the invoices for my customers. All of the fields are in my line items file and simply being viewed in my invoices file within the portal. However, I have summary fields that are in the invoices file that are calculating the total product cost, the total shipping cost, and the total sales tax for the invoice based on each portal row or record within the line items file. It is working.

However, know in my customer database I want to be able to view the buying history of each customer. I had it set up in a portal, but I can't view all the information I want because the info is in two different files. Example--I want to be able to see the date of the invoice, the invoice number, and the products ordered (these are all in the line items file), but I also want to be able to see what that invoice total was (that is in the invoices file).

Have I set this up the best way I can (having an invoice and line items file) and if so, is there a way to view all those fields mentioned above in one portal even if the fields are from two different files?

Thanks for the help.

Kathy

[jasonwood]

Usually you have all of the invoice information (except fot the items themselves) in the invoices database (eg: Invoice Number, Client Number & Information, Date, Sub Total, Taxes, Grand Total, Payment, etc.)

Then your line items file simply has the items sold on the invoice (item number, description, price, quantity, total price, etc.)

It soulds like you don't even have any records in your invoices database... is this correct?

For client history, I usually have a portal to the invoices database which shows the invoice number, date, total invoice, and balance due. Then if the user clicks on one of those lines, it goes to the invoice so you can see the items.

[KathySL]

Yes, Jason, I do have records in my invoices file. One for each order I get. My line items file has some of the same info, except for the summary fields I talked about. So, if I want to run income tax reports, I want to run those from the invoices file?

If I hear you correctly, the line items file should only have the items that were ordered, quantities, and prices? Currently my line items file has Billing Name and address, recipient name and address, invoice number and date, item description, item cost, shipping cost for the item, and any sales tax that would be applied to that item.

I am imputting all that info from my invoices file, via the portal. Then I have summary fields in the invoices file to tell me how much they paid in items, how much total shipping they paid, and how much total sales tax they paid per invoice.

So, what I need to do is have invoice date and number in BOTH files so I can view this and the summary field totals within a portal showing buying history?

Did I set this up correctly?

Thanks for the help.

Kathy

[jasonwood]

You have to have the invoice number in the line items (how else would you relate them?)

You really don't need any client information in the line item, that should be on the invoice.

Normally shipping charges go on the invoice... but I can see how you might want to have shipping on a per item basis, so that's up to you. This reminds me of future shop, who charged me $15 per item for shipping, even though all the items were teeny tiny.

If I were doing a solution with a lot of shipping, I would put the weight of theitem on the line item, then calculate shipping charges for the invoce based on weight and postal code.

Dollar fields on the invoice are usually calculations, something like this...

(this would probably appear below your portal to LineItems.

invoiceShipping (number field)

invoiceSubTotal = Sum(LineItems::totalLineCost) + invoiceShipping

invoiceTaxes = Sum(LineItems::LineTaxes) + (invoiceShipping * Tax Rate)

invoiceGrandTotal = invoiceSubTotal + invoiceTaxes

invoicePaid (number field)

invoiceBalanceDue = invoiceGrandTotal - invoicePaid

For client history, you'd put a portal in Clients, and set up a relationship to Invoices based on client number. You would have fields like InvoiceNumber, InvoiceDate, invoiceSubTotal, invoiceGrandTotal, invoiceBalanceDue.

Put a button on each line to "Show related record" and you're all set.

[KathySL]

Thank you Jason.

I think that will work. One last question. My company sends corporate gift baskets and it is quite likely that within an invoice some of the items would be taxed and some would not (anything shipping out of state is tax exempt), this is why I currently have tax per item (and shipping per item based on price), in the line items file. Are you saying that I can do away with that and have the tax caculated in the invoices file? If so, how could I tax some of the items ordered and not others? Would I "flag each item ordered as tax or exempt and then write a calculation in the invoices file to calculate the tax for the invoice only on those items "flagged" as taxed? If so, can you help me with the script?

I really appreciate the help on this.

Kathy

[KathySL]

Gosh, one more thing.

Often I have more that one recipient per invoice. Would I have these fields in invoice file as well, just as repeated fields? Then for printing I would have these as portal rows in the line items file? I assume I am still printing invoices from the line items file?

Kathy

[Ugo DI LUCA]

You don't need any script for that.

Just flag the line item as "tax" or "tax free" with text fields called "ftaxfree" and "ftax%", that would show "no" or "yes" (for example).

Create 2 calculation fields Ctaxinvoice ("InvoiceId" & "ftax%") and CnotaxInvoice ("InvoiceId" & "ftaxfree").

Then make a self relationship into the line item with "InvoiceId&ftaxfree" and another with "IncoiceId&Taxfree".

Now you can do whatever you wish, using these relationship for your calculations to summarize the total tax due, or show a two lines summary with both calculations.

In my business, we deal with 2 tax rates and also have free tax invoice for export.

For my business, I use both method, as the tax is also shown in the lines of the invoice, with a 3 lines tax summary at the left end of it.

For your concern about the cost of shipment, as my invoices are end-month, based on shipments, I generate the cost of shipment in the line item as a one line per shipment cost.

[jasonwood]

In my system, every product has a tax code. N = none, G = GST, P = PST, B = Both

The taxCode is another field looked up onto the line item when I enter a product.

If you only have one sales tax where you are (we'll call it "T"), that makes it a bit easier.

You still calculate tax on every line item. The tax field (we'll call it LineTax) is a calculation, If(taxcode=T, linePrice * [taxrate], 0). This says, "If the item is taxable, this field is the total line price multiplied by the tax rate, otherwise it is zero".

In invoices, like I showed you in the last post, tax is calculated like this:

= sum(LineItems::LineTax)

My invoices always have a Bill to and a Ship to address. Usually if you have multiple ship to's, you use separate invoices. Is there something unique about your business that makes this unpractical?

BTW: Never use repeating fields. There is almost always a better way.

[KathySL]

No, nothing unique about my business except to save paper and make it easier for customers to read their invoices. I can have over 100 ship to names going to one company at times. Just trying to save them 100 individual invoices.

Kathy

[jasonwood]

I think I get it now...

You sell corporate gift baskets so you typically have one client that orders many gift baskets to be sent to many different people, in which case multiple invoices would be impractical.

My suggestion then would be to have a Bill To address on the invoice, and then a separate Ship To address for each line item.

Edited: This must look like a really dumb post... the reason is I hadn't seen the previous post at the time when I wrote it!

[KathySL]

Yes Jason, exactly. And I currently have it set up the way you are suggesting. I think all I need to do is make sure some of the information I currently have in line items goes into invoices so I can view the history in a portal from my customer file.

I am also assuming that I would use the summary totals that I have in my invoices file to run income tax reports? Run them from the invoices file? Am I correct?

Kathy

[jasonwood]

I am also assuming that I would use the summary totals that I have in my invoices file to run income tax reports? Run them from the invoices file? Am I correct?

Yep... I've never actually made a summary field so I don't know much about them, I just run the new report assistant and it walks you through the process, including creating the necessary summary fields automatically.

[KathySL]

Thank You!!!

[machadodesign]

I am a novice that is trying tto do the same thing that you are discussing but I am totally lost. Is there a possibility that you could post a sample file that I could use as a visual reference or could you suggest a source such as a book.

Thanks

[jasonwood]

If you go to http://www.filemaker.com/support/solutions_tools.html

you will find the FileMaker Solution Framework.

This is a simple database that has customers, invoicing, and product databases.

I've never looked at it but I'm sure if you dismantle it piece by piece you will be able to teach yourself what makes it tick.

If you think you'd learn better from a book then have a look at the "FileMaker Resources" forum elsewhere on this site.