LinkedIn
Twitter
Hi,
I am trying to build an application that is capable of tracking the stocks of products with the delivery, sale, purchase features. So there are many locations and in all of them, they can sell and purchase and send products to other locations.
In my product table, my columns are like this:
| Product Barcode | Product | Price | Type | Image |
inventory table columns:
| Product Barcode | Location Barcode | Initial Stock |
And in my delivery table, my columns are like this:
| Timestamp | Product Barcode | From Location Barcode | To Location Barcode | Quantity |
In my sales table, my columns are like this:
| Timestamp | Product Barcode | Location Barcode | Customer | Quantity |
I track my current stock with this virtual column in the inventory table:
SUM(SELECT(Purchases[Quantity],
AND([Product Barcode]=[_THISROW].[Product Barcode], [Location Barcode]=[_THISROW].[Location Barcode]))) +
SUM(SELECT(Deliveries[Quantity],
AND([Product Barcode]=[_THISROW].[Product Barcode], [To Location Barcode]=[_THISROW].[Location Barcode]))) -
SUM(SELECT(Sales[Quantity],
AND([Product Barcode]=[_THISROW].[Product Barcode], [Location Barcode]=[_THISROW].[Location Barcode]))) -
SUM( SELECT(Deliveries[Quantity],
AND([Product Barcode]=[_THISROW].[Product Barcode], [From Location Barcode]=[_THISROW].[Location Barcode]))) + [Initial Stock]
I want the have a view where I can see all my inventory for each location. But since we can not join 2 tables together I cannot get the products that are not in the inventory table which are delivered from X to that Y.
E.g.
Inventory table:
Delivery Table:
If I create a view with a data source inventory table the product with "product barcode" = 3 is not shown in the view since that product is initially not in the inventory.
Is there a way to solve this issue by changing the structure (table designs) or some other way? Normally I think of joining tables with product barcode reference but it is not possible here and I need to use locations too.
Thank you.
Solved
1 ACCEPTED SOLUTION
Thank you for the idea. I tried what you said but It didn't work. But after your suggestion, I thought of adding a new behavior to my app where the delivery button has additional behavior of checking if a product exists in a location (checking from inventory), and if it exists then we don't change anything in the inventory table but if it doesn't exist we add it to the table with 0 initial stock:
Behavior:
Only if this condition is true:
NOT(IN(CONCATENATE([_THISROW].[Product Barcode],": ",[_THISROW].[To Location Barcode]), SELECT(Inventory[_ComputedKey], true)))
After creating this behavior I added it to the form view in my UX section:
And it works!
Thank you so much for helping.
