3 Replies Latest reply on Nov 28, 2011 4:42 PM by philmodjunk

    Creating Records in another File and summing results

    DavidWill

      Title

      Creating Records in another File and summing results

      Post

      Situation:

      There is a one table that has one record for each Month for each rental unit. (100's of unit and thousands of records over the years)

      Each record has:

      Year

      Month

      Balance Due

      Interest Rate for past due amounts

      Accumulated interest amount added to balance due each month

      Dozens of other fields

       

      Each month this table is queried to determine if the is a balance is past due and interest is calculated and added to an "Other" field which is a part of fields accumulating the balance due. This is repeated until the balance due is paid or written off as a bad debt. This calculation is done manually.

       

      The objective is automate this process by manually invoking a script at the beginning of each month to post interest to related many table records whose one file has a past due balance. The sum of related files is be set to a field (Accumulated_Interest) in the one table and added to the parts that accumulate the balance due.

       

      The key between these files exists as "Rent_ID" in the one table.

      The records should contain the posting date, interest rate, balance due (base calculation) and the calculated interest as well as the Rent_ID.

       

      Can someone write the script exactly as is needs to be (without the Table names), including checks so the system doesn't blow up and/or do something devastating?

       

      Please email me directly if interested in working for pay or post an example for all of us.

       

      davidlwill@gmail.com

        • 1. Re: Creating Records in another File and summing results
          philmodjunk

          Does month store the number of the month or the name?

          Is Interest Rate for past due amounts looked up from another table or specified in some other fashion?

          • 2. Re: Creating Records in another File and summing results
            DavidWill

            Thanks for responding.

            Month is stored as the name of the month.

            The interest rate is stored in each monthly rent record so it can be changed on a monthly basis for any rental unit.

            The Rent_ID looks like, "Madsion 016 (2) for January 2011" It is a concatenation of the Unit ID (Property name followed by the unit number in parentheses for clarity) strung together with the month name and the year separated with the word, for. Long and ugly but leave no doubt where it came from. If there were hundreds of thoundsands of records, we'd use a serialized key.

            The reason for storing all this in the Interest table is so it can be traced back to exactly what each interest charged was based on.

            The main part of this rental tracking program was started in the early nineties and still pays for itself everyday. The main part has tables for Property, Unit and another for rent payments. The Unit and payment tables also manage a lot more information.

            • 3. Re: Creating Records in another File and summing results
              philmodjunk

              The interest rate is stored in each monthly rent record so it can be changed on a monthly basis for any rental unit.

              Yes, but how would it be changed? This might be an important detail in a script to generate the needed records. This sounds like a value that would be stored in a preferences file if all tenants get the same interest rate, in a table of rental units or tenants if specific to a unit or a tenant. Whereever it's stored, it would then be looked up from (via field option or script) there and copied into this field in your billing table. You'd copy rather than reference the value in another table so that changes to the rate don't affect past billing records.

              Month is stored as the name of the month.

              That can be a bit combersome. You can make a month a date field that stores the date for the first day of a given month. This one field then records both month and year and you can use data formatting in your layouts to display month and/or year as needed on each record. This allows you to sort records in this billing table by the month field and they'll sort in chronological order.

              Rent_ID looks like, "Madsion 016 (2) for January 2011"

              Not a good idea. It's much better to use a serial number field to identify each record in this billing (Rent) table. The property description, unit number should be stored in the table of rental units and pulled into this report via a relationship. The month and year would be extracted as needed from the Month field I have already recommended.

              The reason for storing all this in the Interest table is so it can be traced back to exactly what each interest charged was based on.

              How exactly are you currently computing the interest so that it is applied to the the balance due? Is this a monthly rate so you take the unpaid balance and multiply it by your interest rate?

              How do you log rental payments? Is there a payment field in the Rents table?

              You should have these relationships:

              Property----<Unit----<Rent Payments

              Property::PropertyID = Unit::PropertyID
              Unit::UnitID = Rent Payments::UnitID

              Property::PropertyID and Unit::UnitID would be defined as auto-entered serial numbers.

              New question: How do you intend to start off this script? Will you specify a  month and year, then run the script to generate records in the rent payments table? Will you click a button to generate the payment records for "next month"? Will this happen automatically on a specific day of the month?