Updating data of a column from another form

[Benny_mk]

So I have an invoice app where I have a tab to create the invoice and another tab for payment of that invoice, and some customers may have multiple invoices to be paid, so payment tab to create a payment table where customer can pay those multiple invoices (which is stored in one row for one payment).

What I am trying to do is that I want to know which invoice is paid by the customer and which hasn't.

So what I did is to have the invoice table (Penjualan) to have a column that refer to the payment table (Pembayaran) , this way I can see which invoice is paid with which payment column.

The problem is that I do not know hot to update the column of invoice (Penjualan) table column when I submit the payment (Pembayaran) table, with the condition is that the ID of the (Penjualan) is chosen in the (Pembayaran) form

TLDR : trying to update (11.) Penjualan[Pembayaran dari ID] with (2.) Pembayaran[ID Pembayaran] if invoices id (2.) Penjualan[No. Nota] is chosen in (5.) Pembayaran[Nota]

[Joseph_Seddik]

For this, you should use a Form Saved action on the Payments view. Whenever a payment row gets added or updated through the form, the action will update the corresponding invoice(s) accordingly. 

[Benny_mk]

But the invoice is in another table, and I tried using the automation, the process can only run tasks within the same table only.

How do I change data value of another table

[Joseph_Seddik]

1. Create an action to set row values in Invoice table, let's call it invoicePaymentStatus. 
   
   
2. Create an action in Payment table let's call it formAction to execute an action on a set of rows, set the referenced table as Invoice, and the referenced action as invoicePaymentStatus.
3. In your form view, set the Form Saved action as formAction. 
   
   

Please read this: 

Actions: The Essentials | AppSheet Help Center 

[Benny_mk]

I think I get what you mean. It's clearer with that explaination! Thank you.

But the one I'm really struggling on is to update the Pembayaran dari ID (in green) to be the same as ID Pembayaran  on another table, if the No. Nota (in red) is in the Nota in another table.

It is essentially the same as adding to the virtual column on the payment (Pembayaran) table but without editing the data or creating new data, just updating the data.

I've been trying for a few days on the formula but I just don't get it.

Which part did I do wrong?

[Benny_mk]

Update: I manage to udpate the column with some constant text exactly the way it supposed to, but I still don't know how to update it using the ID of the other table.

[Joseph_Seddik · Accepted solution]

@Benny_mk Sorry for the late reply. It took me time to relate translations to column and table names despite your well written description. 

I see that in your Pembayaran table, you have an EnumList column grouping the corresponding Penjualan for which a payment has been made in one column. This will prevent you from establishing correct table references and will complicate extraction of information. 

A better approach, would be to have a table that has one row per payment per invoice. For this you should do the following:

1. Create a new table Penjualan_Pembayaran. This table will have the following columns:
   • Key, with Initial value: UNIQUEID() 
   • "ID Pembayaran", type Ref  to Pembayaran table. Option Is part of checked. 
   • "Nota", type Ref  to Penjualan table. 
   • "Amount", type Price. This is the amount of the payment for a particular invoice. You can set it manually, or retrieve it automatically from the invoice with this expression in the column's App Formula: [Penjulan].[amount of invoice]
     
     
2. AppSheet will create a reverse-reference virtual column in Pembayaran table called "Related Penjualan_Pembayaran". With this, your "amount" column in the Pembayaran table should have the following expression in its App formula:
   
   SUM([Related Penjualan_Pembayaran][amount])
   
   
3. In your Pembayaran table, change the type of the "Nota" column from EnumList to Ref pointing to the Penjualan table. 
   
   
4. AppSheet will create a reverse-reference virtual column in Penujalan table, named "Related Pembayaran". This column will contain the payment for this invoice if exists. Better still, this will be a clickable reference not just a number, so from a Penjualan view you'll be able to easily navigate to the related Pembayaran record.
   
   

Otherwise, should you still want to keep your current data structure, you would do the following:

1. Add a new normal column in your Penjualan table, let's call it "Refresh". This column will be of type Number, Initial value: 1. 
   
   
2. Your "Pembayaran dari ID" App formula should be :
   
   FILTER(Penjualan, CONTAINS([Nota], [_ThisRow].[No. Nota]))
   
   
3. Create an action refreshPenjualan, with the following settings:
   • For a record of table: Penjualan 
   • Data: Set values of some columns in this row 
   • Refresh column, with the expression: [Refresh] + 1 
     
     
4. Create another action updatePenjualan, with the following settings:
   • For a record of table: Pembayaran 
   • Data: Execute an action on a set of rows 
   • Referenced table: Penjualan 
   • Referenced rows: [nota] 
   • Referenced action: refreshPenjualan 
     
     
5. Go to your Pembayaran view, and set updatePenjualan as the Form Saved Action.

[Wardaddy]

Hi Joseph,

I would like to apply the same expression on my form as well. The situation of Benny was quite similar to mine but slightly different. I'd tried your solution and it's identical to what I applied but it doesn't update the row on my reference table after I submitted a form. 

Here is what I'm trying to achieve, I have 2 separate app, "Job Order Master" and "Job Order". Job order app has a form with reference table share the same spreadsheet with "Job Order Master" .

Whenever a job order form submitted (with selected "Job Order Number" dependent dropdown from reference table) , the reference row with the same "Jobs Order Number" will insert a "value" or "Completed" and the respective "Jobs Order Number" will be filtered and won't populate in the job order form "Job Order Number" dropdown again.  Hope you can share your thoughts.

Thanks in advance.

[Benny_mk]

Wow! That was really clear and really helpful! Thank you for your help Joseph! Seems like I'm almost done with my app.

By the way I did some tweaking and added some column to suit the app well, but there are some things that didn't work out and I don't understand why
(plus I don't know why I can't post pictures anymore in here):

1. I have 3 column with some formula (Total sum of invoices , total sum of payments & the difference of the two):

1. Pembayaran [Total Jumlah Tagihan]  = SUM([Related Penjualan_Pembayarans][amt_tagihan])
2. Pembayaran [Total Nominal Pembayaran] = SUM([Related Tipe Pembayarans][Nominal])
3. Pembayaran [Kurang / Sisa / Titip]  = [Total Jumlah Tagihan]-[Total Nominal Pembayaran]

and I have a column in the Penjualan_Pembayaran which is :

• Penjualan_Pembayaran [sisa_tagihan] = [idPembayaran].[Kurang / Sisa / Titip]

What I actually wanted to do was that if the difference is a positive value, it will be in the column of the latest invoice (nota with the highest value), whereas the rest of the invoice will be valued 0 in that column. But if the difference is negative, everything will be valued 0.

i.e. :
if(Pembayaran [Kurang / Sisa / Titip] is positive)
{Penjualan_Pembayaran [sisa_tagihan] with highest nota will be valued Pembayaran [Kurang / Sisa / Titip], other nota chosen will be value 0 }
else {all Penjualan_Pembayaran [sisa_tagihan] = 0}

2. Then if it's possible, update the Penjualan[Sisa Tagihan Nota] accordingly with the Penjualan_Pembayaran[sisa_tagihan] where the invoice number is the same ; Penjualan[No. nota] = Penjualan_Pembayaran[nota]

Ultimately I just want to see which invoice is paid and which hasn't, because some of my customer doesn't pay on the invoice immediately , but rather after several weeks or months and pay a bunch of invoices at once.
Even when they pay they don't pay in full amount, so there will be leftover amount they haven't paid, or paid in excess where they can use that excess amount to pay off future invoice.

[Joseph_Seddik]

Would you please edit your post and translate all column names in your expressions to English? I'm having difficulties following. Thanks. 

[Benny_mk]

Hi! Sorry I been very busy with work. I managed to do what I needed to do. Thank you so much Joseph for the help so far.