How about basing everything on slips, so I would call that invoices. Then you can start from the idea that customer creates an invoice every time he/she rents. This Invoices table would need a relationship to products and suppliers most likely with a join table. Maybe even a join table between Products and Supplier.
I would say thanks for reply. I really appreciate it.
I forgot that I have another table.
In the first place, I also thought that I rather use INVOICE table and the joint table (INVOICE DATA).
The things is the shop will have to issue a temporary INVOICE (which I called it SLIP) and when the customer return the Items(PRODUCTS), the shop will issue a RECEIPT which the total amount will be different with the SLIP depending on the return day (which has discount rate base on the return day).
So its gonna be like Temporary Invoice (SLIP) & Final Invoice (RECEIPT).
Tables & Relationship:
- Products(pkProductsID & fkSupplierID)
- Slip(pkSlipID & fkCustomerID)
- SlipData(fkSlipID & fkProductsID)
- Receipt (ReceiptIDpk & SlipIDfk & ProductsID fk)
- Supplier (pkSupplierID)
- Payment (pkPaymentID & fkSupplierID)
still works on that ?
I would not worry so much about the temporary nature of how your invoice works before you try a relationship. FileMaker is more than capable of handling the specifics of how a record (in your case invoice) will work or change. For example a script could take care of whether Product is checked in (charged) or out, based on a field. If an Invoice is related to a Customer and a Product, It can be calculated later. In FileMaker relationships are the key. Also think of the idea of starting an Invoice and completing it. The whole time this Invoice remains related to Customers and Products. The least amount of tables the better, as long as they can get the job done. Sounds to me like what we have can work.
Thank you for your advice. I will try it .
I will report it back to you.