Hello appsheet community! I do not know how ...

(Alfredo Pou) #1

Hello appsheet community!

I do not know how to solve this case: I have to register the payment of several rents. The amount is updated every few months. I created 2 tables, one where I register the payments and another where I put: the amount, how often is updated and the percentage of update for each rent and the date of the last update. Each month the payments are recorded in the payment table, but when it is time to update the amount, I have to register it in the other table in order to record the update date and the new base amount. I can not think of doing everything in the same table and I do not know how to write in 2 tables at the same time. Can anybody help me? From already thank you very much!

(Steven Coile) #2

My interpretation of your problem is this:

One table receives a record of rent payments received.

A second table is used to calculate the next rent due based on: the most recent payment received amount; an adjustment rate; an adjustment frequency; and the date of the last adjustment.

Structured this way, you currently must manually enter the rent payment amount received in both the first table and in the second table. You would prefer to have to enter the amount only once, such that the second table automatically recalculates using the most-recently-received payment captured in the first table.

Is my interpretation correct?

(Alfredo Pou) #3

Yes, is correct.

(Steven Coile) #4

One approach would be to make the column in the second table that contains the rent-received amount use an app formula that grabs the most-recently added rent payment amount from the first table.

Assuming the first table is FirstTable and the column containing the rent-received amount is [rent_received], and the second table is SecondTable and the column containing the rent-received amount from the first table is also named [rent_received], set the App formula for the [rent_received] column in SecondTable to:

=any(select(FirstTable[rent_received], in([_RowNumber], list(max(FirstTable[_RowNumber])))))

(Steven Coile) #5

Another approach would be to do away with the second table entirely and instead wrap it all into the first table. Each row in FirstTable would consist of:

rent_rcvd: the rent payment received. Manually entered. Initial value: =lookup(max(_RowNumber), FirstTable, _RowNumber, next_due)

date_rcvd: the date rent was received. Manually entered. Initial value: =today()

adj_freq: how often rent is adjusted, in days. Manually entered. Initial value: =lookup(max(_RowNumber), FirstTable, _RowNumber, adj_freq)

adj_amt: how much rent is adjusted each adjustment. Manually entered. Initial value: =lookup(max(_RowNumber), FirstTable, _RowNumber, adj_amt)

adj_date: when next adjustment is to occur. Manually entered. Initial value: =(lookup(max(_RowNumber), FirstTable, _RowNumber, adj_date) + if((today() < lookup(max(_RowNumber), FirstTable, _RowNumber, adj_date)), 0, adj_freq))

next_due: how much rent due next month. Manually entered. Initial value: =(lookup(max(_RowNumber), FirstTable, _RowNumber, next_due) + if((today() < lookup(max(_RowNumber), FirstTable, _RowNumber, adj_date)), 0,

[adj_amt]))

This is untested. Use at your own risk. Provided as an idea only.

(Aleksi Alkio) #6

I would choose Steven’s option #2 but little bit in a different way:-) I would probably created your features with virtual columns. The amount is easy but the feature “how often is updated” is little bit difficult to know what you are looking for. Maybe list of dates? The percentage and last payment should be again quite basic.