POS System ( Sales,Income,Expenses,Stock,Supply)
Hello,
I have been working on a POS System all created in Ninox,
What I'm trying is that when the sale of 1 product happens (Sales Table) the product is deducted from the Stock table and money added to the Income table.
When I buy from the Supplier the number of products is added to the Stock Table and the money is discounted in the Expense table
Would appreciate the help a lot!
30 replies
-
Hello Bruno,
I'm not sure what your question is. Is it "What do I have to do, to create follow-up actions?" ?
It would help to see your Sales Table and to now how you realise a sale. Is there a button, do you create a new sales entry "by hand", ...?
Please tell more about the sales mechanism and then I can tell you how to create follow-up actions.
-
Lars
Hello Lars,
Yes, to create a follow-up action.
Sale is realized by hand.
My shop operator/salesman by hand whenever they make a sale ,they will add it by hand with the details needed and I want to achieve the follow-up actions
-
-
Sound like you create a record in your Purchases/Supply table and you want the Stock table to aggregate data from Purchases/Supply. I'm guessing that you have a reference field in Purchases/Supply to Stock. If not then how are the two tables related?
-
Fred
Yes, I want the stock to be linked with sales and purchases and the money flow of sales and purchases to be displayed in income and expenses. Sorry if I'm not clear with the explanation
-
-
Can we see an image of your data model please.
-
John Halls
-
John Halls p.s not the best, I have been trying
-
-
In the Stock table why not have three fields. A ‘Stock In’ field that increases every time you buy new stock from the supplier, a formula field that calculates the Stock Sold by counting the products sold in the Sales table, and another formula field to show ‘Remaining Stock’ by using the formula ‘Stock In’-‘Stock Sold’ Then every time you sell something the Stock table is automatically updated. Use a formula field in the Income table in the same way by calculating the value (sum) of existing sales from the Sales table.
-
Paul Chappell
Hello Paul,
This seems like a good idea, but how can we do it like this for many products?
-
Bruno Skëndaj Presumably you have a Stock record for each type of product? So every time you make a Sale you know which product is being sold. So the formula fields are in the Stock record for each product. Your formula does a Count() for each product. 'Stock In', 'Stock Sold', and 'Remaining Stock' always relate to specific Products because the Stock table is based on each type of product. I may be missing something, but I'm can't see the problem. You Purchase stock. For that there is an Expense. For each Purchase there is a specific type of Stock (Product), so 'Stock In' is just a count() of the purchases. You then sell a number of Products/Stock (or different types of Product/Stock) in each Sale. And each Sale record relates to a Customer Order. Which should relate to a Customer table - although you don't have one of these. The Data Model looks fine. The 'Stock' table just needs 3 formula fields to count new stock (from Purchase/Supply), sold stock (from Sales), and Remaining stock is the difference between the two.
-
Paul Chappell That's exactly what I want to achieve.
I was thinking of giving each product an ID so this way was better for multiple products
In theory, I have the idea in my mind but I'm really struggling to create this all linked and make everything synced
-
Bruno Skëndaj Look at the Ninox example database "Inventory". It is very similar to your requirements. You should be able to get some ideas from that.
-
-
This is where the use of reference fields come in. We don't know the names of those fields so you will have to adjust the following formulas.
For StockIn (I use mashed up words so I don't have to use single quotes in formulas) it could look like:
cnt('Purchases/Supply')Yes it is that simple. This is assuming you made a reference field in the Purchases/Supply table to the Stock table and kept the default names. In Ninox you always make the relationship on the many side.
Thinking out loud, it looks like you might want to link Sales to Purchases/Supply as I would think you want to know which specific item you sold not just an article. For example you need to know you sold a specific iPhone 12 to a customer not that you sold an iPhone 12.
If you don't care which specific article is sold and you have a reference field to Stock then you can do something like:
cnt(Stock)If you do create a link to Purchases/Supply from Sales then it would change to:
cnt('Purchases/Supply'.Sales) -
Fred Paul Chappell I have created the database for the income expenses and reports all that, from ninox inventory template I have edited it and have a well-working inventory, How I can link these 2 together? as databases it's not possible so I exported the tables of income expenses as .csv files and imported them to my inventory database, here I'm struggling to link them together, would be great if someone would help me with this
-
these 2 groups need to be linked, the 1st bracket inventory from template of ninox I have edited it and is working as I wish, the second bracket includes tables with incomes and expenses, I need to make automation of these tables so that when I sell something the product is -1 from stock for example and money being added as income, in the other hand when I purchase from my supplier the product +1 and the money taken as expense.
I hope I'm clear, I really appreciate all your feedback :)
-
We have given you some suggestions with the stock issue. Have you tried any of the suggestions?
If this is a test DB, can you upload a copy of the DB to this post so we can take a look at it?
-
Tried to do the suggestions, Was not able to make it work
-
my bad
-
I haven’t yet looked at your DB, but why do you need Income and Expense tables? Your Sales already contain the value of each sale and Purchase presumably contains the cost to buy products. So you just need views of Sales and Purchases with the total of the relevant columns to give you income and expense values. No need for extra tables.
-
Looking at your DB, it seems like you have done a good job with the stock part. Your 'Inventory Herne', 'Inventory Bergkamen', and 'Inventory Beckum' all do what you want. It shows you the current stock of articles based on each where you bought them from and subtracts the number of articles sold. So I'm not sure what you need done here. In fact you are using the power of the reference fields to gather the data and NOT doing select statements. Good Job!
I'm not sure what you want to do with your Income tables. You have a date field and a yearmonth field. Can you expand on what you are thinking here?
-
Fred I really have worked a lot in this, from 0 knowledge thanks I really appreciate the good words.
The idea is as I said to Paul
at the end of the day, every day, I want income and expenses generated in one table only date income and expenses, and day after day the list goes on.
that table "Taglicher Kassenstand" is a daily report, that I manually want to complete with the information with the income/expenses table. Because I want to print it every day.
I have made a mistake making 2 different tables 1 income 1 expense...
This is the idea
-
Bruno Skëndaj Don’t think of Income and Expenses as being daily totals and needing separate tables. You only need a single table called something like ‘Transactions’ (or ‘Money Items’). All this table needs is a Date, Amount In and Amount Out. When you create a Sale all you need to do is create a record in ‘Transactions’ consisting of the Date and the ‘Amount In’ of the Sale. Do the same for every Purchase, except recording to the ‘Amount Out’ field. So this way your Transactions table is just a continuing list of money in and money out. You can then create a View of the Transactions table grouped by Date with the Total of the Amount In field and the Total of the Amount Out field. And to finish off create a Formula field for Balance (Amount In - Amount Out) and then Total that. You could also create a formula field for ‘Month’ and group by that for monthly totals. Your next problem will be to ensure nobody changes any of the Sales or Purchase records because you will need to delete the Transaction record and create a new one if the amounts have been changed. In fact, it is bad practice to allow accounting records to be changed otherwise your history (or audit) will be affected.
For reporting you have so many choices with the single Transactions table. Print a detailed report of all daily items. Print daily, weekly, or monthly totals. Or create a Chart showing how your business is performing over time… and your profit!
-
Content aside
- 5 mths agoLast active
- 30Replies
- 721Views
-
7
Following