Tracking rent payments using built in app formulas

[AaronTerry]

I’m trying to write an in-app formula to calculate a tenant’s [Balance] based on the current [Rent] and payment(s) manually logged by a form in a different view and sheet called ‘Payments’ when a tenant e-transfers rent.

In my ‘Rentals’ sheet I was able to make it work by creating a Balance formula like this (=sum(X2:2)-sumif(Payments!A:A,[USER],Payments!G:G)) and a Charge formula like this (=IF(TODAY()>=[1/1/2021],[RENT]) for the first of the month but I’d prefer it in the app so the sheet columns don’t have to be formatted when a new user joins.

I’m thinking EOMONTH(TODAY(), -1) + 1 is involved and a slice might useful but I can’t quite figure it out!

Screenshots of relevant data columns to follow. Link to my previous thread for user roles if someone is building a similar app. Security filter for using 4 different roles, each one with a higher security than the other - #4 by ...

Any help or guidance would be greatly appreciated! Happy with my results so far!

[AaronTerry]

[Steve]

Aaron_Terry:

I’m trying to write an in-app formula to calculate a tenant’s [Balance] based on the current [Rent] and payment(s) manually logged by a form in a different view and sheet called ‘Payments’ when a tenant e-transfers rent.

Please elaborate on this, preferably using plain language rather than telling me how you think it should be done in AppSheet.

[AaronTerry]

In Rentals, If Today’s date is the first of the month or greater then the Balance equals the cost of Rent.

In Payments, If the Address column matches the Address listed in rentals, subtract the Amount from the Balance.

I may be missing a key piece that carries the balance over from an unpaid month, maybe I’m better off somehow creating a new row in payments to create the owing balance on the first of the month. I believe that would solve the issue with the balance being tracked in the rentals sheet.

Does this make sense? Thanks Steve!

[Steve]

I think your best bet is to create a bot that runs the first day of each month that charges each tenant that month’s rent, as a negative value in the Amount column of the Payments table. The Rentals table would have a Balance virtual column with an App formula that computes the current balance from the Payments rows for the Address:

SUM(
  SELECT(
    Payments[Amount],
    AND(
      ISNOTBLANK([Address]),
      ([Address] = [_THISROW].[Address])
    )
  )
)

[AaronTerry]

Hi Steve,

I’ve made some good headway with this by creating the virtual “Balance” column as you suggested and now I can use an action to negatively charge the tenant, logging it as a new row in the payments table.

I’m having some issues getting my “First of the Month - Negative Charge” bot to trigger by a scheduled time. I’ve tested it which is free from errors and run it to see if it creates 5 new rows of Payment information and it does so within a second.

I’ll attached some screen shots of my bot and the “Negative Charge” action to make sure the settings are correct.

Thanks for all your help!

[AaronTerry]

[Dan_Bahir]

Is the app deployed ?

[AaronTerry]

Yes it is deployed!

I found that issue in the community and thought that was my problem but sadly it didn’t fix the problem.

[Dan_Bahir]

Does it work when you try with the run button ?

Are there any errors in the monitoring app ?

[AaronTerry]

Runs fine and the correct data shows up in my google spreadsheet. Both the Process and the task is showing success when executed individually in the test page.