How to solve this problem without table (full outer) join?

[yasinaydin]

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.

[Joseph_Seddik]

Welcome to the community!

I've read your post a couple of times, but I'm not sure what is that you want to achieve, so correct me if necessary. 

I understand that you have the Inventory table where all your products are listed, but at the same time deliveries can be performed between sites for products that are not in the Inventory. 

If my understanding is correct, then what you have to do is to make the "Product barcode" as Key to your Inventory table, and in all other tables make this column a Ref to Inventory table. In this way, when a user creates a Delivery record through a form, he will have the option to choose an existing product from the Inventory or to create a new one if needed. The newly-created product will actually be added to the Inventory table, through the Delivery form, thus you'll not be missing any products in your Inventory table.

You might need to read this:

References Between Tables | AppSheet Help Center 

[yasinaydin]

Thank you for your answer and sorry for the unclear question.

I know how to use references and I used Ref in all tables to point Key values of other tables. For example, my inventory table is like this:

Let me fully demonstrate it with an example:

My tables:

Product table:

Inventory table:

Sales table:

Purchases table:

Delivery table(I have a form view that is showing reference list as input for product barcode and location barcode, so they are chosen from other tables):

So looking at the data above we see that there is no sale or purchase. Let's go over just the delivery example. Here we have products 1 and 3. We can show how many are in which location by using the inventory table.

The resulting inventory table I have is this:

But I want it this way:

X:

3 - 45

1 - 43

Y:

1 - 57

3 - 55 (because we delivered from X location to Y)

But since the product barcode 3 is not in the table inventory (as a row) I am unable to show it. Normally, I would join tables in the normal backend but here it is not possible. I am not sure how to make it also have those deliveries counted in this view. 

As you can understand the problem is that product 3 is not in the inventory table so it is not shown as a row but I want to use both delivery+sales+purchase tables(only delivery table in this case) to calculate current stock per product per location.

I hope its more clear.

[Joseph_Seddik]

Thanks I understand. The question is: how does the Deliveries table get filled with new rows?

[yasinaydin]

I used the default "Form view" to create input to the delivery table. And my columns are like this: 

where locations are referenced from the location table's key and product barcodes are referenced from the product table.

[Joseph_Seddik]

Thank you. So you just have to apply the solution I proposed in my first comment. I can see the "Product Barcode" is already a Ref column (normally to Inventory table), so your users should only select from the list or add the new product to the Inventory table, from the Deliveries form. 

You can force this by putting the following expression in the Valid if  and Suggested Values fields of the "Product Barcode" column in Deliveries table:

Inventory[Product Barcode]

[yasinaydin · 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.