I have a small table with tennants, each with move-in and move-out dates.
Depending on my selection of one of these tennants, i would like to filter a table of expenses (which each occur at a certain date). That way i know which expenses should be attributed to each tennant.
Hi @Felix_Meixner! Would Widget Linking do what you want? There are some videos explaining them, like Linking widgets with references - YouTube
While I strongly suggest you reading the material provided in the previous response, to make it simple.
Have a tenants table.
Have a Expenses table.
At the expenses table, create a column of the type REFERENCE, and select the tenants table. That way, each expense is linked to a tenant. You CAN go at the expense table, create an expense and select a tenant.
But the best way to do it, and the way to select a tenant and see it’s expenses, it’s to create a page with a table or cards list of the tenants.
Then add to the same page a WIDGET of the Expenses table (also card list or table), REFERING (bottom left of the new widget window) to the Tenants table.
That’s it. Now the widget showing the expenses will be filtered by the selected tenant. Also, in this page, whenever you select a tenant and see it’s expenses list, if you add a new expense it will be automatically linked to that tenant.
Thanks for your solutions. I hope they at least help others.
As I understand it with ControlProcess_Pav’s solution, I would have to manually enter the references into each row of the expenses table. Alternatively I guess i could use a reference list in the tenants table, but this would also have to be manually populated.
As I want an automatic assignement, I have thought of two ideas:
- in the tenants table I could use lookupRecords to create a list of only the wanted expenses, although lookupRecords might only work with field=value arguments and no comparison operators.
- have a column of type Toggle in the tennants table (with a trigger formula to ensure only one toggle is set at time). In the expenses table, I use a lookupOne to find which tennant has the toggle selected and with the properties from that tenant I can use various Complex Filters to filter the expenses table. (I am using this solution for now)
Actually, I said that was ONE way to do that. (from each expense, select a tenant)
“But the best way to do it, and the way to select a tenant and see it’s expenses, it’s to create a page with a table or cards list of the tenants.”
With two widges one showing Tenants and other showing Expenses in the same table, and the Expenses table with a reference column to Tenants and the Expenses widget refering to that table, then you select a tenant and can add multiple expenses to that tenant.
Or maybe I have not understood your question.
Do you already have all tenants and expenses and need to link both? Or will you populate with time the expenses? Whats the process flow of inserting tenants and expenses?
from this I gather you already have two BIG lists and what you need is just to connect each already created expense from a huge list to a tenant.
What field in the Expenses you can use as a foreign key to the tenants? If you only have names associated to an expense, then the job will have to be manual. Maybe what you want is to reduce the number of possible tenants?
So you have a list of 1000 tenants, and you have an expense to someone named Mary, and you want the list to show only the Marys, or names beginning with Ma?
The table of tenants is not large ( normally less than 10)
The table of expenses will be large (up to 1000 rows)
Tenants move in to and out of the appartments on specified dates. This information is saved in the tennants table.
Expenses are normally billed to the appartment building for a few months (e.g January to March). This information is saved with each expense.
So I need to calculate by how much time these two periods intersect (stay in appartment, billing period) to find out how much each tennant should pay.
I also have to consider further calculations: each expense may also be subdivided differently to the appartments. (higher appartments pay more for heating, ground floor appartments pay more for the water since they use more for their gardens, etc)
Ok, so what happens here is that you link expenses to an apartment, and what you need to know was what person was occupying the apartment in the date of the expense. Is that it?
There is very probably an easier way, but I would then have 3 tables, Tenants, Apartments and Expenses.
Expenses refer to apartment. Already in this table, when you select an apartment, it can create a modifier based on apartment size, and a column for recalculated value based on that modifier. The initial value is always 1, multiplying the expense by 1.
But if you click a toggle button to use the modifier, then let’s say that apartment has a modifier of 150% because of it’s size… or if you insert an expense for the whole building, then that apartment is 10% of the whole building. So it already changes the value based on the apartment referenced and type of expense.
Then you have page where you select the apartment, and it lists the tenants (as I see, an apartment can have multiple tenants) and the dates they started and ended their tenancy.
You select the tenant than you use the filters to filter the expenses of that apartment (remember, expenses refer to apartment, so only expenses of that apartment are being shown) by date.
Now, I would create an ACTION BUTTON , where, when you are sure of the expenses linked to that apartment and which are inside the period you filtered, will LINK all of those to the tenant of that period. Automatically.
1 Like