I've got a design question with Sharepoint 365 (hosted at Microsoft). I would like to find a design without adding custom code, or with minimal custom coding. My background I'm a developer who works in a small machinery production factory, i am usually machine coding products in assembly code or c++. In the past i worked with sharepoint 2010, but not 365-hosted, so this is a bit new to me. In our process-work-flow our products go trough various stages. And we want to keep track of this process with Sharepoint 365. Yes I know MySQL+PHP is nice too for this, but in this case its decided to keep everything hosted in sharepoint.
Situation, let me first sketch what I made so far as it is not that complex :
I made status table (Table1) for the various stages our products can be in.
I made ID name table (Table2) that contains product number and serials as an identification name for each product we sell.
I made transaction table (Table3), which contains time of transaction, a status as from Table2, and the ID name from Table1
With Sharepoint views we can now easily see history and totals, or see which products are currently in which stage... well pretty nice, and helpfully to us.
Here is my design problem, the purchase department buys and sells not on a per item basis but in large batches. So for example they buy in 150 'raw' machines on day X. Then we work on them till they got the status 'ready' as in table3 and then they sell 120 machines. (so we would have 30 in stock).
I could create a simple 'purchase-and-sell' Table4 which stores how many items are bought/sold on a date and with some totals, but I cannot think of a way how to combine that with my transaction list. As production as logged in the transaction Table3 <-- should stay in sync --> then with purchase Table4.
Ideal (i think, I might be wrong here), would be if I could create 'empty records' to be filled by production people, in the transaction Table3, and with a filter on 'ready' they could sell X available items at once from available items, and those then get a new status of 'delivered'.
- Problem one with this, adding empty records (purchase 150 raw products), as now the product table has set unique records for name and serial number empty ones cannt be created.
- problem two, how can one change multiple item status at once (like sell 120) ?
I'd like to stay as close to 'normal' Sharepoint 365 as possible; as it is hosted i prefer not to create custom C# /C++ code. Preferable the logic design would be based upon methods that are in sharepoint 365.