How to have OrderDetail be a part of 2 Parent tables?

[Just_a_noobie]

I am trying to create a distribution app, where there are two types of orders: Vendor Orders and Customer Orders. I have no issues generating the Customer Orders, but is having trouble generating the Vendor Orders.

I want to make it so that the Vendor_OrderDetail will be made up of:

1. A product selected from an Open_Customer_OrderDetail slice
2. Based off that product, generate the total Qty to order with Vendor from the same slice, using all Open_Customer_OrderDetail with the same Product_ID

That would make the Customer_OrderDetail a part of both Customer_Order and Vendor_OrderDetail. However, Appsheet only allows me to select one table when selecting "Is a part of?" 

How would I work around that?

[Suvrutt_Gurjar]

Yes , at a time a table can be "is part of" only one table.

It is not exactly clear why you wish to have the child table as "is part of" both the tables. But you may want to take a look at the below referred sample app for ideas.

In the sample app, the Products table summarizes all the quantities that have been requested for various orders for that product under the column [Total Requested]. You could possibly use "Products" table with some tweaks such as adding vendor names enumlist column for vendor names selection who supply that product, adjusting quantities/ inventories  for delivered orders , open orders etc.

Order Capture How-to

[Just_a_noobie]

For this app, the user wants to only order the amount that is needed for his customers, so ie.

Customer X orders 3x Product A
Customer Y orders 2x Product A, and 2x Product B

So he will then place an order with a Vendor to order 5x Product A, and 2x Product B.
He also wants to be able to track that the 5x Product A has a breakdown of 3x from Customer X and 2x from Customer Y, rather than just have a total sum number.

So once the customers create their order, I wanted to be able to create the order for the vendor using the customer orderdetail, if that makes sense. 

[JPAlpano]

The way a see it, the order to the vendor is a totally separate record from the order from the customer.

Is it really necessary that both orders refer to the row from the same table?

Why not just create a totally separate record for the vendor order detail?

You can do an automation or an action (or a combination) that creates a record in a separte vendor order detail table copying from the recently added customer order detail table. 

The only benefit of the "Is a part of" is that if the customer order is deleted, then the order details of that order gets deleted with it.

You wouldnt want to delete a vendor order and delete with it the order details of that customer, wouldn't you?

You'll end up with a customer order record without any order details.

[Suvrutt_Gurjar]

All that is available in the shared app. Of course you will need some tweaks. If you take a look a the Products table view's related table generated by the column  [Related Order Details] as shown in the screenshot below, it gives the order details by Order number and quantity from the corresponding order detail is obviously there. You can get the customer name from Order number and group by customer name.

[MultiTech]

---

@Just_a_noobie wrote:

Appsheet only allows me to select one table when selecting "Is a part of?" 

---

Just throwing this out there:

• The Ref type does NOT require that the "is a part of" is turned on

You can have any number of reference connections to a record

If you wanted to be able to categorize your data into different pools, the very word "cross-reference" comes from the fact that there are X number of additional reference connections for the record - not just the primary connection (of which the record may be "a part of").

[JPAlpano]

This is so true. I seldom use "is a part of" in my references as most of my tables are supposed to be independent of each other (unlike the order and order details scenario) bec each of the tables are updated by different teams and the work logs need to remain intact for both teams. 

I even have setups where A prent has a child and a grand child, and the grandchild has reference to both its parent and the grandparent separately. 

[MultiTech]

From the primary data collection table from an app of a client of mine:

Fun fact:  [Photo_Parent] is self-referencing

Everything you see in this screenshot is a ref (except the top column); the Enums are also soft-references, so I can still pull data through them.  But I only need to see this item on certain levels of the DB, so those are the only ones that have a hard-ref.

[JP-AN]

Hi, i have this question about this sample app...
I want to know how i can achieve this spreadsheet formula, but with an appsheet formula...
Or is it not possible, and thats why it is done as a spreadsheet formula??

Cause i am having some trouble in summing receipts applied to invoices. I have a table of "receipts" wich is "part of" the "invoices" table... so i want to sum the receips for and show a balance column (invoice amount -  sum of receipts amount).... but i see that this is done with a spreadsheet formula here, if i am not mistaken. 

Thank you!

[Marc_Dillon]

Because that formula sums up multiple other records, and so by default it won't get auto-updated if used as an App Formula in a real column. If used as an App Formula in a Virtual Column, it will get auto-updated. Its use in a Spreadsheet Formula here is just another option, as spreadsheet formulas are also updates constantly.