Distributing payment to each service until balance becomes 0 - Maybe Looping

AppSheet AppSheet Q&A
, , ,
1

Lovely people of the community, please help me solve my problem.

Below are the tables:

  1. Service Booking
  2. Service Availed
  3. Payments

The process is to add a service booking in the system, then add different services under it which are being availed in that particular booking. So service booking & service availed is a parent child relationship.

Next step would be as and when a payment comes under a booking, you attach a payment to it. So service booking & payments also have a parent child relationship.

Whereas service availed & payments are a brother sister relationship.

Now, what I want is that once a payment is added under a booking, I want the payment to be distributed one by one from first to last service under that booking, till balance to be received for each service becomes 0.

I personally feel that it can be solved with the help of looping with actions, but I need guidance on how to do it. I am not able to build logic around it.

2

Do you really want it in that complex manner and in real columns?

I would probably simplify it as follows.

I will have

A) one VC called say [All_Services_Availed_Fees] of price type in the Service Booking table with an expression something like

SUM([Related Services Availed][Service_Fee])

B) Another VC called say [Payments_Made_Todate] in the Service Booking table with an expression something like

SUM([Related Payments][Payment_Amount])

C)A third VC called say [Payments_Status] of text type in the Service Booking table with an expression something like

IFS(

[All_Services_Availed_Fees] - [Payments_Made_Todate]>0,

CONCATENATE( "Payment of “, [All_Services_Availed_Fees] - [Payments_Made_Todate] , " is outstanding”),

[All_Services_Availed_Fees] - [Payments_Made_Todate]=0,

“All payment done, no outstanding”

)

1 Like
3

Honestly speaking, the idea behind tracking collection for each service is also for cash collection reporting at the end of the month. To determine for which service how much cash has been received.

4

Okay , got it. Thank you. But this raises more questions which I will not ask now.

Maybe someone has an elegant solution for you with the provided details.

1 Like
5

I hope so, Suvrutt.

Thank you!

6

Here’s my thinking. It’s untested, so TEST THOROUGHLY!!!

Assumptions:

  • Payments table has a column named Amount that contains the payment amount.
  • Service Availed has a column named Amount Due that contains the total amount due from the customer for the service, and Amount Paid that contains the total payment received for the service, such that Amount Due - Amount Paid = Unpaid Balance.

Because the Service Availed and Payments rows involved don’t change as a part of these computations, we should be able to use the Related Service Availeds and Related Payments virtual columns of the Service Booking table to improve efficiency and reduce complexity.

The loop condition would probably look like this:

AND(
  (SUM([Related Payments][Amount]) < SUM([Related Service Availeds][Amount Due])),
  (SUM([Related Payments][Amount]) > SUM([Related Service Availeds][Amount Paid]))
)

“(The sum of all payments received so far) is less than (the total amount due from all services)” (“there’s an outstanding balance”) AND “(The sum of all payments received so far) is more than (the total payments allocated per service)” (“unallocated payment remains”)

Create an action for the Service Availed table named (e.g.) Update Amount Paid of type Data: set the values of some columns in this row. Under Advanced, add an input named Available of type Decimal with a default value of 0.0. In Set these columns, set Amount Paid to this expression:

(
  [Amount Paid]
  + MIN(
    LIST(
      [_INPUT].[Available],
      ([Amount Due] - [Amount Paid])
    )
  )
)

Within the loop, there should be an action for the Service Booking table of type Data: execute an action on a set of rows that references the Service Availed table and uses the Update Amount Paid action from above. The Referenced Rows expression would look like this:

ORDERBY(
  SELECT(
    [Related Service Availeds][Service Availed ID],
    (([Amount Due] - [Amount Paid]) > 0.0)
  ),
  [Service Date],
    false,
  [_RowNumber],
    false
)

Replace Service Availed ID with the name of the key column of the Service Availed table, and Service Date with the name of the column in the Service Availed table that contains the service date. This expression gathers the related Service Availed rows that still have an unpaid balance. Note that the SELECT() expression is the same as in the loop condition earlier.

Add an input for Available with the following expression:

(SUM([Related Payments][Amount]) - SUM([Related Service Availeds][Amount Paid]))

TEST! TEST! TEST!

1 Like
7

Thank you Steve for sharing this across. Let me check this.

1 Like
8

@Steve The loop condition you have mentioned in the beginning. Where do I have to use that?

9

@Steve can you help with this?

10

In the action that acts as the top of the loop. What are the actions you have setup?

1 Like
11

So I have tried to follow exactly what you have written above because I am still trying to wrap my head around the logic. So 2 actions are setup as you told above one to update amount paid & one at booking level.

12

Take a look at these search results.

13

So steve I have made one action at service booking level, which is basically execute an action on a set of rows, where I am linking the update amount paid action.

At the service booking action itself I have put the loop condition but it is not working exactly right.

Leaving that aside, one thing which I observed is, that if i add a payment once tag it to a booking & through actions tag it to the service.

Then if I add second payment to the booking and execute the action, the system forgets about the first allocation and allocates the payment added the second time. So it doesn’t take the first payment added into consideration.

14

Also, Suppose there are 2 services added, one of 1500 & another for 650. And I add a payment of Rs 1000, the action is allocating Rs 1000 indiviually to each service, whereas the actual amount paid by the customer is only 1000 & not 2000.

I want it to allocate the payment to the first service till it becomes 0..

IDK if I am doing something wrong

15

Here is one attempt to do what I believe you would like to accomplish.

Animation.gif

The key to this app is to create a Payment Allocations table which has both Services and Payments as reference to the respective table.

Then when a payment is added to a project, add allocation records using a loop.

It would be too long to put what I did in words so I will simply share this POC app if you would be interested.

My App Portfolio → See Payment Allocation

16

Hey @TeeSee1

This really helps. It is actually what I wanted. Thank you soo much for sharing the app.

I had a question if you don’t mind, right now it allocates payments basis the order in which services are added. Is there a way for me to choose services I want the payment to be allocated to rather than it taking a default order?

Thank you for your efforts!

17

Can you be more specific as to how you want to specify the services to allocate payments to?

Just the order? Including amount? If your selection is random, the only way to do it is via manual allocation.

1 Like
18

Basically I want an enumlist to select basis which it can just allocate the payments..does that make sense?

19

If suppose there are 4 services.

I want to select 2 services manually and let the system allocate accordingly.

Would that be possible?

20

What happens if you have Service A - 100 and Service B -50 and receive a payment of 60.

Do you allocate 50 to Service B and 10 to Service A or all 60 to Service A and none to Service B?

Or do you always receive the exact amount corresponding to whatever services the particular payment is received for?

1 Like