Multiple Line Items

[molevid]

Hello,

I am new to FM and am having problems creating multiple lines on invoices and hope that someone could help me. I want to create an invoice for a customer and be able to create multiple products lines on ONE invoice.

I have a created the following table:-

Customers Database

Products DB

Customer product DB

Invoice DB

Line Item DB

I have created a simple version which allows one line per invoice (created without line items DB) and this works OK, its just the majority of my customers order more than one product at a time so I would be creating a lot of invoices and hope there is an easier way of creating invoice with multiple line items and still retain the information i.e. products sold to which company on which date on which invoice number.

Can anyone give any suggestions on a good way of achieving this. It seems like a simple request however I cant seem to find a solution.

Thanks

[LaRetta]

Whether you have only one product on an invoice or multiple, you should ALWAYS use your Lineitems table for every line.

Check this demo file by Barbara

Edited May 5, 2009 by Guest

[mcm237]

This solution assumes that you have created a relationship between products ordered and the customer who ordered them.

Create a layout that pulls data from your Customer table. This will be the invoice for each customer. Put a portal on the layout that grabs data from the products ordered table. You can specify how many rows (aka how many products) you want displayed and can sort the records however you want.

If you only want to see the products ordered say in the last month, perform a find.

Let me know if this makes sense or if you have any questions.

[molevid]

Thanks for the link.

The tables you on the link seem to be similar to what I have already. Still the problem I have is the function of the line item portal in the invoice.

I can set the portal up to have as many rows as I want however when I go to browse mode to key in the information, it either only lets one line item be entered or duplicates the line above or if I have a scroll bar it just lists what’s in the line item table to how many rows I created in the portal. Am i going about this the wrong way hence the portal problems??

I thought that way to input the product line items into the invoice was through the line items portal in the invoice layout itself? It looks as though from the example you posted that you enter directly into the line item table and just view via the portal?

My initial thought to create an invoice, you would go to the invoice DB, Key in the Customer Number which would bring up that customer and their address from your customers DB. The Date and Invoice Number would be an automated number created every time you create a new invoice record. The products would then be entered via the line items portal (product code entered tp bring up product details, description price etc linked from products DB), when this is complete the bottom of the invoice would have a calculation for nett amount, tax and total amount. Again is there a better way to achieve what I am after?

Sorry if I am over complicating a simple task. Any ideas would be greatly appreciated.

Thanks

[comment]

Perhaps this one might be more suitable for you at this point:

http://fmforums.com/forum/showpost.php?post/309136/

[comment]

I don't think those are good ideas. For example:

Create a layout that pulls data from your Customer table. This will be the invoice for each customer.

If the invoice uses a layout of Customers, then there will at most ONE invoice for each customer.

[LaRetta]

I thought that way to input the product line items into the invoice was through the line items portal in the invoice layout itself? It looks as though from the example you posted that you enter directly into the line item table and just view via the portal?

Actually, I have never seen Barbara's example but assumed it was standard structure (which is what you need). I do not believe that entering lines directly in the portal is the best method (sorry, Michael, I assume you provided as simple example for self-rated beginner). It doesn't matter HOW the records are created behind the scenes; what matters is the result.

The problem with entering directly in the portal (using Allow Creation) is that you can end up with empty lineitem records AND ... if you have more records than displays in the portal and User keeps wanting to add records, then they must scroll to bottom each and every time. It is a PIB and Users aren't always clear how to add another record.

Comment, I've used your file (I hope I am forgiven) and I've disallowed 'Allow creation' and handle new Lineitems behind the scenes (as Barbara did). But then the User is placed on the newly created lineitem (even if it's at the bottom of a very long list). This is how I prefer to present LineItem entry to a User ... which (behind the scenes) jumps to the LineItems table (which is good habit to learn) AND ... it allows User to clearly see where to enter the new record because it places them there (something Barbara's file didn't handle). A User can add line after line easily.

The other option is the single line above the portal where a User adds new lines. I like the method but haven't used it in quite some time and I'm unsure if the behavior has changed since vs. 8.0 so I'd rather not suggest it.

InvoicesDemoREV.zip

Edited May 5, 2009 by Guest

[comment]

I don't necessarily disagree - but one must learn how to walk before they can run...

BTW, you can prevent empty LineItem records by validating.

[LaRetta]

Sure you can add validation. But, even if you have 'reset portal scroll' unchecked, and the Users happens to exit, it will keep producing a painful validation error and not provide the User a way to fix it (because they won't know that they must scroll to the bottom of the portal). And if you've ever seen the bewildered look on a User's face when they sit and stare at a full portal and can't figure how to add another line, you'll know why I strongly suggest doing it right to begin with. I learned to walk first and I regret it and wish someone would have taught me right.

It's no more difficult to learn to run (using this simple script) than to learn to walk and then go fix all their portals down the road. Learn it right the first time.

Edited May 5, 2009 by Guest

[comment]

I think we'll remain disagreed. In any case, molevid should now have the full picture in order to proceed - and that's the important thing.

[molevid]

Thanks for all your help, the example DB that you sent has been helpful as its much easier to play around and see what should be done and how you have done it.

I am having a little issue writing the script for “adding line item” however I think this is down to the fact that I have stored my tables in separate Databases rather than tables within one DB like the two examples you gave. Can I write a script that will “add a new line item” from another database?

I don’t really want to start again and create a new DB with multiple tables however if you think this is the best way for achieving the line item issues and other scenarios that I may come across in the future then perhaps now is the time to do this.

Thanks for your help so far.

[comment]

You can do either one, but I think it would be best if you consolidate your tables into a single file before you expand further.

You don't need to start from scratch - pick one file and import the other tables (select "new table" as the destination), then fix the relationships and layouts.

[LaRetta]

I agree with Comment on this. Since you are new to using FileMaker (and even when not), it is much easier (scripting, privileges etc) if everything is in one file. :wink2:

[molevid]

OK, I will create one DB for my tables and see if I find that any easier.

Could you just explain the "add item" script that was on the example you sent. I understand the principle of whats been created however the "set variable" and "set field" scripts are the ones I am not sure of.

Thanks

[LaRetta]

Comment is MUCH better at explaining things but here is the basic logic:

Set Variable [ $invoiceID; Value:Invoices::InvoiceID ]

This places the InvoiceID from the current record onto the variable 'clipboard' so you can take it with you to the LineItems table (and assign your new LineItem to that invoice). We are using a script variable (we know because we started it with $ instead of $$ which indicates persistent global variable)

Freeze Window

Just keeps the User from seeing anything you are doing. They think they stay where they are ;-) My favorite foolery.

Go to Layout [ “LineItems” (LineItems) ]

New Record/Request

Set Field [ LineItems::InvoiceID; $InvoiceID ]

Creates a new LineItem and puts that variable InvoiceID into the LineItem InvoiceID field

Commit Records/Requests [ Skip data entry validation; No dialog ]

Eliminates possibility of record lock; just good practice.

Go to Layout [ original layout ]

Go back to where you were on the invoice table (same layout, same invoice record)

Go to Portal Row

[ Select; Last ]

Puts you on the newly created LineItem (note that you should not sort your portal)

Go to Field [ LineItems::ProductID ]

Puts you in the ProductID field so the drop-down pops open for selection

Edited May 6, 2009 by Guest

[molevid]

I created a new DB, using just one DB as suggested.

I entered the script to create a new line item as explained in the last post and created a portal on the invoice layout from the line items table. However I am having problems.... when I click the "new line item" tab it just creates a new line item in the line item table but does not direct you to the portal (as per you example database you attached previously.

All the relationships seem to be working Ok in the other tables ie. Lookups. Based on what I have explained is it possible to tell if likely to be a portal problem? Relationship problem? or a Script problem?

Although I understand that its a little difficult to give an exact answer not knowing the exact relationships or script however thought that if it likely to be one of the three I could at least start looking into a specific area to see where I have gone wrong.

Any ideas?

[LaRetta]

Uhm, no, I can't tell just from your explanation. I don't know if you don't have a proper relationship or why it isn't working as suggested. You must re-create the portal and script according to YOUR table occurrence names, not the ones in the demo file. I suggest you zip and attach your file for us to look at.

Maybe Michael (Comment) has an idea, since it now seems painfully clear to me that he was right on our first discussion on letting someone walk before they run.

UPDATE:

It is possible you would have run into an issue anyway. I suggest you check the following:

Your have a relationship from Invoices to LineItems based upon the InvoiceID using =

Your portal properly points to the valid LineItems relationship (which is related to Invoices).

Your starting layout (where you fire the script) is a layout (check layout > Setup) and it says 'show records from' Invoices.

Edited May 7, 2009 by Guest
Added Update

[molevid]

I have attached my DB so far.

The only difference is that I have created an additional table called "customer price file" this table allows me to enter an individual price for a specific customer also it enables me to put customer part number on the invoice as well.

Let me know what you think.

Filemaker_DB.zip

[LaRetta]

Open that Add Item script. Go down to to the line which says:

Set Field [ LineItems::Invoice Number ]

... you do not have a calculation result specified. Click 'Calculated Result' and type $Invoice Number

You must also be sure to attach a drop-down (or pop-up) to your Unique Part Number field so that, when you land there after this script, it pops open so the User can enter the part number.

Remember that Set Field[] has two parts ...

1) Specify Target Field (you enter the field name you are setting)

2) Calculated Result (you enter the field name or calculation you are using to set which includes entering the variable here).

[molevid]

Fantastic thats now working.

Just a quick follow up question, the Line Item table in the DB I attached doesn't seem to be functioning properly. I want to be able to key in the unique part number and the customer part number & price will automatically appear. I have looked at the relationships and think I set this up correctly however its again doesnt seem to work. Is there anything you saw that would fix this or is it a fundamental problem.

Thanks very much for your help.

[LaRetta]

I do not know why you have a Stock Number, Customer Product Number AND Unique Part Number but play with this modified file. You will notice that I ditched the relationship between the concatenated Customer Number and Stock Number and instead joined on BOTH fields using an 'and' relationship (see LineItems to Customer Price File2). I added a calculation called cCustomerNumber in LineItems so it could be used in a join to your Customer Price File2.

Also notice that I created a conditional value list so that, when you create Customer 1000 invoice, it will filter the pop-up containing only THEIR products in the portal for selection. Also notice in that LineItems portal that I deleted the duplicate fields and instead you can just place the related fields directly in the portal. You will see that, in the LineItems portal, there is a Customer Part Number and Part Description field ... but that they are from the Customers Price File2 table occurrence.

I also deleted a few other redundant fields. You do not need Customer Name in every table. Since Invoice is related to Customers, just place your Customers::Customer Name field directly on your invoice.

I didn't mess with your other relationship to Customer Price File. But that table occurrence would only be a portal displaying all of your customer prices on a customer layout.

I hope this gets you moving forward. :wink2:

Filemaker_DBMOD.zip

Edited May 7, 2009 by Guest
Changed some wording

[LaRetta]

I also wanted to say that Customer Price File shouldn't need the Product Description in it since it is in the Products table. But I was confused on whether the description went with the Customer Product Number (so should reside in the Customer Price file, ie, is it what the Customer calls the product) or with the Stock Number (so should reside in the Products table).

I do not understand what you are trying to accomplish with those various product numbers but I think the Products table is out of place. My focus was on the lookup of Customer price into LineItems and the conditional value list.

[molevid]

Hello again.

Thanks to your help I now been able to create the multiple line items and have been moving on rapidly since then. Although I imagined being satisfied with what I originally set out to do I now see the real value of databases and now want more from it.

The problem I have run into now is that I want to show how many parts I have in stock through stock receipts and stock withdrawals (in my products table) however this is proving difficult.

I have a order line items table, which includes stock receipts field (quantity IN) and a sales line item table, which I assumed would act as the withdrawals through the quantity sold field (quantity OUT).

My initial thought was that I just needed to create a field in my products table and have a calculation of quantity IN minus quantity OUT. However this does to work. I have looked through the forum and nothings seems to give me the answer that I need.

Is this a basic calculation or is it much more involved than that.

Any thoughts (again).

Thanks

[LaRetta]

I have in stock through stock receipts and stock withdrawals (in my products table) however this is proving difficult.

Inventory is the second-most difficult module to design (second only to Accounts Receivable). There are different ways of handling it. Your LineItems table can also handle your stock in but I prefer to have a separate table called Purchases related to Products on StockID. You would then, from your Products table, use a calculation similar to:

Sum (Purchases) - Sum ( LineItems )

... but that is very over-simplified so don't stop reading now ... because through time several things happen: 1) a stock recount is taken and the figures need to be adjusted and 2) your system will begin to crawl after many thousands of records accumulate in both tables if the total is only based upon aggregate functions.

I prefer using an Inventory table, a Purchases table and a Yearly Inventory table. Purchases in, adjustmens (product returns & damaged) are handled in the Inventory table and yearly (or whenever physical recount happens, a new figure is set into a standard number field in table called YearlyInventory (which relates to Products on StockID. It includes the date the physical inventory was taken again and the relationship is sorted in descending order of date. There are MANY variations on this theme; this is just the one I prefer. This keeps the speed up, allows for inventory adjustments and then your calculation would be something like:

[color:green]YearlyInventory::InvetoryStartQty - Sum ( LineItems::QuantitySold ) + Sum ( Inventory::Adjustments ) and/or + Sum ( Purchases::Qty)

You will need to filter your LineItems and Purchases tables in this relationship so that entries prior to your yearly start are NOT included in THIS Inventory relationship. I would search here on forums for Inventory - there are several approaches, including using script triggers to write dynamically etc. Problem with these approaches is you lose you history when you just plan to write dynamically. I prefer an audit trail back through time which remains FAST for totaling but allows for order errors, returns, damaged goods and so forth. Since the relationship to LineItems and Inventory (and/or Purchases) is filtered down to the last date of full inventory, it remains fast.

UPDATE: Well, I mixed a simple example with a complex one (Purchases can also be included with Inventory Adjustments table; depending upon business rules). But I hope you see the vision of how you need to track. And, at year end, you will need to produce totals for damaged goods, what products could be returned to inventory etc.

Be sure that you are very clear on the business rules. Don't accept what your Office Manager says - be sure that the rules come from the accountants on what they will need. This will drive the complexity of your solution.

Edited May 20, 2009 by Guest
Corrected green

[molevid]

Thanks for the suggestions, very helpful. I need to play around and see how I get on.

Regarding the purchases table, I have a table that creates orders on suppliers (supplier order & Supplier line item), is this not the same thing? If not what fields should be in here. I tend to over kill on what information I think I need in tables when sometimes just the minimum is needed.

Another ideas on fields on the other tables may also help.

Thanks.

[LaRetta]

I have a table that creates orders on suppliers (supplier order & Supplier line item), is this not the same thing? If

Yes, it is same thing as Purchases. It's just that I began mixing a complex example with a simpler one. Sometimes an Inventory table can hold Purchases AND adjustments.

Again, it depends upon your needs and only you will be able to work through those questions. Feel free to, as you develop your design, post it here to see if you are keeping on a good relational path. And be prepared to read a lot of forum posts about it. The method I described is a modified approach which attempts to take the best of both worlds (dynamic inventory totals for up-to-date tracking, ie, customer orders 3 Widgets and Salesman can say 'we only have 2') which is SLOW if using straight aggregate ... to writing everything static (which opens upon possibilities of crash in the middle of script etc). You can have dynamic but speed as well.

In truth, I would not suggest that anyone less than Intermediate+ level even attempt it but then ... I did and I survived the first inventory system I created. The business, however, got a few grey hairs in the deal but then my first job was low pay also so they got what they paid for.

Edited May 20, 2009 by Guest
Added 'in truth ...'

[molevid]

Hello back again.

I have been at this a while and have now created inventory system that controls the stock level, Purchases in and Sales out. All appears to be functioning at present, very happy.

Again I am thinking about the next step, which is accounts receivable, this may be too much and should be happy with what I have however I would like to keep going if I can.

The initial question I have is how to transfer the information I have i.e. sales & purchases to control the accounting side. Do I duplicate the data I have or do I need more tables to do this.

Any pointers to get me in the right direction. Your help so far has been invaluable.

Thanks.

[LaRetta]

Do I duplicate the data I have or do I need more tables to do this.

Never duplicate. And you will use your LineItems table but you may need a General Ledger table and a Deposits table. AR is much too complex to explain in a forum post but I can give you some generic basics ...

AR is money received from sales. I put my customer payments in the LineItems table and apply the payment against a specific Invoice. Some financial rules require that money accepted MUST apply against an invoice (and cannot simply go 'on account'). This is a very GOOD business practice and there are various interpretations of these rules so I would check with your accountant and get clarity. Simply, if I receive a check and I am not perfectly clear on which Invoice it should apply against, the check is NOT deposited and the customer is contacted for clarity.

You also need a method of creating manual adjustments, ie, refunds on products (again refunded against an invoice), damaged in shipping, lost products, chain store discounts and such. Your LineItem table should hold an AccountNumber field and ALL money in and money out should be posted to an account.

You may also have a general ledger but AR is a small piece and usually an accountant will take your AR data (and its narrow group of account numbers) and be satisfied with that. Also check into the business rules on aging. You have a lot to study if you wish to take this on but there are many free online resources - just search for things such as "posting to general ledger" and "Accounts Receivable".

AR cannot be handled without AP dipping its fingers into your data, ie, you are given a check from AP which reimburses a customer for damaged products. And this all should be tracked in the LineItems table. You can still have multiple payments against one invoice and multiple invoices can be paid by one check. I cannot advise on how you should handle all of these pieces, only make you aware of them. Do NOT let yourself be caught like deer in headlights on this process.

Hash the rules with the Owner until they are sick of you and then ask even more. You must be sure you understand the checks and balances. Once you think you understand exactly how every adjustment/discount/payment should be handled, write up every +/- balance possibility. Ask a hundred 'what if' questions such as, "What if a customer received a damaged shipment and some products were damaged and they instead just want that money taken off their next shipment?" Keep asking every possible question because manipulating data/structure in the middle of a fiscal year is nightmare. Oh. And since you'll be handling the AR piece, you might as well handle the deposits, since you (the User) needs to enter Check #, Amount, Date etc anyway.

UPDATE: BTW, I've been out of the accounting field for 9 years. And I haven't programmed an AR module for 3 years. So you must put in the homework to check the current laws in place for your state. In truth, this should all be laid out for you as specific Developer Requirements but that rarely is the case so work closely with Management and their accountants and get everything DOCUMENTED AND SIGNED on every portion of it.

Edited June 4, 2009 by Guest
Added update

[bruceR]

Maybe like this.

Filemaker_DB.fp7.zip