Import CSV into child table

[Paras_Sood]

Hello appsheet community!

I have a use case which is quite common for everyone:

I have created an order management system, where a customer can have many orders and orders can have many lines of product.

When a new order is created, I want the users to be able to import a csv file with all of the order details to be automatically filled up in the order details table (child) for that particular order (parent)

Is there any way to do this from the order view? Otherwise what is the best way to have a csv import (order details) for an entry in the parent table (order)

Right now, the action button is only visible for me when I have at least one entry (order detail) for a particular order.

I tried to look for something similar in the community posts, found a few posts with sample apps shared but I couldn’t really find what I’m looking for. Thanks in advance!

[Paras_Sood]

No one? lol

[TeeSee1]

Assuming you have in your CSV the order ID field with the proper value populated, you can call the CSV import action defined on the detail table from the order view by creating a "Date: Execute an action on set of rows" action on the order table similar to below.

Since you are not really taking action on any specific detail rows (import CSV is a table action, not a row action), you can just set an empty list in the referenced rows field.

I do not think you can automatically populate the order ID field of the item table rows as you import CSV using the order ID of the selected order row.

Others may have better suggestions.

My two cents..

[Paras_Sood]

"Since you are not really taking action on any specific detail rows (import CSV is a table action, not a row action), you can just set an empty list in the referenced rows field."

Can you please elaborate on that? I think I'm missing something

[TeeSee1]

My understanding is as follows..

Actions can be applied to specific rows (ie change the value of some column(s) in a row(s)) or at the table level depending on their type.

CSV import action is executed at the table level (not affecting any existing rows) so specifying rows (Referenced Rows) in this case has no relevance. Just need to have something which will be 'ignored' anyway.

Some actions at the table level (not a row-level action)...

[Paras_Sood]

What would be a better and efficient way to add order details to an order management system?

[TeeSee1]

Seems like you are already collecting orders outside your AppSheet app (I guess that is why you have order details in a spreadsheet).

So just populate your Order ID in the spreadsheet (CSV) and upload.

That seems reasonably efficient to me.

Or maybe you can just use the CSV file and use it as your data table source.

It is kind of difficult to answer your inquiry without more details on how you are capturing orders in the first place.

I believe the more information you share, the better chances of getting more suggestions from the community.

[Paras_Sood]

Okay I'll put out my use case as below:

Right now, we maintain a master sales list in which we keep a record of all the orders that we receive. We receive orders through emails and through B2B EDI documents which we manually enter into this excel spreadsheet. The spreadsheet is highly inefficient because it does not give us visibility on order status. I want to get rid of the excel and incorporate this into google appsheet.

There should be the following key features in the app:

1. It should be easy to add orders. If possible, the users who import orders through EDI usually export the order to an excel sheet. I want them to be able to upload this excel sheet to appsheet or bulk update orders somehow.

2. I also want the functionality for partial fulfillment of orders. When something dispatches from stock, it should be deducted from a particular PO and the outstanding amount should display for that order along with a history of what has been dispatched against that order already.

I hope this makes sense.

[TeeSee1]

@Paras_Sood 

What you want to accomplish makes perfect sense.

The issue is that you need to really design the whole thing from scratch and I am not sure if an inquiry that requires this level of effort can be answered in this kind of Q&A forum.

Even though AppSheet is a NO Code solution, you have to have an understanding of how to design database tables (normalization, primary key, referential key, etc) to implement a solution in a robust way along with its 'dialect' on how to realize certain function (for this, if you have specific questions, the community is a great place to seek advice).

Unless you have someone who has experience and skills mentioned above, I would recommend that you ask for a professional service.

This is just my opinion..

[Paras_Sood]

appreciate your reply

#1 has been answered by you (although Im still really confused by the solution provided. maybe its just me, it went over my head)

#2 i just want a nudge in the right direction i do have knowledge of how databases work but i need an idea on how to realise partial fulfillment

[TeeSee1]

For partial fulfillment you need a table to track fulfillments related to your orders.

So in the fulfillment, you have to have a col to relate the fulfillment to the PO along with fulfillment id, date, qty, etc, the usual.

On the PO table you can use an expression in a virtual column

  [ordered quantity] -  SUM([related fulfillments].[fulfilled quantity])

to show the open quantity.

[Paras_Sood]

This actually goes a layer deeper because every order will have many items and each item might be partially fulfilled. So the entire order should be updated for every single item in that order related to the fulfillment.

[TeeSee1]

Well then your fulfillment records should be tied to your order items rather than the order level.

Also your open quantities should be calculated at the item level not at the order level.