Paid through next year calculation

[DeborahW]

Hello,

I have a small, simple database of organization members. I'd like to have a field (Paid_Year) that shows the following:

If the paid date (PD_Date) is equal to or after October 1st of the current year the field shows the following year. If it is before that date but after the previous October 1st it will show the current year (and, of course, will update as time goes by). Any other paid date would return an empty field (or perhaps "not current"). I apologize if this is really obvious. I've been playing around with it and haven't found anything that really works.

Thanks in advance for any help!

Deborah

Edited November 29, 2008 by Guest
I have tried to change the platform to Mac OS but it won't "stick".

[Kevin Frank]

1. You want to have a calculated field (number result), and make sure it's unstored. Otherwise it won't update properly in the future.

2. Here's some calc syntax for you:

Let ( [

paidDate = PD_Date ;

yearThis = Year ( Get ( CurrentDate ) ) ;

yearLast = yearThis - 1 ;

yearNext = yearThis + 1 ;

octoberThis = Date ( 10 ; 1 ; yearThis ) ;

octoberLast = Date ( 10 ; 1 ; yearLast )

] ;

Case(

paidDate >= octoberThis ; yearNext ;

paidDate >= octoberLast ; yearThis ;

""

)

) // end let

HTH,

Kevin

[comment]

You could try:

Case (

PD_Date ≥ Date ( 10 ; 1 ; Year ( Get (CurrentDate) ) - 1 ) ;

Year ( PD_Date ) + ( Month ( PD_Date ) ≥ 10 ) ;

"not current"

)

The result of the calculation should be set to Text, and the calculation must be unstored.

However, it seems that you are overwriting the PD_Date every time a payment is made. That's a rather vulnerable process. A better method, IMHO, would be to keep a related table of Payments with each payment being a separate record, with its own date and "paid for" fields. You could still see members paid status in the Members table, for example by calculating Max ( Payments::PaidFor ).

[DeborahW]

Thank you, Kevin,

I could not get this to work. I'm sure it is because I'm missing important details. I got a lot of "can't find this field", "can't find this function" type errors. Do I need to define fields for each of the "Lets" (YearThis, YearNext, etc.)? I did try this but it still didn't help and seems redundant but I don't really understand the function very well

Deborah

[DeborahW]

Thank you.

This sort of worked although I'm getting some strange results for some of the records. For instance, PD_Date in January of any year returns "not current". Also, October 1st of 2008 and other prior years returns "not current". Other dates for earlier PD_Dates return the year for which they were current at the time rather than "not current". I tried tinkering with this but I really don't know what I'm doing .

Thanks again for your help.

Deborah

[color:green]"You could try:

Case (

PD_Date ≥ Date ( 10 ; 1 ; Year ( Get (CurrentDate) ) - 1 ) ;

Year ( PD_Date ) + ( Month ( PD_Date ) ≥ 10 ) ;

"not current"

)

The result of the calculation should be set to Text, and the calculation must be unstored.

However, it seems that you are overwriting the PD_Date every time a payment is made. That's a rather vulnerable process. A better method, IMHO, would be to keep a related table of Payments with each payment being a separate record, with its own date and "paid for" fields. You could still see members paid status in the Members table, for example by calculating Max ( Payments::PaidFor ). "

[comment]

I really don't know what I'm doing

Neither do I. Does this work for you?

pddate.fp7.zip

[Kevin Frank]

Thank you, Kevin,

I could not get this to work.

I think the problem was that I didn't include the "TO::" portion of the field name in the first Let argument. Assuming the relevant TO is "Organization", and taking Comment's advice to use an unstored calculated *text* field, you should be able to copy and paste the following into the calculation definition for Paid_Year.

------------------------------------

Let ( [

paidDate = Organization::PD_Date ;

yearThis = Year ( Get ( CurrentDate ) ) ;

yearLast = yearThis - 1 ;

yearNext = yearThis + 1 ;

octoberThis = Date ( 10 ; 1 ; yearThis ) ;

octoberLast = Date ( 10 ; 1 ; yearLast )

] ;

Case(

paidDate >= octoberThis ; yearNext ;

paidDate >= octoberLast ; yearThis ;

"not current"

)

) // end let

------------------------------------

The reason I opt for such verbose code (as a training example, at any rate) is that it makes it clear what all the assumptions are, and allows the Case portion to be written in something approximating standard English.

FWIW,

Kevin

[comment]

If the calculation is defined in the same table as PD_Date, then there's no need to use the TO in the field name.

[Kevin Frank]

Hmmm... I wonder why it didn't work for her?

[comment]

That I don't know, but I am sure it's not a missing "TO::" portion. After all, if you paste your original formula into my file, it will work.

[Kevin Frank]

That I don't know, but I am sure it's not a missing "TO::" portion. After all, if you paste your original formula into my file, it will work.

Good point. Perhaps she pasted it into the FMA Data Viewer? In that case, the "TO::" portion would be required, and without it she'd get a "table cannot be found" error.

[LaRetta]

For instance, PD_Date in January of any year returns "not current".

I believe that your Pd_Date is set to text and not date. Change it and it [color:green]might work.

Edited November 30, 2008 by Guest
Changed 'would' to 'might'

[LaRetta]

BTW, Deborah, I'm referring to Comment's calculation which is the one I tested after you said you were getting strange results on SOME records. I said it [color:green]might work because I'm unsure if that is your issue. But the calculation works perfectly and that's why I suspected your data type for the field.

I too express concern over your method of replacing the Pd_Date as payments come in (if that is what you are doing). When working with people's payments (money), it is critical that an audit trail be available to easily display every payment received (along with date paid, check number etc) and separate records for each payment does just that. :wink2:

[DeborahW]

Perfectly! Thank you. You must know what you are doing (certainly more than I do!). [color:purple]I just noticed than this is exactly the same calculation that was given earlier. I believe it didn't work before due to the other settings (storage and field type) that I must have had wrong.

To everyone else: Thank you for your patience and willingness to help. I appreciate the cautions regarding financial record keeping, etc. I'm not the treasurer of this group and will leave that job to him. I just needed a simple way to sort the list so I can send membership reminders to the appropriate people.

Kind regards,

Deborah

Edited November 30, 2008 by Guest