Newbie problem: One-to-many Relationship?

[goldensyd]

Please excuse this probably rather easy problem for you experienced users. I don't use FMPro a lot and have never figured out a solution to this problem.

I have two related files. One file is called Inventory and the other file is called Sales.

The related fields are Inventory Number, Date Sold, and Sold For (dollar amount).

In the Inventory file Date Sold and Sold For always relate to only one item.

In the Sales file Date Sold and Sold For could describe one inventory item or many inventory items being sold as a group.

The Sales file uses the field Inventory Number to lookup the Date Sold and Sold For data and place the data in the Inventory file. This works as long as the Inventory Number field in the Sales file only contains one number. However if the Inventory Number field in the Sales file contains more than one inventory number no data is transferred to the Inventory file.

Individual items are frequently sold as a group to the same client. I can't figure out how to create a relationship between many Inventory Number and one Sold For dollar amount.

In addition I will need the Sold For amount in Inventory to be divided by the total number of items sold, not the amount for the entire sale repeated for each individual item.

[Wim Decorte]

Why do you need the date sold and sold for info in the inventory file? It serves no purpose.

If you have a relationship between inventory and sales on inventoryID then you can view all sales for any inventory item through a portal in the inventory file that shows related sales data.

[goldensyd]

What is a portal? The purpose served is to mark the inventory item as sold so it is not counted at year end. For income tax purposes I must have an accurate listing of the value of my inventory. The amount it sold for is important in determining if that item is worth purchasing for resale in the future.

[transpower]

This is a standard accounts receivable application. You should have these related files: Customer, Inventory, Invoice, Line_Item. Check out the many available books on FileMaker, such as The Book of FileMaker 6 by Kubica. The reason for using FileMaker, say instead of QuickBooks, is that you can tailor the program to suit your business perfectly.