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]
Solved! Go to 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:
Otherwise, should you still want to keep your current data structure, you would do the following:
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.
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
Please read this:
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?
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.
@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:
Otherwise, should you still want to keep your current data structure, you would do the following:
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.
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):
and I have a column in the Penjualan_Pembayaran which is :
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.
Would you please edit your post and translate all column names in your expressions to English? I'm having difficulties following. Thanks.
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.
User | Count |
---|---|
33 | |
30 | |
30 | |
19 | |
17 |