Pranam
We use MS Access for our inventory at present. For “Sales orders” we have a form (related to a customer table) with a subform (related to a product table) where we can add product name (from a drop down menu), amount, price correction.
Suppose we have an order from a Customer 1:
2 x product 1
3 x product 2
1 x product 3
How one can build it in SeaTable ?
As far as i know we have no subforms/subtables here.
I was looking through several templates in SeaTable but couldn’t find an answer.
The only way, which comes to my mind is to create every time for a new order as many rows (records) as products/items we have for this specific order like in the picture below.
if you are looking for a structure, how to build something, I would recommend to search how you would build such a solution with a SQL-database. This could then easily be adapted to SeaTable.
I just search for orders how database design at Google and here is the result from
Order table
orderid int primary key
totalprice int
orderstatus int
Order Items table
orderitemid int primary key
orderid int foreign key
productid int
quantity int
This is the way I would build a SeaTable structure. You have a table with the orders (one line = one order) and another table with the orderitems (one line = one item of one order).
But give me some time, I will build a short example and post screenshots here.
thank you for the fast response and valuable screenshots.
I’ve built it the way you made and after a while i realized that the structure is exactly same with the structure of the tables in our Access…
Can we take one step forward ?
In Access the “belongs to Order” column in “Order Items” of your table is generated automatically (as it is a sub-form). Is it possible to automatize this process also here ?
I.e. First of course we would have to create new order to get an order number.
After selecting a Product (in Order Items tbl) and when the column “belongs to Order” is empty, automatically copy the newly created “order number” (from Orders tbl) and record it to the current row.
By this we could save a time and avoid potential mistakes for orders with multiple items.
I presume this is possible with the JavaScript, but to write the code is beyond my limited knowledge.
Or maybe there is also another simpler solution ?
Thank you.
This is great. It’s also great because you are developing it together with a customer. Perhaps, once it is fully developed, you could add it to the ready-made templates you offer so other customers can benefit?
Hey Kriya,
SeaTable is that powerful that I see many possible solutions. Allow me to explain the different approaches and then choose the one you like most. In general, I would group the table Order Items by Order-ID and use the color options of SeaTable to show problems or missing values.
Now after this general recommendation. Let me explain three different approaches to make your life easier.
Approach 1:
Just have a look at this animated gif. You can create an order item from the table Orders. Just double-click on Order Items, then enter something that does not exist and create a new order item. The Order Item then already has a the Order-ID in it.
Approach 2:
Use a button to create a new line at the Order-Items. this new Order Item has not Order-ID in it. Just copy-and-paste the Order-ID to the new item. Copy-and-paste works easily with STRG+C and STRG+V.
Approach 3:
my last approach uses an automation and is therefore only available for teams with an Enterprise Subscription. First you have to add a new text column to the table Order Items. This column can be hidden later on and will hold the order-id.
Here is what my automation does:
the automation runs every time a new row is added to the table Orders
the automaton has to actions:
Action 1: Add record to other table: Write the Order-ID to the hidden new column
Action 2: Add links: Link together the order item with order.
I hope you could follow my explanations.
Christoph
Fantastic !
Thank you for giving this final solution and for elaborate explanation.
Now everything is clear.
i need to accustom a bit with SeaTable
It is again confirmation for me that SeaTable is a very good choice from many points of view and one aspect is helping others like in this example.
Same solution can be applied to Purchases Orders and from this point it is very close to create practical Inventory Management template with tracking sales and purchases, sending emails with invoices etc.
If by chance it would be manageable for me i will be happy to contribute and share such template in appropriate category.
P.S.
Christoph, gif in approach 3 is same with the previous one.
