Invoice system template help

[gijane]

Howdy all,

 

I am trying to create a Filemaker Pro Invoicing system for my small coffee shop.

It needs to perform basic "cash register" type functions, as well as keep track of customers pre-paid and tab accounts.

 

I have been looking at the "Create an invoice system using portals" Template kit for several days now smiley-cry and since the "kit" was created originally for FileMaker 4, I am having the hardest time getting the info to work. (BTW the instructions I'm using is located at http://filemakertoday.com/portinv.htm)

 

Does anyone know where I can find an invoice system template? Any help would be more than appreciated.

 

Thanks in advance,

GI Jane

[gijane]

I have tried to do this with individual customer, invoice, menu item data bases...I have also tried it using separate tables for the customers, invoices, menu items etc. I am stumped. What is the best road to go.

 

Thank,

Jane

[Weetbicks]

First up, how much database/relational database theory experience do you have? If you are coming to this fresh with no "under the hood" knowledge, you are going to find it tough no matter how good the tutorial is.

 

Second, do you have any FileMaker experience (again see #1).

 

Taking a quick look over that tutorial (ie skipping most of it) it looks like the basic structure is all there, Customers / Invoices / Invoice Line Items / Inventory.

 

Personally I wouldnt bother with that template, it uses alot of old scripting methods that can be done mucher nicer these days.

 

Tell ya what I'll do, I'll whip up a really simple bare bones template database which represents what the filemaker 4 template does. You can take it and do what you want with it from that point. I'll have a crack at it this weekend so check this space. Hopefully it wil prove useful for other people too. (that is if someone hasn't already built the template from that article in filemaker 8+)

[gijane]

Wow! Thanks so much...

 

I used to use FileMaker many years ago...like 11, for a research job I used to work for. Before I got the job, my dad was the family filemaker expert...then I started working with it. I loved it. Mind you I never took a class or read a book on the subject. But I thought I did quite well being self-taught. My dad was soon coming to me for FileMaker help...but I left that job about 8 years ago, and hadn't done much with the program since.

 

About 2 years ago I revisited the FileMaker Pro to create a system for my husband's business. He loves it...

 

I am NOW reading about all the Magic that FileMaker can do...most of the basics I know and understand.

I understood the old versions of relational setup, but the new ones are not making sense to me at all...and I think I understand portals, but I can never get them to work right.

 

I also don't completely understand the difference between a table and a layout.

 

Oh well I appreciate your help!

Sincerely,

Jane

[Weetbicks]

Hi gijane.

 

Well I started working on the template for your invoicing database... it was a lazy sunday afternoon, with nothing better to do.... so I kinda got carried away lol. I have built your coffee shop database basing it on that template, but its, well, quite a lot nicer than how the filemaker 4 one looks I hope you like it, and it does what you want it to do.

 

The total time to build it was around 3 hours.

 

I'm sure that if you look at the scripts, and under the hood @ the tables and relationship graph you'll be able to see how it all works, I've commented scripts to help you follow them.

 

If anyone else wants to take this and adapt it feel free. The icons used are from the Nuvola icon set (http://commons.wikimedia.org/wiki/Category:Nuvola_icons)

 

 

If you have any questions about how anythin works let me know. Sorry I kinda didn't just answer questions but went ahead and rebuilt it, you don't have to use it obviously, but if anything it is a good representation of the template.

[gijane]

Wow that's Great smiley-laughing. You really out did yourself! You are truly a life saver!

 

I have one question tho...

 

I haven't altered anything yet, but it seems that only the first line in the invoice is the only one showing up...If customer Joe wants ground coffee and a t-shirt the inventory item changes instead of going to the next line and adding an additional product. Just a small tweek I'm sure...problem with the script perhaps...

 

I am still on my quest to learn more about FMP. And as I pick apart and tweeking I'm sure I will understand more

 

but for this project at least...Time is of the essence. So thank a million!

 

GI Jane

[HBMarlowe]

If you have any questions about how anythin works let me know. Sorry I kinda didn't just answer questions but went ahead and rebuilt it, you don't have to use it obviously, but if anything it is a good representation of the template.

 

Hi Daniel,

 

Nice work for a quick job, and the icons look great. Just one question. Is there a functional difference between using auto-enter calculations in the InvoiceItems table, as you did, and using lookups to achieve the same thing?

 

Thanks,

 

John

[Weetbicks]

Hi John,

 

not as far as I know. As far as I can tell lookups and auto-enters are very similar, with lookups performing some additional functionality that I didn't require, so I went with the auto-enter. I usually use auto-enters wherever possible over lookups, just force of habit I guess

 

gijane, I have to apologise, I wasn't paying attention to the name of this thread - Filemaker 8.0. Turns out to do the adding of invoice items I used the Go to Object script step which was not brought in until version 8.5, whoops!

 

I will fix it up an have it working for 8.0 after work today, which I guess is going to be in about 10 hrs or so.

[Weetbicks]

Hi all,

 

here is a filemaker 8 friendly version to resolve the issue you mentioned

[gijane]

Daniel,

 

Thank you so much...I can't tell you how much I appreciate your help on this project.

 

 

I will no doubt have questions, but you have saved me heaps of time and frustration!

 

Thanks again!

GI Jane

[gijane]

Thanks again Daniel for your help on this project...

 

I have another question regarding this project.

 

Is it possible to put buttons in a portal?

 

I have the inventory portal set up on my invoice template, and it works great! However, I have some 220 items in my inventory. So scrolling through to find the Small Latte may not be the most efficient method for data entry.

 

Is it possible to create a button-portal-script thingy that will enter the Inventory Code into the Invoice, the the rest of the invoice info would fill in via lookup?

 

I'm thinking of creating a "bank of buttons" to look just like my printed menu.

So my staff can just click on the product button.

 

If it is just a "bank of buttons," am I understanding it right in that the Inventory portal may not be the way to go at all?

At least for product entry anyways.

 

Thanks again!

GI Jane

[Weetbicks]

To do what your asking , if I understand correctly - is to setup buttons on a layout for "latte" , or any really commonly bought inventory item, so you can add it with 1 click rather than scrolling portal.

 

This is very easy with a slight modification to the script that currently runs when u click an inventory item. check out the script "Add Invoice item from Selected Inventory Item"

 

The first script step is to set a variable $InventoryID to the selected portal rows Inventory ID. The rest of the script is general in that it does its thing without reference any context in particular. Modify this script to accept inventory ID as a script parameter. So first change the set variable script step to set $InventoryID to Get ( ScriptParameter ).

 

If you want to continue using the inventory portal in any respect, you need to change the script run when u click a row. in layout mode, locate the button in the portal that runs the script (I think I made it transparent, no line colour and moved it to the back, so its a little tricky to find if you don't know how). In its action options, set a script parameter to pass. Set it to:

 

Invoices to Inventory for All Inventory Portal::Inventory ID

 

Now whenever the script is run all it is concerned with is getting an arbitrary Inventory ID and adding the invoice item. See where this is going?

 

With this extra layer of abstraction it is now possible to do all kinds of things. You can now create buttons on your layout to add common inventory itmes. Simply set up the button to run the script (which you should probably now rename since it is more generic). After selecting the script, pass it a parameter which is the Inventory ID of whatever inventory item it is you wish to associate with the button.

 

----

 

You can go one further. Setup your button bank for common inventory items, then create a field on the Home table / global / called "Other Item". Create a value list of ALL inventory item IDs (with the option to show 2nd field being the Inventory Name). You can use the old Inventory portals table occurrence / relationship to build the value list. Attach the value list to the "Other Item" field on the layout. then simply add a button next to the field "Add Item", attach this button to the script, and as the script parameter, pass it the contents of "Other Item" field - which contains an ID. (note if yer gonna do this make sure you modify the script so that if it is passed no script parameter it exits right away).

 

Using this method u can completely do away with the portal, or you can use a combination of all 3 methods (keep buttons, or an other field, and make room by reducing the inventory portal size to 1/2 rows).

[gijane]

Ummmm, wow, now that was an answer...

 

Sounds great if I had a clue what you were saying...

 

I'm afraid my Filemaker Brain is not at this level quite yet. I got bits and pieces of your suggestions.

 

I am sure it is "very simple" once I learn more...can you help me break this down into bite size pieces. Opps! I'm sorry to sound like such a simpleton, but I really do want to understand this stuff.

 

Thanks again,

GI Jane

[Weetbicks]

Okay heres an easy one, answer yes/no

 

By "Button Bank" do you mean a set of buttons on the layout that will repalce the portal? each button will say something like "Latte", or "Cappuchino" , and when u click one of the buttons, that item is added to the invoice

 

yes/no ?

[gijane]

Yes, to replace the Inventory portal

 

Something like:

 

Drink Size

Latte [M] [L] [XL]

Mocha [M] [L] [XL]

 

Where each would be a button with an assigned Inventory Id and price.

 

I know it's simple scripting. Something I'm new to.

 

Hope that clears it up.

GI Jane

 

PS Thanks for your patience.

[Weetbicks]

Check out the new copy of the file I'm attaching, in particular the invoice screen. You should be able to follow it from the instructions, good luck

[bruns]

Weetbix, If you don't mind me asking, on the icons you used which look great, how did you get rid of the black background. I copied a couple over to see what they would look like but all have a black background. Thanks if you can help..

[Weetbicks]

The icons are png format, which means their background colour is "transparent" so any filemaker colour you put behind it comes thru.

 

To answer your question would require to know a couple of things, first how did you copy them? did you go into layout mode and copy/paste it into another files layout? Or did you take a screenshot? the first method should work

 

Second, windows or pc?

 

Third, did you paste it into a container field? if so make sure that container field is transparent fill colour also, and that it has no conditional formatting applied.

 

If all else fails you can grab the icons direct from the source. they are from the Nuvola icon set http://commons.wikimedia.org/wiki/Category:Nuvola_icons You can grab the actual png files there.

[Hawaii80]

Hi all,

 

here is a filemaker 8 friendly version to resolve the issue you mentioned

Weetbix,

 

You did a very nice job! I should get you to help me create an invoice table for my tenants rent payments. I'm trying to find a way to have my database automatically enter their rent payment as a debit each month in the payments table, and I think that's what I'm missing is some sort of invoice table to make that happen where I can script auto due dates upon opening the database and it automatically enters a new tenant rent debit on the 1st of each month. Then accounting can manually enter the rent credit as rent comes in creating a 0 balance due.

[siriuslea]

Hey Weetbix, I am not a cafe member yet. Can you email me this file so I can check it out? siriuslea@hotmail.com

[brudderman]

Hello, Weetbicks!

 

I have just joined the forum and and am wondering if it's possible for me to get a copy of your invoice sample file by email. For some reason I can't edit my profile (can't even see your profile) to include an email address if it's not there already, but this one should work: jmc1846-info@yahoo.com. Thanks so much if you are able to do this!

 

James

[dorian_6_7]

Hello, I am very new to filemaker and have been asked to come up with an invoicing system for our clients using filemaker 8.5 and 10. We currently have a database that has a customer table, job table, and accounts receivable table. We mostly use it to keep track of dumpster rentals by clients so we know how many are rented, where they are, how long they have been out, and when to pickup and drop off dumpsters. We also want to be able to use it as an invoicing system for customers where it would include the ability to create prepay accounts for customers who pay ahead and would subtract from the account when a new job is entered for that client and the selected payment method was prepay purchase. It would also need to be setup so when the prepay account does not have sufficient funds it either gives a message that sufficient funds are not available to pay with that method or automatically take the remainder of the job price - prepay balance and add it to the total balance due for the customer. If the job is not a prepay block purchase it would be a dumpster rental and that would mean that the job price would be owed by that particular customer for each rental they have. I would need to have it show the balance due by the customer minus any payments made, a payment method they would like to use, and if it is paid or unpaid. I don't know if the invoicing template you created for gijane would work for me or not, but it may give me a good idea of where to go next. Is there anyway I can get your template for download or is there a better option you would recommend? Any help is appreciated as I am completely stumped and have been struggling with this setup. Thanks

[Weetbicks]

The original invoicing system related to this thread has since evolved well beyond a simple invoicing template so it's no longer suitable to distribute. FileMaker has built in invoicing templates which could be useful to look at. There are other solutions out there (fm starting point, biztracker, core2 crm) which might be useful to look at for ideas .