And what business model are you using where you need to track security deposits?
Managing Rental Property?
Or something else?
A description of what you need to do with these security deposits in context of how your business functions would be very helpful.
You are right, i forgot to give some info about general business.
Yes we are managing a rental property of a 150 tenants.
We collect a deposit from each tenant at the beginning of lease and then return it at end of lease minus charges if there are any.
Sometime a tenant will upgrade or downgrade to a bigger or smaller space and i have to add a line for the change. I am sure there are better ways to use Filemaker than Excel.
Presumably, you then need to know which tenant has leased each apartment as well as any deposits they have paid. From there you might, in the future, add a move out inspection capability to better document what charges are being deducted from the deposit.
Tenants::__pkTenantID = Deposits::_fkTenantID
Tenants::__pkTenantID = Apartments::_fkTenantID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
You may or may not need the Aparments table, but I am assuming that you will be using FileMaker to record information about each apartment such as the apartment number, square footage, any furnisning or appliances....
A portal to Deposits on the Tenants layout can show each deposit made by a tenant. If they change spaces, I'd create a new record in the Deposits table to log the additional charge or refund. A summary field in Deposits can compute the total deposit from those entries.
Well i am not too worried about using FileMaker ro record info about each space (apartment) as we are using a different booking system that keep track of all lease info, monthly charges, date started...)
Unfortunately the system we are using is not perfect and it let us charge customer for Security deposits but it is not able to give us reports on who have a security deposits or what is the total of security deposits we currently hold at a specifice date or even as today's date.
I was thinking of FileMaker mostly to keep track of the security deposits. So i can have a good reporting on it such as knowing:
- which member we owe money
- what is the total deposits of all customer at a date
- how many customer we hold deposits for
- balance for each customer
Thanks a lot. I am starting to get a better picture of FileMaker
All of that can be managed with this part of what I recommended:
I'd set up Deposits like a bookkeeping ledger with a "debit" field for entering deposits received and a "credit" field for entering amounts refunded and charges applied against the deposit. Each such event would be logged as a new record in Deposits. A portal can list all such Deposit records for a given tenant.
You can then define a calculation field, cBal, as Debit - Credit and a summary field, sBalance as the running total of cBal to show the current balance on any given Tenant's deposit amounts.
I guess i am doing something wrong as it does not come up right.
I have attached screenshots of my configuration, if you could have a look and let me know whats wrong with it.
I recently made a screencast about something that is not entirely your situation but it can be compared.
Maybe if you follow the exercise you can learn some interesting tricks:
Here is more picture
Not sure why you called it "_BalanceID", but you need a summary field that computes the total of _BalanceID.
And make sure that you select the Running Total option for it.
One important field missing from the Deposit ledger is Description [reason] of the Debit/Credit. Remember the deposit belongs to the Member and can only be changed for reasons mentioned in the Member's lease agreement, such as damage etc. I would also recommed the Deposit balance be a field in the Member's table, therefore his total Deposit is the Current balance as each Debit and Credit is issued. Then the Summary fields are what accounts call "Balancing your books"
Last... You must have a Starting Balance, which would be the other reason to put the Balance on the Member's table.
BTW, you will eventually find the need to MERGE you books together, as Phil mentioned.
Hey, thanks for the help.
My transactions deposit give me a balance per line, how do you make it take the previous balance and adjusting to the new transaction.
Also what calculation would you do to have the Total Balance of all Members?
Do you see any table that is missing or would you do things differently? I know i need to add the summary field in the member layout, which i will do once i figure out what is wrong with my calculation.
Thank you all.