complicated relational databases...please help

[CelineCountryman]

I am trying to get our database to do something that I don't quite understand or know how to explain very well. Someone else set up the database and I am just trying to expand it for us.

Here is my problem: I have three files, "Customers" "Inventory" and "Invoices." When I open "Invoices" and enter a customer name, their shipping and billing info drops in from "Customers". When I enter a SKU for a product, all the product info drops in from "Inventory." What I would like it to do is that when I type (in "Invoices") that a customer order three of say SKU 5-101, that the inventory count of that item will be subrtacted by three within the "Inventory" file.

I get that I can pull the info over from another file, but I want the number count to CHANGE when I enter that a customer ordered three. I would like to do this so that I can export the Inventory file and know what the count is on all the SKUs without having to hand count, and subtract each time...which is what I have to do now

Sorry if I am not explaining this very well...I hope someone can help me. My email in [email protected]

Thank you!

[RalphL]

There are more than one way to do this. Next you will want add items received from vendor. For starters look at the template file "Inventory" that comes with FileMaker. This will give you a starting point. This method uses relationships to subtract the number you sold from the number you started with.

[SlimJim]

I am guessing that the current inventory count is going to drop into place so that you do not put through orders when you have no available stock. If that is the case you could turn the inventory count field into a button so that it updates when you click it, e.g. button defined as SetField(Inventory::Count;Inventory::Count - CustOrderedNumber)

[Ralph's method is much better structured than this Q&D solution]

[CelineCountryman]

Hmmm...ok I'm still lost. What exactly is the Inventory template suppost to tell me? I know that a relationship can be made between two file, there are two established ones already (for pulling customer shipping address and for pulling product info into the Invoice file). I'm trying to get the count to decrease in the Inventory file when I 'sell' an item to someone...I've tried a few things...I thought I maybe had it once, but the count didn't change, so nope...then I tried a few other things and kept getting "circular definition" errors...

...anybody??

[RalphL]

The inventory template should give you an idea about what fields you will need and what relationships you will need. You need to dig into it and understand it before proceeding with your design. Count is going to be a calculation = number started with - number sold.

[CelineCountryman]

I'm going somewhere else for help...I don't need attitude and don't need to be directed to an empty template that does not explain any further than the booklet did...if there is anybody else out there who might want to help with this "circluar definition" thing, please let me know.

[RalphL]

I am sorry that my suggestion doesn't help you. Without seeing your database it is imposible to see why you are getting the circluar definition error. It means that your calculation is going back on itself, sort like a dog chasing its tail.

I thought that the template would help you. It has the fields, calculations and scripts that you could use in your database.

[comment]

I have read this very carefully and I don't think your complaint about attitude is called for. I believe we have all learned from studying other people's templates.

More to the point: there is no way that I know of to make a regular field in one table automatically change its value as a result of something that has happened in another table. You could run a script each time an Invoice is created, but IMHO it would be much simpler to make a calc field in Inventory =

- Sum (Invoices::Quantity)