Inventory Management - Dashboard view? Inline details from 2 different child tables

[connex91]

Hi everyone!

I am building an inventory management app.

Use case:
Stock transfer from a warehouse to another. The sender and the receiver both have the app.
The sender fills out a Stock Transfer Order form with the data in 2 tables:

1. Tables StockTransferOrders with general data like date, sender name etc and
2. Table StockTransferOrderDetails with a list of products and quantities sent

The receiver fills out a Goods Received Note form with the data in 2 tables:

1. Table GoodsReceievedNotes with general data like date, receiver name etc and StockTransferOrderID
2. Table GoodsReceievedNoteDetails with a list of products and quantities

My goal:
I would like to create a view where the user can select a StockTransferOrderID and see on the same screen inline tables for both the related StockTransferDetails and the GoodsReceivedNoteDetails to make sure that the goods received match the good sent.

My issue:
I created the following dashboard view but I have no idea how to connect the various elements.
I would like the following: When the user clicks a StockTransferOrder row on the top, display side by side the relatedStockTransferOrderDetails tables and GoodsReceievedNoteDetails at the bottom. Alternatively, the top view could be a simple dropdown list of existing StockTransferOrders.

Thank you for your help!

Faustin

This is the Dashboard view

 

Below are the tables:

 

 

[WillowMobileSys · Accepted solution]

So I see you do have Interactive mode turned on for the Dashboard.  But you don't want to use the "Inline" views - those are specialized views for use inside of Detail and Form views.  Instead, create normal Table views and place those in the Dashboard.  AppSheet will take care of the rest.

The relationship between the StockTransferOrder and StockTransferOrderDetails tables looks great and should work right away. 

However, the GoodsReceivedNotes table will need some work.  There is no physical relationship between the StockTransfer tables and the GoodsReceived tables. 

Can you describe, in words, how the StockTransferOrder table is related to the GoodsReceived tables?

[connex91 · Accepted solution]

Hi @WillowMobileSys 

---

@WillowMobileSys wrote:

However, the GoodsReceivedNotes table will need some work.  There is no physical relationship between the StockTransfer tables and the GoodsReceived tables. 

Can you describe, in words, how the StockTransferOrder table is related to the GoodsReceived tables?

---

You are right. That stems from another issue that I "resolved" using a trick that is not elegant at all:

For context, a warehouse can receive the instruction to release goods from 2 different sources:

1. A StockTransferOrder to send Products to another warehouse
2. A DeliveryNote to indicate that the Products come from an external supplier

I did not know how to manage refs from multiple tables so in the GoodsReceivedNotes table, I created 2 fields: a dropdown name SourceDocumentType and a dependant dropdown Enum that lists the IDs of the selected Document Type. In the table, GoodsReceivedNotes that number is stored as plain text.

That is why there is no physical link between the table GoodsReceivedNotes and the tables StockTransferOrders / DeliveryNotes. 

Is there a more elegant way achieve that goal?

Below is a screenshot of the table GoodsReceivedNotes showing the DocumentTypeID and SourceDocumentID fields

Below are screenshots of the case of GoodsReceivedNotes from a StockTransferOrder:

Below are screenshots of the case of GoodsReceivedNotes from a PurchaseOrder:

[WillowMobileSys · Accepted solution]

So I see you do have Interactive mode turned on for the Dashboard.  But you don't want to use the "Inline" views - those are specialized views for use inside of Detail and Form views.  Instead, create normal Table views and place those in the Dashboard.  AppSheet will take care of the rest.

The relationship between the StockTransferOrder and StockTransferOrderDetails tables looks great and should work right away. 

However, the GoodsReceivedNotes table will need some work.  There is no physical relationship between the StockTransfer tables and the GoodsReceived tables. 

Can you describe, in words, how the StockTransferOrder table is related to the GoodsReceived tables?

[connex91 · Accepted solution]

Hi @WillowMobileSys 

---

@WillowMobileSys wrote:

However, the GoodsReceivedNotes table will need some work.  There is no physical relationship between the StockTransfer tables and the GoodsReceived tables. 

Can you describe, in words, how the StockTransferOrder table is related to the GoodsReceived tables?

---

You are right. That stems from another issue that I "resolved" using a trick that is not elegant at all:

For context, a warehouse can receive the instruction to release goods from 2 different sources:

1. A StockTransferOrder to send Products to another warehouse
2. A DeliveryNote to indicate that the Products come from an external supplier

I did not know how to manage refs from multiple tables so in the GoodsReceivedNotes table, I created 2 fields: a dropdown name SourceDocumentType and a dependant dropdown Enum that lists the IDs of the selected Document Type. In the table, GoodsReceivedNotes that number is stored as plain text.

That is why there is no physical link between the table GoodsReceivedNotes and the tables StockTransferOrders / DeliveryNotes. 

Is there a more elegant way achieve that goal?

Below is a screenshot of the table GoodsReceivedNotes showing the DocumentTypeID and SourceDocumentID fields

Below are screenshots of the case of GoodsReceivedNotes from a StockTransferOrder:

Below are screenshots of the case of GoodsReceivedNotes from a PurchaseOrder:

[WillowMobileSys · Accepted solution]

As I understand it, your Document Notes table could have been created based on entries in multiple source tables.   So your "Source Document ID" column if of Text type and NOT Ref.

However, If you wish for the Interactive Mode to work in the Dashboard, the column MUST be of Ref type.  

What I recommend then, is instead of having a single column to store all source ID's, have a separate column for each one.  Then Modify your view to show only the column indicated in the "DocumentTypeID" column.

I hope this helps!

As a side note...

I noticed when looking over your table configurations, you have a Row ID column as well as a source table ID column.  I think this is good since your source ID's do NOT appear to be random but, at least, somewhat data driven - e.g "STxxxxxx" or "Pxxxxxx".

BUT, you want to be consistent in which you use as the Row Key which I would advocate be the Row ID column ALWAYS.  However, this means when you create the Ref columns, they MUST store the ID from the Row ID column for the data structure to be solid.  If you need to carry over the source table ID as well, then either insert a table column to extract it from the Ref column OR insert a virtual column to extract it from the Ref column which is useful if the source table ID could change - the VC will update automatically.

Since ID's don't change often, I prefer to use table columns so they do not become part of the Sync VC calculations.

[connex91]

Hi @WillowMobileSys 

I was afraid you would say that 😅. I have indeed created additional columns and did the show/hide of the fields, it is not as elegant as I wished but I guess it is the best we can do with the platform at the moment.

About the side note: Thank you for the advice; I have changed every Key to  RowID.

For the Interactive Dashboard, I added a VC in each Details tables that link back to the source document, this way when I click on a PurchaseOrder or a StockTransferOrder, the related GoodsReceivedNoteDetails Product appear. And I created slices of PurchaseOrderDetails and GoodsReceivedNotesDetails to show the relevant Product names and quantities.

Below is the result: I select a PurchaseOrder and I get the GoodsRececivedNote on the right and below I get side by side the products in each of their Details tables which is exactly what I wanted to do. I will do the same for the StockTransferOrders.

 Thank you for your guidance!