Inventory Management

[Modesto]

Hello everybody,
I sell pet products and am making an app for my own use. I need to control the stock of each customer and each product like this:
Customer B Product 1 Stock X
Customer B Product 2 Stock Y
Customer C Product 1 Stock Z
Customer C Product 2 Stock K

I have Customer, Product, Purchase and Sale tables. In addition the products have Categories.
The problem is that my customers don't register sales, so I have to go to the store and count each product and calculate what he sold by the difference of what he bought.
Can anyone help?
Thanks in advance

[SkrOYC]

---

@Modesto wrote:

The problem is that my customers don't register sales

---

[Michael_at_Graz]

If you track the stock level from your sales partners by visiting and your sales to your partners, you can use a virtual column with a SUM(SELECT function, to get the difference. Or you make a additional table and let a bot or action copy your sales from every table, to get the difference.

Like this:

• Invoice Entry on Table Order_ITEM: Customer A, Product A, qty 10
• Visit Entry on Table Visit_ITEM: Customer A, Product A, Qty 5
• The Difference is the actually Stock level from your customer: Qty 5

I dont know your exact table structure, but maybe it helps you, if you take a look at our used function:

This formula in a virtual column [VAbgang LIE] count our saled and used products to our customers and intern from many tables, based on conditions, for one branch from us:

SUM(
SELECT(
Artikel Intern[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale] = "LIE",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)
+
SUM(
SELECT(
Artikel Daten Operating[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale] = "LIE",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)
+
SUM(
SELECT(
Artikel Aufträge[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale] = "LIE",
[Lock] = "Ja",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)
+
SUM(
SELECT(
Artikel Filial Bestellungen[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale Abgang] = "LIE",
[Lock] = "Ja",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)

This formula in a Virtual Column [VZugang LIE] tracks the items we bought from our supliers from many tables, based on conditions for one branch:

SUM(
SELECT(
Artikel Rechnungen[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale] = "LIE",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)
+
SUM(
SELECT(
Artikel Filial Bestellungen[Menge],
AND(
[Artikelnummer]=[_THISROW].[Artikelnummer],
[Filiale Zugang] = "LIE",
[Lock] = "Ja",
[_THISROW].[Bestandsverwaltung] = "Ja"
)
)
)

And this formula in a virtual Column [VBestand LIE] calculate the difference and tells us the stock level for one branch:

[VZugang LIE] - [VAbgang LIE]

You can easy change the conditions and to your product categories, ad tables to your formular.

we use this formular in our articel table, to get the actualy stock level for every product.

Maybe it helps you.

Nice greets from Austria

[Tommy62 · Accepted solution]

Schauen Sie sich dieses kurze Video an, es könnte hilfreich sein--> Einfache Bestandsverwaltung mit mehreren Filialen