Create an invoice from child of multiple parents

Atlas · 11-25-2022 07:04 AM

Hello Team,

I am working on an invoicing app now. Due to my customer's business model, I must create an invoice from multiple sales. I have a Sales and Sales Details table. The invoice must include all the child records of selected Sales records. So simply, When I click on an action button for selected rows in the Sales table, it should copy all child records to the Invoice table with an automatically given invoice number.

I appreciate any help.

WillowMobileSys

@Atlas wrote:

it should copy all child records to the Invoice table

Are you implying that you intend to construct an AppSheet set of tables for Invoice and Invoice Items? Or by saying "Invoice table" do you mean a table format in an Invoice document, e.g. a generated PDF?

Atlas

Hi, Jhon thank you for your attention. I want to construct an Invoice table from the sales table with its child records. I would generate a PDF directly from the Invoice table later. I believe adding a picture for the desired Invoice table makes clear my need.

WillowMobileSys

Ok. AppSheet does have a "Bulk Processing" feature on Table views. You can take advantage of it by showing a Table view of your desired Sales rows, select the "Bulk Processing" icon (see image below) and then choosing the action you wish to apply. Maybe you have a "Create Invoice" action. When you create an action against the proper table, it will automatically show up in the "Bulk Processing" feature.

You would design your action to drill into the Child rows, add a new row into your Invoice table, copy the proper Child row details and set the necessary Invoice Information.

AppSheet will send each of the selected Sales row to your action one-by-one.

I hope this helps!

Table view showing the Bulk Processing icon

With rows selected, Click the "3 dot" menu to see more actions - if any

Atlas

Thanks, Jhon, I am using my apps for more than 3 years now. I know how to use the action on a bulk selection.

@WillowMobileSys wrote:
You would design your action to drill into the Child rows, add a new row into your Invoice table, copy the proper Child row details and set the necessary Invoice Information

That's what I am trying to do! An action to make this magic trick which pulls just the child records of the parent table. I hope I made myself clear.

WillowMobileSys

Got it! We don't always know the posters experience level.

Ok, to build this:

1) First action, "Create Invoice" is of type "execute an action on a set of rows " and selects the list of "Related Children", target table is the Child table, target action maybe called "Create Invoice Items"

2) "Create Invoice Items" is of type "add a row to another table using values form this row". It create the Invoice Item rows grabbing the value from the child row BUT also through the Child Parent Ref column you can gain access to any Sales information to carry into the Invoice Item row.

That should be it...just the two actions!

Atlas

It seems easy but I know it is not. I will do it and will update the results here. Thank you, Jhon.

Atlas

Dear all,

It should be titled "Finding the Missing Grandparent for Children and Parents" I am not a programmer or a database engineer, I am just trying to create some apps as a junior citizen developer. I made several apps that works well, and I learned to make apps by doing.

Bref, I have a situation where I cannot figure out what to do. I have parent and child tables, SALES >> SALES DETAILS. I want to add one record as a grandparent by selecting one or more than one parent record directly from the SALES table. This action should create a one-grandparent record in the INVOICE table which holds all the references. After digging in the forum for a week, I could not find the proper way to end this scenario. With limited knowledge of AppSheet, I've just thought this, to find directly from the invoice table records which sales have been made, and for each sale, which products were sold.

I look forward to more opinions, and your help to achieve this.

Sorry for the title and for bothering you with this post. Any help is appreciated if you are available to look at it.

@WillowMobileSys @MultiTech @Koichi_Tsuji @Suvrutt_Gurjar @TeeSee1 @LeventK @Steve
(no particular order)

WillowMobileSys

Ok, following up on my last post describing the two actions you need, I have setup in my test app a Sales, Sales Detail and Invoice table to help illustrate these two actions for you. I have annotated the images below to try to explain the pertinent parts. Please look over and ask any questions.

Based on my understanding, these are the only two actions you need when using the Bulk selection feature to write Sales Details info to your Invoice table.

Give it a try and please ask any questions you encounter.

Action 1) - Create Invoice - seen in Bulk selection feature

Action 2) - Add Details to Invoice

Atlas

John thank you so much for such a clean explanation and demonstration. I am sure that this will work.

From my understanding, this is meant to create every Sales Details record in the invoice table. (As per my first scenario)

After our talk, I realized that pulling Sales Details rows to the invoice table would cause duplicating the data, and that's why I rewrote the scenario. Is it possible to create one record as a Grandparent to the invoice table which holds all the references to Sales and Sales Details?

TeeSee1

Having read this thread so far, here is one way to accomplish this though it may not be what you had in mind exactly.

Create an Invoice parent table.

Invoice ID (uniqueID or whatever)
FT_NO
FT_DATE
CUSTOMER (ref to customer)
Sales Orders (ENUM LIST of Sales Orders)
Add Count (a utility column described later)
Sales Order Details (a VC described later)

Then Invoice Detail table

Invoice Detail ID (uniqueID)
Invoice ID (ref to the Invoice Parent)
Sales Order ID
Sales Order Detail ID
Product
Quantity

Here is how you create an invoice

Create an invoice parent
Pick a customer
Fill out FT_NO, FT_DATE (not sure how you determine these)
Dependent dropdown on Sales Orders based on the customer selected
Action to create invoice details

You can use the technique described in FAQ-add-row-per-value-in-EnumList to created necessary Invoice Details rows.

Control table is the Invoice Parent. You need to add
ENUMLIST/LIST of Sales Order Details to include can be created from the Sales Order ENUMLIST by
```
FILTER(
 "Sales Order Details", 
 IN([Sales Order ID], [_THISROW].[Sales Orders])
)
```
Target table is the Invoice Details table

Here is a skeleton demo. You can add more fields and tweak UX but should be able to see the basic idea.

Atlas

Hi TeeSee, this fits perfectly with my second scenario. I appreciate your reply very much. This is easy for me to use and I like it. However, for other users to make this process easier, is there any way to create an invoice record for the Invoice table directly from the Sales table with an action that includes only four columns?

The columns are FT_ID (unique), FT_Date (today), FT_No (sequential), and Customer (anyrow selected from the Sales table customer column).

And adding the FT_ID column to the Sales and Sales Details table at the time would transform the invoices table into a grandparent table. Sales and Sales Details will turn into children. I would like to know whether this is applicable and whether it is the right way to do it.

WillowMobileSys

@Atlas wrote:

However, for other users to make this process easier, is there any way to create an invoice record for the Invoice table directly from the Sales table with an action that includes only four columns?

@Atlas wrote:

I realized that pulling Sales Details rows to the invoice table would cause duplicating the data, and that's why I rewrote the scenario. Is it possible to create one record as a Grandparent to the invoice table which holds all the references to Sales and Sales Details?

I do not recommend doing this, create an Invoice table that simply references the Sales and Sales Details tables, but it depends on your business processes.

Invoices are really a snapshot of your Orders at that moment in time. When you create them they should capture the data as of that moment.

If there are any adjustments to the Orders...and there will be...then an "updated" invoice needs to be created. Typically the old invoice is kept for records and bookkeeping. So this would mean generating a new Invoice with its own data.

Consider how you will handle the use cases where Invoices have been created and/or Paid but adjustments are needed to the order afterwards.

Atlas

In this business model, the product is sold without an invoice and only dispatch notes are sent. Whenever the total sale amount exceeds 10k for dispatched items that are not billed, they invoice these sales as one invoice.

In the second scenario, I thought of creating a "grandparent" that holds the sales references so I can access all the Sales and Sales Details through this invoice record, which could prevent duplication of the data. I can also add a status column for payments and one additional status column. In case they would like to edit or delete some records, the manager can change the status of the invoice so they can make the necessary changes.

TeeSee1

You definitely need to think through what @WillowMobileSys has pointed out.

Just as a pure AppSheet implementation exercise, I did update the demo like this.

Atlas

Greetings TeeSee, thank you for your concern and thank you again for this demonstration. I will definitely consider John's comments.

Hopefully, I will be able to start making the application I promised. I am thankful for both of your support. So far, I haven't had such a request from anyone. I would like to ask if it is fine for you to share the test application as a community sample so that I can practice it.

Best Regards

TeeSee1

https://www.appsheet.com/portfolio/3401559

See Order Entries - Tabular

Atlas

Hello TeeSee, thank you for the sample app. Last night, I began implementing the invoicing feature with some changes. So far so good. I will update the results here.