Add Rows to related list using a dashboard view

[Bob83]

I hope someone can help me, I've been stuck on this problem for a few days!

Simplifying (we are in the context of a management system for restaurants):
- I have the "Products" table and the "Orders" table.
- Since the relationship between the two tables is "many to many", I created a third "JOIN" table, with inside it the references to the keys of the two previous tables, and, in addition, the "Quantity" field.

- By doing so, when I create a new instance in the "Orders" table, I automatically find, in the "Ordini_Detail" view, the Related List of the "JOIN" table, with the famous "Add" button that allows me to add Products to that specific Order, through a dedicated form. And so far so good.

The question is:
- I think having to add Products to an Order using the dedicated form that Appsheet automatically makes available is rather inconvenient, in the context of a restaurant.
- I would therefore like to be able to add elements to the "JOIN" table without going through the "Add" button, but through a dedicated view of the "Products" table, where, for example, by clicking on a "+" button associated with each Product, it is added to the Order we are handling.
- To do this, created a "Product Selection" dashboard view, which contains the "Orders_Detail" view and immediately below the "Products" view.

- I created a "GoTo Product Selection" Action, which applies to the "Orders" tab, of the type "App: go to another view in this app", specifying as target "=LINKTOROW([Order_ID], "Product Selection")" In this way, clicking on any order opens the dashboard view "Product Selection", with the first view "Orders_Detail" already filtered on [Order_ID].
- I then created an "Add Product to Order" Action marked by the "+" button, visible on each product of the "Products" view, which is the second view of my dashboard. This action is of the type "Data: add a new row to another table using values from this row", the "table to add to" is obviously "JOIN". This action should add the selected product to the JOIN table relating to the order preselected in the first view.

The problem is:
The "add Product to Order" action requires the columns of the "JOIN" table to be populated. I don't know how to retrieve the Order_ID of the first dashboard view that I need to populate the "Order_ID" column of the "JOIN" table.
The two views appear to be unrelated.
Instinctively I would like to populate the column with "Orders[Order_ID]", but I get the error:

In summary, by pressing the "+" key of a Product in the "Products" view (second of the dashboard), I would like to add that specific product to the "JOIN" table, relative to the specific Order_ID specified in the "Order_Detail" view (first of the dashboard).

I hope I explained myself well, otherwise I'm ready to give clarifications!

Thanks!!

[WillowMobileSys]

I believe you may have over complicated the data structure and mis-interpreted the relationships - which is why you are struggling.

In an Order System, there are 3 main tables - 1) Orders  2) Order Details and 3) Products

Your join table is really the Order Details table.  This is where you keep the Quantity, Price and Order subtotal information. 

You do not need the "Join ID" column. There is not a many-to-many relationship between Orders and Products.  Instead, there is a one-to-many relationship between Orders and Order Details.  AND there is a one-to-many relationship between the Product and Order Details tables.

The Problem??...

I do understand that the standard method of adding an Order Details record is inefficient when you only have a detail or two you need to include with each Order Details row.  I am not clear on how you are presently proposing to quickly add the Products to an Order.

---

@Bob83 wrote:

The problem is:
The "add Product to Order" action requires the columns of the "JOIN" table to be populated. I don't know how to retrieve the Order_ID

---

This seems to imply that you need to click the action button each time you add a Product to the Order.  I don't understand how this is better than the standard Add button that AppSheet automatically sets up.

A Potential Solution

The typical way I have overcome the "add-one-product-at-a-time" syndrome.... is by including on the Order Form, a multi-select list of the items to be included.  This is done by adding one or more additional "helper" columns - the most important of which is a column that provides the list of Products to chose from.  This column is part of the Order entry where a user selects all of the Products to be included in the order.

Then on Save of the Order Form, I automatically, with the use of actions, add the Order Details rows with the associated Order ID and pre-populated with an item from the chosen list of Products.  This takes a series of actions to accomplish and there are multiple posts in the community on how to do this.  Lookup "looping actions".

For quick update of the Quantity, and any other Order Details row information, I present the automatically generated Order Details rows in a QuickEdit Table View.  A QuickEdit table allows for editing of all the rows at once and then a user can save all changes with a single Save button press.

Afterwards, if a user still needs the ability to add a single product, then you can just fall back on the standard Add button AppSheet provides.

I hope this helps!

[rohankhan]

yes you are right from my point of view

[Bob83]

WillowMobileSys thank you first of all for the reply!

I'll try to explain myself better, forgive me but I'm taking my first steps with Appsheet!

---

@WillowMobileSys wrote:

There is not a many-to-many relationship between Orders and Products.  Instead, there is a one-to-many relationship between Orders and Order Details.  AND there is a one-to-many relationship between the Product and Order Details tables.

The fact that there is a "one to many" relationship between "Orders" and "Order Details", and that there is a "one to many" relationship between "Products" and "Order Details", does not imply that there is a "many to many" between "Products" and "Orders"?
In practice, my "JOIN" table I think is the same one you call "Order Details", or am I wrong?

---

@WillowMobileSys wrote:

You do not need the "Join ID" column.

---

In the "Order Details" table, in addition to a REF field referring to the "Orders" table, a REF field referring to the "Products" table, and any "QTY" fields, or price subtotals .... it is not necessary identify each record with a key? So through an "Order Details ID" field? Because otherwise the key of the "Order Details" table remains the [rownumber] and AppSheet does not recommend it.

Regarding the question of the Add button:
My idea was to be able to add Products to the "Order Details" table by not going through the form made available by AppSheet when we click the Add button, which, while managing it with multiple selections (which I've heard of) still remains a form visually too simple.
Instead, it would be nice to be able to do the same thing, for example, with a dashboard view that places "Order Details" next to "Products", something like this:

I think this entry screen is more beautiful and practical than the one available with the Add key, it allows you to see the photos of the Products, group them in different ways, assign actions etc.
In the "Products" view, on the right, each product has a "+" button, which should automatically add it to the "Order Details" table, relating to the specific "Order_ID" opened in the left view.
And my problem was the Action associated with the "+" key.

Specifically, what should I enter in this field?

I don't know where to retrieve the "Order ID" related to the open view on the left.

For now I solved it with a very inelegant solution  😅 which it is not necessary to talk about now, but I would like to know your point of view!

I hope I didn't misunderstand something!
Thanks everyone for the help!

[WillowMobileSys]

---

@Bob83 wrote:

does not imply that there is a "many to many" between "Products" and "Orders"?

---

Conceptually, but not physically. 

For example, consider relationships between Doctors and Patients.  A Doctor can have many Patients and a Patient might be seeing many Doctors.  This is a direct many to many relationship. 

But now consider Patient visits to Hospitals.  On each visit the Patient sees a Doctor and it could be a different Doctor on each visit but that is part of the Hospital visit detail. The Doctor info could be removed and the Patient -> Hospital Visits relationship still has meaning.  In this case there is NOT a direct relationship between the Patient and Doctor.  

This is the same for Order and Order Details.  Product info is only indirectly related to the Order.

---

@Bob83 wrote:

it is not necessary identify each record with a key? So through an "Order Details ID" field?

---

Yes, sorry you are correct!  I mis-read your table columns.  Join ID is the key for that table so your structure can remain as is.  

---

@Bob83 wrote:

I think this entry screen is more beautiful and practical than the one available with the Add key, 

...
And my problem was the Action associated with the "+" key.

Specifically, what should I enter in this field?

---

I see.  By nature of the Product visible in context on the screen, you are hoping to grab the correct Order ID to automatically assign to an "Order Detail" row upon tapping the Product "+" button.

This is a very nice idea!   I love it!

AppSheet does not provide a way to grab details from a visible view.  However, I think you can achieve this!!

What you need to do is "remember" which Order is opened in the Order Detail view.  This means recording it somewhere.  The way I like to handle this is with a "utility" table where I can temporarily record row information I need access to within a processing stream and then delete it when I'm done. 

The idea is this...use a custom Group action to open the Order Detail view when the Order row is selected to be opened.  In this Group, the first action would record the Order ID info in the utility table.  A second would physically open the view.  Now when you click the "+" to add a Product, lookup the Order ID from the utility table to assign to the Order Detail row.

A slight issue arises when you want to remove the Order row from the utility table.  You can't stop a user from using the back button to close the view and you cannot capture that action to perform a Delete.  I might then simply add a Delete into the custom Group action to remove any pre-existing Order rows inserted into the utility table.

I hope this helps!!

[Bob83]

Thanks so much, it helped me a lot! Knowing that I can't take data from a view will save me a lot of time from trying to do so! 😊😊

The solution you proposed is exactly the not very "elegant" one I was thinking of adopting!
Specifically, I created a "Users" table where the emails of the app users (who may be more than one at the same time) are stored in column A (key) and in column B the "last Access Order_ID" to which they made the access. In this way, as you were saying, I will be able to retrieve the "Order_ID" from this column B to use in the "+" Action of my dashboard. And so far so good.

Now, however, two sets of linked problems arise:
1) How do I load the "Order_ID" into column B of the "Users" table? I know that there is an action to modify columns of the same row, or another to add a row in another table.
However, I would need a mix of the two: modify a column of a specific row in another table (specifically, I think, with an action on the "Users" table saved among the actions of the "Orders" table). How can I do?
2) Problem 1 solved, I guess I will have to be extremely careful to press the "+" button only after our "last Access Order_ID" support field of the "Users" table has been synchronized and updated.
Otherwise the "+" button will either not work or will work incorrectly.
Do you think there is a way to avoid this sync problem?

Thanks again!

[WillowMobileSys]

I would recommend keeping this "utility" data separate from your other app data.  That way when things change (or "utility" needs expand) you don't run the risk of mucking up the critical app data.

However, if you insist on using you existing table  to store the information, it requires using a a couple of actions - one to transition from Order processing to updating the User table (this can also PASS the Order ID value) and a second action to update the User row.  AppSheet does provide the INPUT() function as a way to pass values between actions.  Note it is considered Beta and apparently not all apps have access to it.

If you use a separate table, you will only need the "add a row to a table" action and then a Delete action.

[Markus_Malessa]

---

@WillowMobileSys wrote:

AppSheet does not provide a way to grab details from a visible view.  However, I think you can achieve this!!

What you need to do is "remember" which Order is opened in the Order Detail view.  This means recording it somewhere.  The way I like to handle this is with a "utility" table where I can temporarily record row information I need access to within a processing stream and then delete it when I'm done.

---

I am amazed that there is still no way to implement this kind of functionality by default within Appsheet. Seemingly there are a lot of frameworks out there that have a 'SelectedRow' capability, and based on Appsheet's own INPUT() function and the ability to select multiple rows from a table and perform an action on the selected rows it would seem that the basic building blocks for this already are present within Appsheet. Yet there is nothing currently available where the selected row or record can be stored as a variable and reused in other processes (looking at automations here too, where 'return value' does not allow additional subsequent steps after). It seems like some of the most basic functionality of applications require very lucrative work arounds, counts for looping and many-to-many relationship handling too.