11 Replies Latest reply on Dec 30, 2010 5:25 AM by GuyWinter

    Rental Property Assistance



      Rental Property Assistance


      Hi there,

      I am a newbie to FMP11A and am trying to set up a simple database for a rental property for university students. I have made a start, but there are some calculations which I am finding difficult to locate or find a way around.

      A brief overview of what I'm working with:

      To make things simpler, as rental rates may change from year to year, I thought it'd be best to have a separate file per year.

      There are a total of 7 units in 1 apartment block: x6 2-bedroom units, and x1 1-bedroom unit.

      Number of tenants occupying units would ideally be the number of bedrooms involved, however, this year for instance we have currently 1 person in a 2bed and also 3 persons in a 2bed. Therefore I am open to the possibility of having more tenants than bedrooms. (no current problem here)

      Ideally the lease period is 9 months (Sept to May). Again however I have tenants who don't obey this structure and am therefore open to the possibility of having various lease beginning and ending dates.

      We usually require monthly payment due at the beginning of the month (but it rarely works out this way as some tenants are slow on payment). The first payment would consist of the first and last months rent (e.g. rent for Sept and May). Also, a security deposit is required at the beginning which is returned at the end of the lease on inspection of the unit.

      Also, information and stats about the utilities paid per month will come in handy. The rental rate is inclusive of everything, but tenants are encouraged to not abuse these (utilities). It would therefore make sense to monitor the utilities.

      OK... now about what I have thus far in the database...

      A portion which just requires fields to be typed in and recorded (stored) is the utilities section, where monthly bills are then punched in as they come to keep track.

      I have 3 major tables: 1. Tenants (list of all the tenants and their info).   2. Monthly Rental Rate for the current year (per apartment per number of tenants occupying) - This is the rate for an individual tenant.   3. Payments (made by tenant and the date made)

      I also have one minor table which includes the Number of tenants occupying each apartment in the year, and currently. This is used for calculation purposes.

      In the Tenants table... Fields include: Name, Apartment # (to which tenant belongs to), Security Deposit (Yes.No Radio button), Start of Lease (date), End of Lease (date), Monthly Rental Rate*, Total Amount Paid up to current, Total Amount Due** up to current, Final Month's Rental Rate***.

      Other fields used for calculations : Expired Lease (which results 1 if end of lease date is < current date and 0 if not), Number of Current Tenants (using a relationship of the same table (is this called "self-join" or something), Counts the Apartment # field and subtracts from it the expired lease field resulting in the number of current tenants)

      *Monthly Rental Rate: Related to Monthly Rental Rates table. A case of if for example current number of tenants = 2 then the rate for 2 tenants in that apartment is produced (as an individual rate for that tenant). This is a current rate and is subject to change if there are less or more tenants in the said apartment.

      Total Amount Paid relates to the Payments table which is pretty straight forward I think (sum of Amount Paid in payment record).

      **Total Amount Due is calculated by using a formula found in this forum (i think) which calculates the elapsed months (from the 1st of the month) from the start of lease up to current (+1 to include the current month) times the monthly rate~ + monthly rate~~ (for the last months rent) + security deposit (caution fee below), and subtracting from all that the Total Amount Paid.

      Here is the formula... (Bold is the formula found on a forum)

      (((Year(Get (CurrentDate)) - Year(Start of Lease) ) * 12 + ( Month(Get(CurrentDate )) - Month(Start of Lease ) ) - If ( Day (Get(CurrentDate)) < Day (Start of Lease); 1; 0 ) + 1)* (Monthly Rent) + (Monthly Rent) + (Monthly Rent 10 11::Caution Fee)) - (Total Amount Paid)

      ~Monthly Rate is dynamic and would cause a problem when number of tenants change...I need to avoid that and somehow store the monthly rate in the condition that the tenants are of a certain number during a certain period of time.

      ~~Monthly Rate (should be final month's rental rate), as this may differ from the current monthly rate.

      ***Final Months Rate (which is where I am stuck now as I am kind of working backwards from what I have typed). This should be the rental rate for the final month of the lease (which may differ to current if other tenants are added or end their lease earlier)

      ...So I basically need a formula which will calculate the rental rate for the last month of the lease. This I can replace monthly rate~~ (for the last months rent) with in the above formula for payment due. I would also need a way to save the monthly rates as they may also change in the next month (depending on the number of tenants occupying the said unit), This would be incorporated in the above calculation I guess in order to get the exact payment due.

      Other info concerning utilities can be worked on at a later date. I cannot recall at this point if there is anything else I require for the time being.

      My apologies  for this probably overly large post, but I would just like to be concise enough to make it easier for your responses to cover my requirements. And I am also open to any suggestions which may better my custom database.

      Thank You in advance!

        • 1. Re: Rental Property Assistance

          Is there anyone who can assist me?

          • 2. Re: Rental Property Assistance

            Well you say "A brief overview of what I'm working with:"

            And then post a description of your project that is anything but brief! Wink

            You'll need to pick up some basic skills in FileMaker and a basic understanding of how relational databases should be designed. There are books and tutorials out there that can help you get started. Be prepared to invest some time in learning FileMaker before you tackle this project so that you have some basic skills to work with.

            We'll be glad to help you out with answers to specific questions as you work through that process.

            Your first step design wise will be to figure out how many different data tables you need and what kind of data will be stored in each. Just to help you get started, having a separate file for each year is completely unnecessary in a database system. You can easily store and work with records from multiple years if you design your system correctly.

            At the very least, you'll need a table for each tenant. Where one record records info on one tenant. A table for each rental unit were a single record documents all relavent features for that unit. And a Leases Table that links one or more tenants to a specific rental unit record for a specified length of time.

            • 3. Re: Rental Property Assistance

              Thanx for the reply PhilModJunk

              Firstly, I apologize AGAIN for the long post. This is actually the first time I needed to post online for help anything (as far as I remember). We have a car hire company and that is how i decided to go into databases as I plan to manage the business and customers on the computer as opposed to by book eventually.

              I just thought that this would be a small project to practice on and make organizing easier as well.

              I have done a little reading, and actually made a good start with my current setup (I think). The file per year option was just an easy way out for me, but come to think of it, if this current year works out well I may actually gain enough experience to work around one file multiple years. But that at this point is not a main issue.

              I am confused however that you suggest a separate table for each unit and each tenant. Would the fields for each individual table not be the same with different info in THAT ONE record? Does this not defeat the purpose of accomplishing what I wish in an efficient way and use one table with individual tenant records?

              Whilst this is discussed: My amount due calculation is working fine with some small flaws that I want to correct. The final month's rent is currently just using/adding an extra monthly rental rate (which is the current rate proportional to the number of tenants CURRENTLY occupying the said apartment).

              I would like to start with being able to calculate the final month's rent in the instance that in the final month's lease, the THEN occupying tenants would b calculated which would in turn give me the rate for that apartment at THAT point of time (the final month (probably the first day of the last month) of the tenant in question). This will then give me a more accurate 'amount due' record.

              Next, I need a way to store or calculate the previous monthly rate so that in the case that another tenant is added/omitted at a later date (at which point it would probably be the 'current date'), the previous rate is not discarded, and the exact 'amount due' record is found/calculated.

              Here is a quick example to look at:

              UNIT A - Rental rate per tenant = 600 if one tenant, 500 if two tenants... Rent is due at beginning of month

              Tenant 1 - Starts 1st Sept 2010, Ends 31st May 2011

              Tenant 2 - Starts 1st Oct 2010, Ends 31st March 2011

              At the beginning of Sept, (1) pays 600 (1st month) + 600 (last month)

              At the beginning of Oct, (1) pays 500, (2) pays 500 (1st month) + 500 (last month)

              My current database (at the beginning of Nov) would read for amount due:

              (1) = 400 (should be 500)... and now final months rental would turn to 500 (should stay 600). If this (Nov) is paid, amount due would show -100 (should be 0)

              (1) paid 500 (Nov) + 500 (Dec) + 500 (Jan) + 500 (Feb) + 500 (March) = 2500 (+prev 1700) = 4200 (TOTAL)

              My current database (at the beginning of April) would read for amount due:

              (1) = 1200 (should be 600)

              And there you see the flaws...

              Any help is appreciated.

              • 4. Re: Rental Property Assistance

                I am confused however that you suggest a separate table for each unit and each tenant. Would the fields for each individual table not be the same with different info in THAT ONE record? Does this not defeat the purpose of accomplishing what I wish in an efficient way and use one table with individual tenant records?

                That's not what I had in mind. One table for all units and a separate table for all tenants. Each record in tentants describes a separate tenant. Each record in units describes a different unit.

                • 5. Re: Rental Property Assistance

                  LOL...Darn English language!

                  Right... So, you're saying: (at least) 1 Table with the units (1-7) listed as (7) individual records, 1 Table with the tenants listed with their individual records (approx 13)... This brings a total of 2 individual tables. And you said to have the Lease info separately.

                  I was stuck at that part. If you read what you posted yourself, you can see that it COULD mean both. That is however what I have in a sense, with the lease information incorporated in the tenants table.

                  I think I understand what you propose, so I will try it and see how and if it works.


                  • 6. Re: Rental Property Assistance

                    You don't want to include the lease information in the tenant's table as one tenant can have more than one lease with you and you shouldn't duplicate a tenant record just to record a new lease.

                    This allows you to treat each lease separately.

                    TenantsTable----<LeasesTable>---Units    ( one ---< many )

                    One record in the Leasestable thus documents leasing one unit of a rental to one tenant.

                    Don't get me wrong here, you could include the lease info in the tenant record and given the small number of tenants and units you describe, this may work well for you.

                    The above structure gives you better flexibility for managing your data and this pays off more and more as you use the system to manage more and more units/tenants.

                    • 7. Re: Rental Property Assistance

                      OK... So I've added the Lease table...

                      Now I have: a Tenants table, a Units table, a Lease table, and a Payments table, as my major tables.

                      The Payments table obviously records all of the payments made/collected from the tenants sorted by date. So I think that is straight forward for the moment.

                      I have set up the Lease table with the name of Tenant, Apartment #, Start and End Lease dates to begin with.

                      The Units table consists of info on the unit including rental rates distinguished by number of tenants occupying the unit, number of current tenants, and telephone number for the said apartment (as that would not change). 7 records in all (7 units).

                      As majority of the info from the Tenants table has been moved to the Leases table, contact info can be placed in the Tenants table, but I am now left with the Total Payments Made, and Amount Due: Where do you suggest is the best place to put these; Tenants table or Leases table? My initial instinct is to place it in the Tenant's table so that the account for each tenant is not duplicated. Please advise. This info is calculated similarly to the method before, but I would like to find a way to deal with those 'current' leases separately i.e. where the current date (dynamic), falls within the lease stage (between start and end of lease). What I wish for is that calculations are made based on the leases excluding those expired ones.

                      My payment due method works well for the time being but will not give a true calculation when tenants are added or subtracted at a later stage. And again, I wish for a way to calculate the rent payable in the final month of the lease in order to produce a more accurate result. Although this may change if a tenant leaves earlier than expected or another tenant comes in, this will still provide an accurate result.

                      If for some reason you do not understand what I am trying to achieve please let me know. Below is a small example:

                      Tenant 1 - Starts 1st Sept 2010, Ends 31st May 2011

                      Tenant 2 - Starts 1st Oct 2010, Ends 31st Dec 2010

                      The calculations I would require to stay constant until another tenant is added/subtracted for (1) would be the total amount due taking into consideration the instances and events that occur during his or her tenancy. For instance; the rent for Sept would be 600, but for Oct to Dec it would be 500. Thereafter 600 till the end of lease or another tenant is added etc. It seems simple, but how do we put this in a formula.

                      Note: Current date will always be greater than or equal to Start date of lease as new leases will only be added after the tenant moves in.

                      I look forward to your wise input on this.

                      Thank You

                      • 8. Re: Rental Property Assistance

                        First suggestion, define a Tenant ID number field and set it up as an auto-entered serial number if you haven't already. Use this field in your relationships to link a tenant to a specific Lease and all their payment records.

                        I would set up Total Payments Made and Amount Due in the Payments table as these refer to payments.

                        I was picturing one Tenant record per lease with a single person billed monthly per the terms in the lease. I can't quite follow your description where tenants coming and leaving change the amount of rent paid by a different tenant. If two or more people share a unit do you have them each sign a lease and each get separate bills for the rent? And the lease states that if one leaves, the other has to pay more rent?

                        • 9. Re: Rental Property Assistance

                          Before we get to the database setup, I'll further explain how we have worked the tenants and their payments.

                          Firstly I would like to say that we are not based in the USA or UK. As these are accommodations for university students we try to have them occupied from September to May (academic year where we are). The 6 2-bed units would usually have a minimum of 2 tenants and the 1-bed unit at least 1 tenant (13 tenants in total). We have had these apartments up and running for about 18 years now, and I, finishing university and managing the family car rental business am trying to organize the business(es) electronically. It just so happens that this year we are facing these possibilities: 3 tenants in a 2-bed unit; of which the final addition (of the 2) only intends to stay until December (1st semester). The rental was then adjusted and agreed upon so that each tenant would pay less individually than if there were 2 tenants. This would only occur up till December, after which the 2 tenants would pay the regular fee. This regular fee per individual tenant would be doubled if only 1 tenant decides to take 1 2-bed unit, which is also happening in one of the units. That particular tenant however has informed us that she has found a room-mate to move in from December. This would obviously change the rental per individual. Also to note, which has happened in the past, is that some individuals leave earlier than previously intended, leaving the burden on a single tenant. Sometimes we manage to find tenants to replace the one which left, but this is not always the case. At which point we would go into a loss as we would only attain half of the regular rental for the said unit. This is also an issue that I would like to explore if possible.

                          As you can see from the above, there are a great deal of possibilities involved to work with, although it seems like a very simple operation. I hope it answers your latter questions.

                          As this is a very simple operation, how necessary is the Tenant ID? I have made a relationship with the name of the tenant (which by the way is first and last name in one e.g. John Doe) in the Tenants table and Payments table.  We have not recalled in all these years 2 tenants with the same name. If however you suggest for other reasons then I would not mind to comply.

                          To repeat, the Payments table is almost like a receipt book (without the individual receipt number). The records are sorted by the date payment was made. Hence a tenants name would be on more than 1 record. How would I then place a field of Total Paid, and Amount Due in this table? I was thinking that since the Tenants have no duplicating record, it would be logical to place these fields in this table in order to locate for the particular tenants.

                          Your opinions and guidance is appreciated.

                          • 10. Re: Rental Property Assistance

                            Using a tenant's name as the primary key is never a good idea. Not only can tenant name not be unique, they can change ( Your culture may differ here ). In addition, if you make a mistake entering a tenant's name it becomes difficult to change that mistake without breaking the link to any related records.

                            Ideal primary keys should always be guaranteed unique and meaningless. An auto-entered serial number is thus a good choice for this.

                            Since each tenant has their own lease record, their arrival and departure dates can be stored in this record and the related Unit record can then use those dates to determine occupancy and the rent due each tenant.

                            In your payments table, summary fields can combine values from multiple records to compute a payment due for one tenant or even produce a combined report for all tenants with subtotaled amounts due. You can also compute an amount due from a tenant's record or even place the summary field from the payment table on the Tenanant layout to display the total amount due.


                            • 11. Re: Rental Property Assistance

                              I have a question regarding this database, I am trying to write a similar one but cant work out how to stop occupied properties - which are already let out from showing up in my properties available drop down list on my tenancy table - nay ideas how to do this as it seems to end up being circular and I dont know how to get around this??