Show the most recent link from another table

[James_Green]

On our table we have one for our units that can be rented out by someone for a time frame. However if someone is moving out on the 22nd for example, we can book another person to move in after that date. However if I look at the table for the units, I can see multiple customers attached. Is there a way to filter that so only the customer which is currently meant to be in there shows up.

For example, if we have two customers ZRO100 and DRO101 have booked unit 1. ZRO100 is moving out on the 20th of April and DRO101 is moving in on the 21st of April, how can I display only ZRO100 until the 20th and DRO101 after.

I would like this in reverse too, so that we can see that someone is going to be moving into the unit afterwards.

I have been trying to sort it out for ages but I think I need an opinion from someone with more experience.

[Julian_Kirkness]

Hi @James_Green

I’ve worked up an example of the first part of your problem for you - I’m assuming you have a table Units and another Tennants. If we first set up Units:

Ignore the fields for Tennants, Current Tennant and Current Tennant Leaves fields for the moment - now we set up Tennants:

This table is pretty much as you would expect - with t link to Units (the Unit field), arrival and leaving date. The clever stuff is in the two calculated fields which use a formula to fill in the Current Tennant and Vacant From Dates if there is a tennant there:

Calculating these seems strange - but we’ll use roll up fields in the Units table to display this information:

So, back on the Units table, we can now use rollups:

and

Note that with Vacant After you may want to format the date to suit.

I hope this worked example helps - come back if you need more.

Julian

[James_Green]

That is perfect, thank you for the help! Does exactly the job, need to get my head around creating fields in other tables to affect another one. Brilliant thank you.