This is not too difficult but not extremely simple either.
Do your clients reserve rentals for a specific day?
Do you track each unit rented (I assume so)? How are you tracking them (barcodes, SN)?
-For FIFO (First in First out) to reduce over use of a small set of rented items.
One Contract can have many payments? The nature of the contracts is confusing (one long term contract or many single contracts) .
InStock and OutOfStock would likely be TO's of Inventory table which has fields for stock status and repair status.
Depending on how the contracts are working and if you are accounting on receivables vs actual payments may change how your financial statements are in the diagram.
If you do not get enough help here from the community to get it done on your own, send me a message and I will work with you on it.
You should have some sort of Order table. Maybe that is what Contracts is.
A good approach is putting in a spreadsheet all the information you want to manage. Then split it up in individual spreadsheets, each holding information that belongs together in a dataset->record. A spreadsheet represents a table.
Then decide how the tables shall be related. Bigtom gave good advise by saying: not too difficult but not easy either.
I do not now how much an accounting error will cost you. If you don't feel easy with walking alone for designing a business application, consider teaming with an experienced developer for coaching.
Thanks for your reply. My business runs just like this:
1. In contract, customer and I will agree upon the details such as rental price, repair fee and compensation, etc. But the duration is unsure.
2. After I signed the contract, they will come to get those items in different days.
3. Each time they will get some types of them, maybe 3 or 5, each quantities are different.
4. During the rental, they probably return some or get more.
5. In the end of each month, I will send the monthly financial statements to them.
6. Before they finish their project, they will return my items in different times. When they return these items to my stock, we’ll pick out some of them to repair and some of the to discard. After repaired, return to my inventory.
7. Payment is long term process. It includes the rental fee, repair fee and compensation for lost and scrap.
8. Sometimes, my inventory is not enough for my customer, I must arranges them form other venders. Meanwhile, other vendor will come to borrow my items if they needed.
9. Sometimes, in my stock, I’ll keep some items for other vendors, meanwhile, they will keep some items for me.
10. In the end of every quarter, I’ll make a quarterly inventory audit, and adjust my inventory.
I answer your questions:
1. My clients just give me a call one day before they need, but not a specific day. I will arrange items and transportation for them at that day.
2. When items in or out stock, I will make a record including customer name, types of items, each quantities, and the rental start date. The rental fee is based on the rental price*quantity*time.
3. There’s no requirement of FIFO or others for my items inventory.
4. Payment is long-term process. They maybe pay me several times in the middle, but mostly, they may pay me in several months after their project finished, even several years…I need track those informations.
It’s a small business, but when I plan to transfer it into filemaker solutions, I found there are lots of relations and confused me. I am a new to filemaker, no so familiar with the ERD based on the filemaker requirements. So please help me. Thanks a lot.
Thanks for your advices. I can not find a useful method to put all informations in one spreadsheet yet, but I will keep trying. Filemaker is new and no so popular in my country, although it has 30 years history. I realize its potential in my business, but it's no easy for me to get useful resources, because there's no experienced developer for coaching I can get in my county, and my native language is not English. I want to find some help in this community. Please help me. Thanks again.
I think your best way to start here is with a simple Invoice system that you can then add to...
Tables: Fields: (where f_***_ID is a foreign ID used to relate the join table (look up foreign IDs for more details)
Inventory Inventory_ID, f_Customer_ID
Line Items LineItem_ID, f_Inventory_ID, f_Invoice_ID
Invoice ( Statement ) Invoice_ID
Join them like this...
Customer_ID = Inventory::f_Customer_ID
Inventory_ID = Line Items::f_Inventory_ID
Invoice_ID = Line Items::f_Invoice_ID
From this very basic setup, you can then create your fields as required
Customers; Name, Address, Bank Details, etc...
Inventory; Name, Type, Make, Model, Price to Customer, Price to other Vendors, Is My Stock?, Is Vendor's Stock?
Line Items; Quantity, Repair Fee, Compensation Fee, Rental Start Date, Rental End Date, Collection Date, Needs Repair?,
Needs scrapping?, Transport Cost
Invoice ( Statement ) ; Date
Then create layouts based on what you would like to use the data for...
Inventory ( My Stock )
Inventory ( Vendors' Stock )
Statement ( with a portal to Line Items for each individual item )
You would do a lot worse than starting with Filemaker's own Invoice starter solution which gives you a lot of the above in a rather attractive set up already done for you.
I hope this helps.
Thanks for your advices, I'll try your methods.
Thank you so much!