1 of 1 people found this helpful
"When I am building an order I might find the product, if not I need to enter the new product. Ideas on how to do this in a user friendly way?"
You simply add the new record(s) to product and/or deliverables. That can be as simple as manually changing layouts to layouts based on those tables, creating records and filling in the fields or you can set something more sophisticated such as a popover with global fields and a button. You enter the needed data into the global fields, then click the button to a) use the data in the global fields to create the new record and b) add the reference to the new deliverable to a line item for your current purchase order.
"How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received"
You'll need more tables related to PurchaseOrder where you document what is received as compared to what was ordered.
"How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?"
you've answered that question yourself. You can't put in products as it won't work given different prices from different suppliers of the same product.
2 of 2 people found this helpful
Take a look at FileMaker Inventory Resources. There are articles and download files that may be helpful.
a) How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received
Take a look at the Receiving Goods article at FileMaker Inventor Resources.
There is also an accompanying demo file that may be helpful. A couple of supporting YouTube video are also available: one on the process and a longer one “under the hood” that takes a look at the tables, fields, and scripts.
b) How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?
Here’s one way to handle this:
1. For each SKU have a primary supplier. This can be marked in the Deliverable table but you need to make sure there is only one primary supplier per product.
2. Once you have the primary supplier for each SKU you can use that price for your Sales Order Line.
3. I recommend the following fields for your Deliverable table:
a. Cost. This is the amount of money the supplier charges you.
b. Markup. This is the % you markup each SKU.
c. Price. Cost + Markup.
d. Lead Time. Number of days it takes from the time you order the SKU until you receive it into stock.
Regarding price changes, do you need to see the changes? If no, then simply update the value as needed. For your cost, that will likely be when you receive the invoice from your supplier. For price, that’s up to you (using the definitions for cost and price above). If you don’t need to store the history of cost and price changes, just overwrite the existing data. If you want to track the history of cost and price fluctuation for an SKU then create a Price Log table with the Product ID, Supplier ID, Date Changed, Existing Cost, New Cost, Existing Markup, New Markup, Existing Price, New Price.
Thank you all very interesting input and food for thought.
Daniel a special thank you for the very clear material you took the time and pain to build and share with all of us.
I think I set my sights on too much of a task as I'm really new to all of this and struggling.
Ciao from Rome