Invoice/Line Items Question

Kathysl2 · March 5, 2005

Hi everyone.

I have searched high and low for an invoice example without luck.

Currently I have an invoice file that consisits of one record per item ordered. It is working wonderfully, but I want to be able to look at a list of all invoices per customer from my customer file. Instead of showing just one reference to an invoice number, it shows a record for each item ordered in the portal. So if customer A bought three things on an invoice, my portal is showing three occurances of that invoice.

However, if I create a line items file and an invoice file (relate them and create a Sum(field) in my customer db, it works. This will take a lot of work to change all this over so before I do, I have some questions.

Can someone explain why the Sum(field) calculation shows once per invoice in the portal if I have a separate line items file, but not in the system I currently have?

I read a post somewhere about creating the layout for printing the invoice from the line items file using subsummaries, not to print from the portal. However, someone also said this is not the case in FM 7.0. Could someone please tell me the best way to create a layout for printing invoices when using both an invoice and line items file in FM 7.0. I can't find info or an example anywhere.

Thank you.

Kathy

spb · March 5, 2005

Kathy:

I'm writing from an FM6 perspective, so take this with a grain of salt. I presume more experienced FM7 people will chime in.

As a general rule for reports like this, I would make the layout in the line items file. Do not use portals (in general, trying to print from a layout with portals can be a headache, also using Sum fields in a portal can be a problem).

Make the layout with two subsummary parts. The first one is sorted by customer (put the customer name field in it, or whatever you like, just don't put any Sum fields in this part). The second one is sorted by invoice number. Place your Sum field in this second subsummary part.

The trick here is to not have a Body part at all. When you make the new list layout, it will have a Body part. Highlight the word "Body" and delete.

Now make your found set of line items then sort by customer & by invoice number. The result will seem blank in layout mode, but in Preview mode you should have the result you want. It will print the Preview mode version.

But you really should have an invoice file. Typically this is referred to as a header file, one record for each set of records matching the invoice number in the line item file.

I have a situation where I import a batch of invoice line items every day, and I have scripts that automatically group them together by invoice number and create a header record for each. These scripts will also sum up the value and stick the result in a total field in the new header record. This way, the invoice total is now residing in a simply number field and no longer needs to be summed.

The resulting header file is displayed in a portal in my customer file, allowing a quick glance at invoices & totals.

I can see a variation of these scripts you could use to loop through you whole line item file and make a header record file in a single pass. If your line item file is large, it could take a while to run, but it would be a one-time operation, and you would end up with the header/invoice file you need without spending a lot of time and effort.

Let me know if this would be useful and I could post the scripts. Keeping in mind, again, that this is FM6, not 7. There may be significant differences here and someone else might think of a better way in 7.

Steve Brown

Kathysl2 · March 5, 2005

Thank you Steve.

I followed most of this, but I think the scripts would be helpful. Also, if there are additional fields that need to be defined, please let me know (like the invoice total that isn't a sum).

Thanks.

Kathy

spb · March 5, 2005

Okay, here is a simplified example.

CAUTION: I urge you to save your line item database as an empty clone. Then make a found set of three or four invoices in your line item file & import these into the clone. Do this after you make the new fields and layouts I describe, so you don't have to make them twice. Develop the scripts in the clone, mess around with them until they work correctly, probably adding extra Set Field steps & global fields to fit your needs. Then go back to your main line item database and import the scripts in. All field names should be the same, so the imported scripts will work, though you'll have to rename them to get rid of the word "imported."

You have a line item database with these fields:

InvoiceNbr

InvoiceDate

CustNbr

ProductID

Quantity

UnitCost

ExtCost

You will probably have more, but this is the minimum for the explanation.

Make a global field for every field that needs to end up in the new header record. When I'm doing something like this, I find it easiest to make a blank layout first, so all the new fields I'm making appear only there, and it is easy to see what they are doing.

g_InvoiceNbr

g_InvoiceDate

g_CustNbr

g_InvoiceTotal (this will be filled later by calculation)

Make these two new non-global fields:

Constant (a calc field, number result, where the calc is the number one--this field will simply contain a one on every record, always).

Omit_FLAG (number field, empty)

Make your new header database with the above fields (not as globals, as "real" fields). Make a Constant field in the header database the same way. No need for the Omit_FLAG. Create a relationship between the new header database and the line item database based on Constant on both sides. This allows you to pass data across using the global fields. I usually add "_CON" to the file name in these relationships, so I can see what they are.

Make a script in the header database called "NewHeader."

New Record/Request

Set Field ["InvoiceNbr","LineItem_CON::g_InvoiceNbr"]

Set Field ["CustNbr","LineItem_CON::g_CustNbr"]

Set Field ["InvoiceDate","LineItem_CON::g_InvoiceDate"]

Set Field ["InvoiceTotal","LineItem_CON::g_InvoiceTotal"]

Go back to the LineItem database and make the script that will create the header records. If you will get periodic imports of new line items (as I do) and must be frequently making new headers, then you will need a variation that only works with the new batch (I won't go into that now, but it is only a few lines added to the scripts below). But if this is a one-time fix, and new invoice records will be created with both headers and line items, then this should do it. (Comments with ** are mine, not part of script steps)

First, make a layout in Line Items, call it "Summary" You only need to stick one field on it, a summary field that will summarize the extended cost of your line items. If your database is large, make a found set of one record before you do this, or it will take forever trying to summarize everything during field creation. Here is the basic script:

Sort (**by invoice number)

Go To Record/Request/Page [First]

Set Field ["g_InvoiceNbr","InvoiceNbr"]

Set Field ["g_InvoiceDate","InvoiceDate"]

Set Field ["g_CustNbr","CustNbr"]

Enter Find Mode (**Restore OFF)

Set Field ["InvoiceNbr","g_InvoiceNbr"]

Set Error Capture [On]

Perform Find (**Restore OFF)

Set Error Capture [Off]

Go To Layout ["Summary"]

Set Field ["g_InvoiceTotal","SUM_ExtCost"]

Go To Layout ["Working"]

Replace Contents [No Dialog, "Omit_FLAG","1"]

Perform Script [subscripts, External, "Header.fp5"]

(**this invokes the NewHeader script you made earlier)

This will isolate a group of line items and set the InvoiceNbr, date, cust nbr to the globals. Then it goes to your new summary layout, where the SUM field is temporarily holding the total for that group of line items. The script sets the g_InvoiceTotal to this number (be sure g_InvoiceTotal was defined to result in number), and your total is now a frozen number, no longer being summed. Then the script sets the Omit_FLAG to a one for each record in your isolated group of line items. Finally, it runs the New Header script in your header database, passing all the info stored in the global fields across and filling the "real" fields.

This does it for a single invoice. You will want to loop the process. Save the above script as is, call it MakeOneInvoice. Now we need to build the looping script. It is easier to see what is happening if we perform the above steps as a single Perform Subscript, rather than putting all the steps in the loop. Here is the loop script to run on the whole line item database:

Freeze Window

Loop

Show All Records

Enter Find Mode (**Restore OFF)

Set Field ["Omit_FLAG","1"]

Omit Record

Set Error Capture [On]

Perform Find (**Restore OFF)

Set Error Capture [Off]

Exit Loop If ["Status(CurrentFoundCount)=0"]

Perform Script [subscripts, "MakeOneInvoice"]

End Loop

This will make a found set of everything but the invoices already done (the Omit_FLAG controls this), and makes a header record for the group at the top of the list, them starts over again for the line items not yet flagged by Omit_FLAG. If your database is big, it may take some time to crank through, but you will end up with a header record containing an invoice total that is a static number (no longer the result of a sum or calc) for each invoice.

Relate your customer database to the header database via CustNbr, and you can place a portal with the invoices & totals. I take it a step further. My portal with invoice totals has the invoice number displayed in the poratldefined as a button. If you click the number, it will take you to a line item layout where you can view the line item detail of that invoice, if you need to.

good luck, let me know if you have any problems.

Steve Brown

Kathysl2 · March 6, 2005

Wow Steve. It's going to take me awhile to figure that out. Thanks.

In the meantime I have another question. I set up the line items and invoice table. I create new records in line items via a portal in invoices. However, I can't figure out how to get it to put in customer number and invoice date on each record I creat via the portal. It puts those in when I first create a new invoice record, but not for each additional item that is ordered on the invoice.

What did I do wrong?

Kathy

Himitsu · March 6, 2005

Kathysl2, you have to remember that a line item can create it's own field along with lookups from where it is created. So if you want the date to be put in the line items, then create a date field in the line items and set the autoenter as a lookup through the relationship that creates the lineItem record and set it to the date field that is on the invoice. I do this way because if the customer paid on a different date than entered, you want that date not an autoenter creation date.

I did an example for someone here some time ago. I will post it here so you can download it. It is a basic layout for an invoice type..

here is the posts

http://www.fmforums.com/threads/showflat...true#Post140920

that has four steps when I created it.

Himitsu · March 6, 2005

Sorry Kathy, I didn't fully read all of it... that is an easy part. I use that portal all the time. I have a student DB and I need to see all the invoices for my customer. What you have to do is just link the customer ID to the customer ID in the invoices table not the line Items. That is all. then put the portal on the customer's table and put the fields in it . I also like to make a loop relationship with the invoice table to itself. So in the relationships area, create a new occurance and put the invoice again in it. Name it something like "invoice customer lookup" just so you don't forget in the future why you did that. What this will do is when you create an invoice, and you put the customer ID in the sales area, the portal will show all the invoices you have made for that customer in the past. This is helpfull for me so I don't double bill the customer for the same month, ect... FMP7 allows a lot of cool things with relatioships that you couldn't do before. It is also a lot better if you keep all of the tables in one file. It will run a lot smoother in the long run. I created my system back in FMP4 and over time converted things all the way up... Took a lot of work but worth it.

Remember when you work with these things, think "What is the common relationship from one table to the next?" and you can think of a lot of answers for your problems. Remember, lineitems only needs one relationship to connect to the invoice. The invoice can contain all the important things for that order.

Kathysl2 · March 6, 2005

Maybe I have too much info in my tables. Is this what I want?

Line Items = Customer ID, Invoice Date, Product ID, Product Name, Product Cost, Product Qty, shipping cost...plus all the sum fields to create what is owed, paid, etc.

I understand I need this file to print invoices as well as to be able to see a proper list of each invoice per customer in my customer file.

So, what is the purpose of the invoice file other than data entry? And is it this file that should include the hard copy of the billing info for the customer it is related to?

I have a realtionship between customers and invoices, based on customer number and a relationship between invoices and line items based on invoice number.

Is this correct?

Thanks for the help.

Kathy

Himitsu · March 6, 2005

Yes that is correct. That will work fine. Remember the invoice table can be the fancy one with all the bells and whistles. The line item just stores the items being bought by the customer. I rarly use it to look at. If you want a nice looking printable invoice that can be any length with a header and a footer, then you can just creat another table that uses the line items and the invoice table to create a printable invoice, but the invoice table alone can handle it. Just make another layout and add a header and a sub-summary and just sort it by customer. then in the body, just put the fields from the line items using the relationship of invoice id to the lineitems. Then when it is done in preview mode, it will show the header and footer for each page. Now of course you will have to make some kind of script that will only view one customer, the customer you are viewing because it will create a very long invoice for ALL customers and you will be printing forever. If you use the new layout wizard, it will walk you along the way. You might have to create several with different kinds of subs and grandtotals.

Kathysl2 · March 6, 2005

Oh, I misunderstood that the invoice had to print from the line items file which is why I couldn't really figure out the purpose of the invoice file. But I see I should create the printable invoice in the invoice file.

I created my print invoice layout in line items and it seems to work. I tried creating it in the invoice file, but I can't get all the items ordered to show up.

Can I still print from line items as I have it now or should I really move it to the invoice file for printing?

spb · March 6, 2005

It depends on the average size of your invoice. My invoices typically contain around fifty line items, with some going as high as 200 or more. If yours will only ever have, say, five or six line items, you could print from the "invoice" file (I prefer to call it a header table, because to me a header record plus its associated line item records together make an "invoice").

The problem I encountered when I set mine up was that getting a portal with many lines to synchronize properly and print (particularly when the invoice ran onto a second page) was nightmarish. I shifted to the line items file where I made my fully formatted invoice layout. It has a logo in the header part, plus all info needed pulled in via relationships to the customer table and header table. The body of the invoice then becomes a simple list layout with a trailing grand summary part that will go on as many pages as needed.

The *only* reason the line item table is ever visited is to print the invoice. This is controlled by external grouping and navigational scripts, and the user never even knows they have ducked into line items to print, then ducked back out again.

All my previous posting involves creating header records for historical data. Making new invoices is another issue. The general model I use requires four tables: Customers, Inventory, Header, Line Item. The user browses within the Inventory table to make selections & enter quantities. Then the selections are applied to a customer. Scripts round up the selections, create a line item record for each (including customer number passed across via a global field as above, invoice number auto-incremented), and a header record.

Himitsu uses a different model, involving subsummary parts. This will work as well, but making the invoice layout directly in the line item table has the advantage of being viewable onscreen in Browse mode, not to mention avoiding the long invoice printing issues. But either way will work, depending on your needs.

The header table is very useful for analysis & review of past orders. For example, as in your original request, you could put a portal on the appropriate layout in your customer table that displays each processed invoice for that customer, Invoice Number, date, total. You could make the portal row a button that when clicked will take one to the header table for that invoice, where the line items could be displayed in a large portal. A "back" button runs a navigation script that returns the user to the customer table. They are never aware they''ve jumped from one table to the next.

In my opinion, this general model is common enough in the Filemaker world that it should probably have its own Forum topic.

Steve Brown

Kathysl2 · March 6, 2005

Thank you Steve. I got it. Working in the line items file to print invoices is the way to go.

Thanks for all your help.

Kathy

Kathysl2 · March 6, 2005

I'm back.

I am working on payment info and sub summary reports to view monthly invoices and payments. I guess I am missing some basic understanding here. I get that the line items file should have all the info about the products, their quantities and their prices. But does that table (line items) also include summary fields for payment info?

Can someone explain to WHY I have to have two tables (invoices and line items)?

And can you tell me which fields would go in which table?

Finally, is there a demo template for an invoice system using line items somewhere that I can download? I have search high and low without luck.

Kathy

Fenton · March 6, 2005

It's been explained already, but I'll try, in a more general sense. An Invoice is the line items that were bought by a person at a particular date & time. It is, in a way, a "shell" for the line items, a way to group them. It matches what happens in reality. A customer expects to get one piece of paper, with the items they bought at the time, with the total cost of those items, including sales tax (which I believe is rounded on the sum of the items purchased, not per item).

It would more difficult to identify this group of items without an Invoice table, and to see the total, which is what you actually receive a payment for. Plus, there is only 1 customer for an Invoice, so it is far more convenient to reference them from Invoice than from each line item.

And Shipping, if any, generally applies to the order as whole, not per item (individual charges per item could, but you'd still need to add them up).

It's a little confusing because 2 different methods can be (and are) used to calculate the totals. From the Invoice one generally uses the Sum() function (in the Aggregate section) on the Invoice relationship to Line Items:

Sum (InvoiceToLineItems::price of line)

to get the Subtotal (before sales tax) of the items.

This generally appears under the portal.

But, as others have said, you can also print the "Invoice" from the Line Items table. The Invoice data: Customer,* Date, etc., generally appear in a Header or Subsummary Part; with totals in a corresponding trailing Part.

You would generally do that when the Invoice line items took up more than a page. Because portals print badly across page breaks, often chopping off part of a line. This could be because you have many line items, or if there is a large description field which pushes it over a page.

To get the totals in the Line Items table you would need (or want) Summary fields. It's the easiest and most flexible way. Because Summary fields can also be used in reports for different found sets, after finding all items sold this month, etc..

The totals in the Invoice table are Unstored (unless you build a reliable transaction processing mechanism). So reports should be done in Line Items. It can also report on a particular item, which Invoices cannot.

If your invoices are short, always fit on 1 page, I would print an Invoice from the Invoices table. It's easier, and that's where data entry is. But it's not a big deal to do it from Line Items either, and you need most of the Summary fields anyway for reports.

*Customer data can be referenced from Line Items in 7 directly from its table occurrence, going "through" the Invoice table occurrence. Though you may want to auto-enter a foreign Customer ID for reports anyway.

Kathysl2 · March 7, 2005

Thank you!

It sure does get confusing with you have Sum fields as functions and summary fields in two different files/tables.

Kathy

Kathysl2 · March 7, 2005

Thank you!

It sure does get confusing with you have Sum fields as functions and summary fields in two different files/tables.

Kathy

Kathysl2 · March 7, 2005

Thank you!

It sure does get confusing with you have Sum fields as functions and summary fields in two different files/tables.

Kathy

T-Square · March 10, 2005

Kathy--

What Fenton is describing is the concept of normalizing your data. The primary goal of this is to eliminate the duplication of data, which is usually the source of many system problems. So in your example, you have Invoices, which track a particular order, and Line items, which track specific items on the order. Your last description of fields was pretty good, but still had some duplication of data, specifically:

Customer ID, Invoice Date, Product Name, Product Cost, shipping cost

The reason that these don't really belong is that each of these data attributes belongs elsewhere.

CustomerID and Invoice date are both attributes of the INVOICE--not the line item. The Customer is linked to the invoice once, and linking to each line item is redundant. Ditto the date.

Product Name and Product Cost would belong in a Products table (Structure: Product ID, Product Name, Product Description [for the online catalog!], product price, Product Specifications, etc.).

Shipping Cost is a little trickier. I would try and find some way to calculate a shipping cost, either based on a combination of the product weight and invoice shipping method, or some other way. That way you don't have to think about it.

Keep in mind that you might not want to normalize at this level because it slows your system down, or it complicates the formulas too much.

So now, what do you gain? Your data entry form would have the invoice stuff at top, with a portal to the line items (allow creation in portal) below. Your staff enter Invoice info one time, and then start selecting products from a dropdown list. Enter the quantity and line discount (you might add such a discount field), and move to the next. Click a button and print the result.

Clean and reliable.

T-Square · March 10, 2005