Tracking rent payments using built in app formulas

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 Aaron_Terry

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

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

2 Likes

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!

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])
    )
  )
)