Data structure for incoming tenants.

[BAP]

I am making an app to help manage an apartment complex. My current setup is that I have a table called Contracts and one called Assignments. As you can see below, the Contracts table gives the name of the contract period as well as the start and end date. The Assignments table pairs a tenant with a contract period and an apartment number. I have a data slice that shows current, past, and future assignments based on the start and end date of the associated contract.

This works great and all, but I worry about this system when it comes to tenants selling their contracts. I want the system to be able to swap out the tenants automatically. While I could make a custom contract period and move the tenant that is selling their contract onto that one and then create another custom contract for the new tenant, I worry that keeping track of things can get complicated really quick and that we might over or under assign people to certain apartments.

Not sure if I could have something counting the number of tenants for each apartment and if it goes over, it warns me. It would also be nice for the system to just swap out the Tenant ID for the tenant buying the contract for the specific contract being sold instead of having to alter a bunch of contracts and assignments.

Looking for some advice on how I could maybe go about setting this up differently or alter what I have.

Thanks

[romulo_torres]

Hello!

Create a Virtual Column for Tenant Count
In the formula for the virtual column, use an expression that counts the number of tenants. The expression might look something like:

COUNT(SELECT(Assignments[Tenant ID], [Apartment Number] = [_THISROW].[Apartment Number]))
"[Apartment Number]" is a common field in both tables.

Create a Bot that checks if the number of tenants: Example: [Tenant Count] > 4

https://support.google.com/appsheet/answer/11432969?hl=en

Create a Temporary Column for New Tenant ID 
Add a Virtual Column: In your Assignments table, add a virtual column (e.g., “New Tenant ID”) that will be used to temporarily store the new Tenant ID during the swap process.

Create an Action

AppSheet Actions 

Set the action type to "Data: set the values of some columns in this row".
In the “Set these columns” section, select the column that represents the Tenant ID in your Assignments table.
For the value, you will use an expression that pulls the new Tenant ID. This will be typically captured from a user input or a form. The expression might look something like [_THISROW].[New Tenant ID] where “New Tenant ID” is a temporary column used to capture the new tenant's ID.

Only Show If: To ensure this action is only available for current contracts, you will need to add a condition:
Create a Virtual Column for Contract Status:
Go to the "Data" tab, then "Columns".
In the "Contracts" table, add a new virtual column, let’s name it "Is Current".
Use an expression like AND(TODAY() >= [Start Date], TODAY() <= [End Date]). This expression will return TRUE if the current date falls within the contract period, marking it as active.

Navigate to the "Only if this condition is true" section of the action.
Add a condition that checks if the contract is current.  the expression might look something like [Contract Status] = "TRUE".