Monthly auto enter debits, looking for direction

[Hawaii80]

I'm not sure exactly how to tackles this yet, so I'm looking for a little guidance in the right direction.

 

I have a property management database that I created and everything works as it's supposed to so now I want to move in the next direction but just don't know where to begin.

 

Of the many tables I have 2 are the tenants table and the tenant payments table. I have a portal for the tenant payments table as a ledger in a tab section on the tenants table; this is where I have accounting enter payments and deduct for late fees etc. Works great, but what I would like to do is have a calculation that automatically looks at the lease start date and end date on the tenants table and on the 1st of each month automatically, (without user interaction) enters the rental amount due as a debit. This way accounting can balance the account by entering a credit manually for the same amount when rent is paid thus bringing the account to a 0 balance due.

 

Could someone guide me in the direction on how this might be done? I'm just not sure where to start. The fields I use in the tenant payment ledger are date, (dropdowncal), fee type, (value list includes; rent, sec. dep. NSF, late fee, overpayment), credit, check #, debit and note.

[touchMe]

Try, a script to create a new record in the payments table each month (or when > renewal date (thru month) on any specific tenant record), and flag a status field on the new payment record, unpaid... this will dissapear when the payment field is not empty, ie; an actual payment received and entered

 

It might be an idea to flag status not only as unpaid, but also have options for payment received, banked and cleared (perhaps part-payments? do u receive them?)

 

Create the record BEFORE you receive the payment, this will tell ya what's due and you could be fancy and in list mode use conditional formatting to present you with a red entry (example) when a payment is overdue

 

Where and how to put the script? - dunno, might think about an opening script which checks for payment dates today or later where the date of last 'invoice' > 30 days... there are lots of ways

 

Think about invoices rather than receipts and you should be good

[Hawaii80]

Thanks for the reply, I'll play around with the idea and see what I can come up with.

[Hawaii80]

Try, a script to create a new record in the payments table each month (or when > renewal date (thru month) on any specific tenant record), and flag a status field on the new payment record, unpaid... this will dissapear when the payment field is not empty, ie; an actual payment received and entered

 

How could you tell the script to create a new record for all tenants per month vs. 1 new record at at time per tenant?

[touchMe]

How could you tell the script to create a new record for all tenants per month vs. 1 new record at at time per tenant?

 

Yup! I guess I would be thinking about an invoiceTable. Whenever I open the solution (assuming you close it once per day for backup and then reopen 8am or whenever) the startup script would include a script based on dueDate

 

Does everyone have the same or differing dueDate(s), eg; are all monthly accounts payable on the 1st (or 15th) of the month, or do different accounts fall on any day of the month?

 

- assuming the latter (first one is easier), script would be something like...

 

[script-accountUpdate]

if no accountUpdate has been performed today, continue...

find all the tenant records where date of last invoice is 30 days or greater

...if none found, use an error-capture (result 401) to take the user out of the script

where found set is greater than 1... go to loop [your one-record-at-a-time bit]

and; create new record in invoices, set field (related field, probably tenantID) from tenantID in tenants... have a bunch of lookups to copy over the standard invoice info, name, address, unit #, etc.,

 

...and you have an automated invoice

 

from there... whatcha wanna do? - record payments? - record deposits? - grab reports?

 

Probably the invoiceTable is key... it sounds like you have been working directly from the ledger, (unless an accountant, or even) most folks would have some invoicing in between the ledger and the tenant records

[Hawaii80]

Does everyone have the same or differing dueDate(s), eg; are all monthly accounts payable on the 1st (or 15th) of the month, or do different accounts fall on any day of the month?

Yes rents are due the 1st of every month but no later than the 5th of each month. But, coming up on the 1st of each month is perfect; we just assign a late fee if it's after the 5th.

 

Probably the invoiceTable is key... it sounds like you have been working directly from the ledger, (unless an accountant, or even) most folks would have some invoicing in between the ledger and the tenant records

[/Quote]

Yes, we've been working directly from the ledger. When they enter payments/debits there it enters new records in the tenant payments table occurrence because I allowed that through the relationship.

 

I think I understand what you're saying as far as the invoice, I may have been looking at that wrong because I was tying in an invoice to something that we would send out to a tenant and in this case we don't. They have a rental agreement and know when they are to pay so we don't send them reminders. But, I gather from what you're saying that doesn't meant we actually send it to them, rather just that the system posts it in order to create the auto debit. Thanks for your explanation.

[Hawaii80]

Okay, I have a script now working great (Craig thanks!); it looks at tenant rents and adds them as a debit as necessary. But I need to figure out how to tell it if you've already run for a particular month, then abort script. For example a custom dialog comes up and I enter rent date, I then enter 5/1/08. Globally all tenants receive a debit for whatever their rent is. But, if I accidentally run it again, or on start up (which would be preference) with the same month, I'd like some way to tell it, "you've already run this for the month of may, so abort script". Any idea?

[IBISâ„¢Robin]

Here is a long-hand way to do what ya want... there are also posts here to show you how to pull everything into one script. Not sure about the entering of the date you mentioned, I have just called it [today] below (so change this to whatever field name you have, or use a currentDate function;

 

Create three new fields (each as date output), one script (to run on opening) and add a step to [your script] that exists

 

1. create a global field, lastDebitRun

 

2. create a calculation field, lastDebitRunAdjusted=TextToDate(Month(lastDebitRun) & "/" & "1" & "/" & Year(lastDebitRun))

 

3. create a calculation field, actionDate=TextToDate(Month(Today) & "/" & "1" & "/" & Year(Today))

 

4. create a script to run at startup;

 

if [actionDate > lastDebitRunAdjusted]

perform [your script]

End if

Exit Script

 

5. add an additional step to [your script], to set lastDebitRun with currentDate

 

Using a current date (today), there really isn't any need to add or enter dates in dialog, the script does the checking for you and runs your extant script when it needs to, but perhaps you have need to alter dates and stuff, so... up to you

 

Remember to add the above script (5.) as an opening script and you should be good to go!

[IBISâ„¢Robin]

Remember to add the above script (5.) as an opening script and you should be good to go!

 

Arrrrrg! sorry, not (5.) it should read (4.) add the new script to startup, NOT repeat NOT you existing script, hope I caught ya!

[Hawaii80]

Thanks for the help Robin, I'll give it a go and let you know how it turns out!

[Hawaii80]

Create three new fields (each as date output), one script (to run on opening) and add a step to [your script] that exists

 

1. create a global field, lastDebitRun

 

I'm thinking maybe I don't need to add lastdebitrun, I have a global field date which is the date that is input into the field when I tell the custom dialog what the rent due date is. This is maybe the date you have in mind?

[IBISâ„¢Robin]

I'm thinking maybe I don't need to add lastdebitrun, I have a global field date which is the date that is input into the field when I tell the custom dialog what the rent due date is. This is maybe the date you have in mind?

 

Could be... I dunno whatcha go there. The rent due date is the first of each month as requested. lastDebitRun simply holds the date of the last time you ran the monthly debit update and is overwritten each month, if your extant field fills this role then it sounds like ya got it, the bit I don't understand is why you want to manually enter a date due when it's automatic, I might be inclined to strip that bit outta your script, whatcha need it for?

[Hawaii80]

Could be... I dunno whatcha go there. The rent due date is the first of each month as requested. lastDebitRun simply holds the date of the last time you ran the monthly debit update and is overwritten each month, if your extant field fills this role then it sounds like ya got it, the bit I don't understand is why you want to manually enter a date due when it's automatic, I might be inclined to strip that bit outta your script, whatcha need it for?

Well the manual entering is simply so I can tell the script the global date I want applied in the global date field for all debits about to be entered. From what you're saying not necessary because already covered, in the script below. If that's the case, then I'm inputting calculations wrong because it still enters new records thereby duplicating the previous entered. Here's what I've got:

 

calculation result date:

actiondate =(Month(TenantPayments::Date) & "/" & "1" & "/" & Year(TenantPayments::Date))

 

calculation result date:

lastdebitrunadjusted = (Month(lastdebitrun ) & "/" & "1" & "/" & Year(lastdebitrun))

 

lastdebitrun = date field global, not a calculation.

 

Then the script as:

 

If[Tenants::ActionDate > Tenants::LastDebitRunAdjusted]

Perform Script ["AutoRentTenant"]

End if

Exit Script []

[IBISâ„¢Robin]

calculation result date:

actiondate =(Month(TenantPayments::Date) & "/" & "1" & "/" & Year(TenantPayments::Date))

 

actionDate=TextToDate(Month(Today) & "/" & "1" & "/" & Year(Today))

 

calculation result date:

lastdebitrunadjusted = (Month(lastdebitrun ) & "/" & "1" & "/" & Year(lastdebitrun))

 

lastDebitRunAdjusted=TextToDate(Month(lastDebitRun ) & "/" & "1" & "/" & Year(lastDebitRun))

 

The rest looks correct...

[IBISâ„¢Robin]

Sorry that came out messy, the two calcs above need to be enclosed within TextToDate, even although the output may be formatted as a date, what is going on within the calc is a function of text... hence why it needs that bit extra

 

The action date should simply be based on current date, but the calc takes whichever date that is... could be the 3rd or 4th of the month by the time a weekend or holiday is out of the way, and converts it to being the 1st of the month

[Hawaii80]

Sorry that came out messy, the two calcs above need to be enclosed within TextToDate, even although the output may be formatted as a date, what is going on within the calc is a function of text... hence why it needs that bit extra

 

The action date should simply be based on current date, but the calc takes whichever date that is... could be the 3rd or 4th of the month by the time a weekend or holiday is out of the way, and converts it to being the 1st of the month

Robin,

 

Sorry if I'm asking an ignorant question here, but I'm confused on entering actionDate=TextToDate(Month(Today) & "/" & "1" & "/" & Year(Today)) in the calc box, because it tells me TextToDate is an invalid function.

 

At first I thought maybe you meant it should be a text field and then on that field do an auto calc then entering it as (Month(Today) & "/" & "1" & "/" & Year(Today)) only because I keep getting an invalid function for the TextToDate. Is your TextToDate a custom function by chance and since I wouldn't have that I'm getting the error?

 

Or am I missing something here that you have in plain english and I'm just not catching on? Sorry.

[Hawaii80]

Okay, still working on it but I see what the problem is Robin with the texttodate. I have FM 9 Pro and 9 Pro Advanced. The texttodate is an older function version 5? Which is now replaced with the getasdate function, this is why it was telling me it's an invalid function in the calculation.