Retail database question

[faaslave]

Well time for another problem. I have a database set up with a product table, line items table, and invoice table. It works fine. Now I would like to take it one step further.

I would like to make a purchase order table, and keep track of quantity of items on hand, in the product table. So I make a table with vendors:

kp_vendorID

product

product #

price

then a purchase order table:

kp_POID

kf_vendorID

P.O. #

extended price

Total

Tax

etc

add a kf_POID to the original product table and a quantityonhand field..

When that is done, and a Po is created where do I put the calc to increase the total in stock in the product table? Is there a way to tell FM that when i commit this record, look at the quanity of each item and add that to the (product::quantityonhand). But only do it at that time. Or does it have to be set up so that (product::quantityonhand) always looks at the PO table and totals any existing quantities that it finds for a related product. That way, if you were to delete a P.O., the quanity on hand would update accordingly.

Likewise and invoice would have to subract from the (product::quantityonhand).

I would think that this is a very common scenerio, since any retail business using FM would need to be able to do this. Or is this really not an application that FM is designed to handle? If it can do this, I would really like to try and make this work.

I received an example a while back from ender if I am not mistaken, of a system that tried to do this, but no luck. It used 5 tables all from different files. I don't think it was his, it was just an example he had found.

Thanks Dave

[RalphL]

I would suggest that you also have a PO line item table. Placeing an order does not change your inventory. What I did (FMP 5.5 & 6) was include a Receiving Report with a Line item. I scripted the inventory update. I don't remember the details, it may have been part of printing a receiving report.

[faaslave]

Thanks for the reply,

I was kind of thinking that the purchase order table was a line item table. But does anyone know how to do it? Isn't there an easy way to tell the quantity on hand field: take 1 away, or add 2, etc.

Is this really beyond the scope of Filemaker?

Thanks,

Dave

[Ender]

Is this really beyond the scope of Filemaker?

Hardly. FileMaker can handle most any data model, including the one I supplied you before:

http://www.fmforums.com/threads/showflat.php?Cat=0&Board=solutions&Number=132680

Here's the original thread that I was referring to:

http://www.fmforums.com/threads/showflat.php?Cat=0&Board=UBB45&Number=106688

[faaslave]

Thanks again Ender,

I did look at the one you gave me. It did make sense, but i couldn't get it to work. I am not sure i was able to get the whole example downloaded. I will upload the one I have if you would be so kind as to look at mine. It will probably be a couple days, I will try to get on it.

Thanks Dave

[faaslave]

I'm back, and I have a sample of what I have so far.

A couple things I am having trouble with so far are:

1) Purchase order not allowing me to put items in the portal

2) What would be the best way to handle quantity on hand reference the service items? They really don't have a quantity

I am going to get rid of the inventory table since the products table should be all I need. With a distinction between service items and products.

And I don't have any really nice layouts for the new tables yet. sorry.

Any help would be great.

Thanks Dave

[RalphL]

In your relationship between PO & PO Line you have it set to allow creation of records in PO from PO Line. I think you want that the other way.

I think I would separate Produts and Services.

[faaslave]

Thanks Ralph,

I was thinking about keeping them separate too. But couldn't I just have a field like category, and have service or product? Then figure out a way to say, if field = category, then don't display inventory, or ignore those fields?

I will try and work on a solution.

And you were right of course, with the creation of records, I reversed it and it is fine for now.

1) So the main question is, should I keep products and services separate, The invoices need to be able to have both on one invoice. The P.O.'s never need to deal with services. Ralph votes for separate. I think I agree.

2) I have read where people talk about using scanners, Could I get a hand held scanner to work with this for inventory?

3) Global container? I want a global container for a logo on every layout. Change it once and they all change. When I tried it that way, the graphic never displayed, it just showed he name of the file in the container. Do people here use some kind of global layout for graphics so when you make a change on one, they all change? Just wondering.

4) Doing some testing, I input a PO and inventory went up, tried an invoice and it worked again lowering the amount on hand. Then deleted an invoice, No change, bummer it should have updated the balance

5) Is it possible, that one day, I will understand this program?

If anyone has any ideas after seeing this, feel free to speak up. It's not like I know what I am doing.

Thanks Dave

[RalphL]

I would not change inventory until an order is completed, I used the print script to do this. If the items are returned then I made a return (sort of a negative order) and items were returned to inventory. I have a restocking charge in this.

In my setup labor was the only none product. It may have been easier than what you have where you are offering services for a fixed price.

I looked at the databases again today, they were done in 2002.

We used scanners for both sales & inventory.

[faaslave]

When you say not to change inventory until an order is completed, does that mean you are suggesting not to commit the record until I am done with it? I remember seeing something about this, I'll look for it.

I still don't know why when I deleted an invoice, my inventory on hand field does not update. I tried to mees with delete records via this relationship, because I am sure that has something to do with it. No luck so far

As far as services go, I was hoping there was some way to tell FM, If category says services, then don't have an inventory field, otherwise do. I may not be thinking this logic out correctly.

What scanners do you reccomend? I don't think they will spend much on one. Also, since I do not deal with retail myself, how do you update your inventory when you do an in house count? Do you just scan everything again and when the numbers don't match, an adjustment screen comes up?

Thanks Dave

[RalphL]

I was using 5.5 & 6 so I didn't have the ability not to commit the record. So what I did is when the user prints the sale the inventory was updated and the record was locked to prevent any changes.

I don't think you should ever delete an invoice, auditors don't like that. You can cancel but not delete.

The owner provided the scanner, I don't remember the brand. This job did not have a happy ending.

[faaslave]

I was thinking that someone may buy something, and right after the user finishes the invoice, they change their mind, or even better I have totally forgot about returns. hhmmm, harder than it looked at first.

Sounds like the scanner didn't work out too good, probably for the best.

Thanks, Dave

[RalphL]

The scanner wasn't the problem. It was the barcodes. Different suppliers using different codes for the same item. I requested that they supply me the information necessary to make the system work. They have not given me any more information and I have not been able to complete the project. They do not answer my emails.

[BruceJ]

These retail solutions have all sorts of idiosyncracies that most of us don't imagine when we are conceptualizing the design.

One solution to Returns is: Create another invoice, and enter the quantiy of the item as Negative. This will impact the cash drawer correctly as well as add back to the inventory. Caution - you'll need to be mindfull and make sure Tax is creditiing back top the customer as well. Use pure multiplication for the final total&tax {TOTAL=SubTotal*1.0567} NOT {TOTAL = SubTotal + (SubTotal*TaxRate)} - the latter will subtract the tax from the refunded amount to the customer - not a good public relations move - ask me how I know...

When you refer to Purchase Orders, I believe you are actually referring to Receiving Inventory? If so, you'll not only need to keep track of number of widgets ordered from a vender, but when the widget arrives, you'll need to check the item in, Quantity Received. This is the field you can reliably use in a calc in the inventory file.

In your inventory file, use a StartingCount, then a QuantityOnHandCalc {StartingCount + SUM(Receieving::QuantityReceived) - SUM(LineItems::QuantitySold}

That said, I think of Purchase Orders as Customer Purchase Orders, these are really just invoices that are not finalized. By creating a "status" in the Invoices file, you can then create a relationship to the Inventory file that uses matches an auto-enter to determine if it should use the Quanity to deplete the Inventory. However, if your move merchandise fast enough, you may not want to hold off reducing inventory as it may cause a shortage and you're better off to treat a Customer's PO just like an invoice and consider the items sold.

I would leave the Servie Items in the Inventroy items, you'll just need to work around them as they will look pretty funk after a while if you look at Quantity on Hand... it will continue to show negative, unless you enter some huge number in the starting qunatity. Not a big deal, just work around it.

[faaslave]

Thanks Bruce,

First off since I am from Kansas City myself, the MO side, I just want to start off saying all of this is very hard to do because of the how the Chiefs are doing. I am sick to my stomach knowing that I have another year to wait for any hope of seeing Peterson, Vermiel, and the defense leave Kansas City so that I might have some piece of mind.

I thought of doing what you said and just put in a quantity of 10,000 for the service items, and it would work but look sloppy. I will if I must.

How do you handle returns? using the negative invoice technique?

Purchase orders are as you said for receiving products. Let me just say that in the beginning of this I had an inventory table that was separate of the other tables, and all it did was keep track of her inventory that she didn't sell. (i.e. colors, perm solutions, that kind of thing) The products table actually had service items like haircuts, that is what she sells. Then she decided she wanted to start selling products like shampoo. I said, no problem, meanwhile thinking, what the hell! not another problem. So I made a category for services and products, one taxable and one not. I left the inventory alone.

Now what I think the right thing to do is, have a product table with what she sells, services and products. Her inventory that she doesn't sell can go in here too. So I can get rid of that table.

I also have an adjustment table, where I have to figure out how to make it so she can adjust quantities if something is lost, stloen, or even found.

I don't know what you mean by starting count, sorry I am just over my head.

This status idea sounds interesting as well, I think it is what I tying to do, but I am still lost on how to do this.

Go Chiefs.....next year

Thanks alot,

Dave

[RalphL]

I am attaching a pdf of the definitions I used in Products. Remember this was FMP 5.5. & 6. I used the Events plug-in to trigger scripts, these appear in the validation. I also had to provide for special orders so there are fields for that. There are some other fields that were used to find parts using a few letters from the name.

Products.FP5.pdf

[faaslave]

Thanks, I took a look at it and I have to say that I never used the previous versions. I guess the events plug-in is not needed anymore?

I will work on this again and post the latest version, hoping that someone on here has a working example of how they did it with FMP7.

This is better than a crossword puzzle.

Dave

[RalphL]

If you want a script to run when the user exits a field you will need a plug-in. There is a one at http://www.softs4humans.com that you can use for a donanation.

I was looking for my E-R diagram, but I haven't found it. I have a paper copy.