I am designing a order entry/purchase order system in FM15 with some interesting design requirements in the UI. I hope it would be something that is a stimulating challenge for the experts to entice them to help me, or even better, the solution is so obvious, that even a caveman could do it.
The client is a trucking company delivering produce to groceries and supermarket chains.
The workflow for typical day is:
- During daytime, orders from 'customers' are entered and processed one by one. Each order would of course have many order items. Very straightforward order entry system from a FM design standpoint.
- During evening time, purchase orders are then generated in bulk (based on the aggregated sum of the individual items ordered for the day) in the following manner:
- If store A, store B, store C, each ordered a box of strawberries, the screen would show 3 boxes of strawberries outstanding. The user could then designate which wholesaler to order all 3 boxes of strawberries from. This single step should be able include other items(eg blueberries ordered for the day.) This process is repeated until all outstanding items have been assigned a wholesaler/supplier (order quantity has been reconciled with purchase order.)
- The key feature is being able to assign in a single click the wholesaler to handle a whole bunch of aggregated quantities. This would also lead to automatic generation of the purchase order records in the database.
- In the event that the total quantity for a particular item is very large, there should be a way to break down the single quantity into two wholesalers. So if 100 boxes of apples were ordered total, ideally I should be able to do the following two steps: assign 50(out of 100)boxes to wholesaler A, then assign the remaining 50 boxes to wholesaler B. The system is doing auto-reconciling in the background so that use doesn't make any errors.
- As the purchase orders are generated in an iterative manner described in b), the system would hide the items that have been assigned a wholesaler(supplier) and only show on the screen the outstanding items.
The above specs led me consider the following solution:
Tables: standard tables and relationships: Customers, CustomerOrders, OrderLines, Products, Suppliers(Wholesaler), PurchaseOrders, PurchaseOrderLines.
Virtual Lists: One would be generated with ExecSQL to aggregate the outstanding orders for the day and used for the layout for the the purchase order assignment screen.
Scripts: Each time items are assigned to a wholesaler, the script would make sure that the Virtual List is re-calculated so only unassigned items would show on the screen, as well as the creation of new Purchase Order recrods in the backend.
Let me know if you have any thoughts or comments.
Thank you in advance!