invoice project for customer wise prices
I have to create invoices using FileMaker Pro.
The invoice will have the following fields for the user to enter:
1. Invoice No
3. Customer Name
4. Item Name
5. Item Qty
6. Item Rate
Then there will be calculation based fields for aggregating Item Qty* Item Rate.
I want FileMaker Pro to auto-retrieve the latest(date-wise) price at which the current Item is being sold to the current Customer. If the current item has not been sold before to the current Customer, then it must not show anything.
How many tables do I need and with what fields? What relationships must I establish?
(I know about Table Occurrences and such other basic stuff. I need the structure of Tables and Relationships)