From a beginning date (month count BY year)

[Achineche]

I hope this is the right place to place the tread, the forum is incredible big.

First of all I apologise for my little-used English, I’ll try to explain as best as I can.

Say a padel/chess/whatever association.

I can calculate the Monthly Fee from an annual Budget, member x, $xxxx, and then apply it to the months owed, but the number of months I get now is the total from the beginning date to current date and budget’s amount changes every year, so, I need that total, but separated by year, and I don’t know how to do that, my knowledge in FM is still weak.

Sample records: Say

Member 1, date of membership 09/01/2009... if his monthly fee is 2009=8.83, 2010=10.68 and 2011=13.97

Months since date of membership=25 ok

but what I need is

2009=12 so I can calculate 12*8,83

2010=12 so I can calculate 12*10.60

2011=1 so I can calculate 1*13.97

I appreciate very much your help

I attach a file to let you see what I’m trying to explain. It’s in Spanish but very simple, so I understand it won’t be any problem.

Thanks again.

Month_Count_BY_Year.zip

[comment]

Can you explain what is being given and what needs to be calculated (or generated)?

I understand you start with an annual budget - but how do you determine the monthly fee? Why do different members have different quotas? And what should happen if the number of members changes during the year?

[Achineche]

All I need is to calculate the months, don’t bother about the other data.

Given.

1. Date from which start paying. (Date1).

2. Date of Annual budget. (Date2).

I need.

To count the months between both dates, but in groups of years.

Things to be aware of: (on my poor opinion)

- budget is annual, what day and month should be set?, should it fall on the last day of the year? because if for example date1 is grater than date2, there’s no month to calculate, but when the budget is actual year, place it’s date the last day of year will count months that haven’t pass yet.

- if date1 and date2 are in the same year, date2 should be current date?

...

Maybe could be easier. Now I can count all months between date1 and date2, could it be possible to extract those month by it’s own years.

Total months 18 ( 2009=5, 2010=12, 2011=1).

Thanks for your help.

[comment]

All I need is to calculate the months, don’t bother about the other data.

But I do "bother" about the other data, because it's all connected. The way you have it now, you would need either a custom function or a script to "split" the elapsed months between the years. However, I am not at all convinced that the way you have it now is a good way overall.

budget is annual, what day and month should be set?

None, I would think. You should enter the year into a Number field. Use the Get (CurrentDate) function to figure out which monthly payments are due (or overdue).

[Achineche]

All data.

MEMBERS:

MemberID:...

DateOfMembership:...

Quotas: the 100% is divided into x members depending on the uses of the club.

BUDGET:

Amount:...

Date: the budget for that year.

BUDGETLINES:

AnnualFee: budget::amount / members::quotas

MonthlyFee: annualfee / 12

MonthCalc: If ( members::DateOfMembership >= Get ( CurrentDate ) ; ( Month ( Members::DateOfMembership ) - Month ( Get ( CurrentDate ) ) ) + ( ( Year ( Members::DateOfMembership ) - Year ( Get ( CurrentDate ) ) ) * 12 ) ) - If ( Get ( CurrentDate ) >= Members::DateOfMembership ; ( Month ( Members::DateOfMembership ) - Month ( Get ( CurrentDate ) ) ) + ( ( Year ( Members::DateOfMembership ) - Year ( Get ( CurrentDate ) ) ) * 12 ) ) + If ( Day ( Members::DateOfMembership ) >= 15 ; 0 ; 1 )

I don’t know if this is the right way to do it, it’s the only I know, as I’ve said I don’t know how to do it. My knowledge in filemaker calculations is very short. I do what I can.

Thanks again for your help. I really appreciate it.

[comment]

My knowledge in filemaker calculations is very short.

But I haven't asked about that (I can see this in your file). I am asking about what are you actually trying to accomplish here. So Adam has been a member since October 2009 and he was supposed to pay:

3 * 8.83 + 12 * 10.6 + 13.97 = 167.66

Now what? Where are the payments he did pay? Do you plan to always calculate the total dues of every member, from the very beginning of their membership? What about a member that quit, and came back a year or two later?

What I'm trying to say that you cannot have a data model without having a business model first - and I don't see one here. Usually, this kind of thing is handled either by having yearly (or another time-frame) subscriptions, or by pre-generating a fee schedule that the members need to follow.

[Achineche]

I’m sorry if I made you mistaken. Maybe I tried to make it too simple.

The file is as simple as I could do it to send it and ask for that calculation. I’m not asking anyone for a whole solution, I made that as an example.

So Adam has been a member since October 2009 and he was supposed to pay:

3 * 8.83 + 12 * 10.6 + 13.97 = 167.66

I can’t get to there. I don’t have the months counted by year, all I have is 16 months, but I don’t know what year they are related to.

What to accomplish?

Business model.

Neighbouring community. I don’t know if that’s the right way to name it in English.

Sample:

- Someone builds a building that has 40 flats.

- The owner of the building sets the different quotas (depending if a flat is bigger than others, has better views, whatever), and they remain the same.

- Owners make a community to pay common services, so every year they make a budget and pay in proportion to their quotas.

- The community doesn't exist until there are at least 5 owners (including the owner of the building), so nothing has to be paid before that. The 4 new owners pay their own quota and the owner of the building pays the other 36.

Answering to your questions:

Do you plan to always calculate the total dues of every member, from the very beginning of their membership?

That is correct. Even if an owner wants to sell his flat, he can’t until he is updated with the community, or the new one pays it.

What about a member that quit, and came back a year or two later?

There’s always an owner of the flat. The flat is the one that owes no matter who the owner is.

Thank you for your time.

[comment]

OK, that's much clearer. The way I would approach this is to pre-generate a payment schedule at the beginning of every year: 12 records, one for each month, with the amount being simply the annual budget / 12. This way you can have a relationship between Flats and Schedule based on a date range. The total charge for a flat is the sum of related payments * quota.

Another option is to generate a record for each month/flat combination, i.e. if you have 20 flats, you'd generate 240 (12*20) "receipts" at the beginning of each year. Here you can mark receipts as paid, and sum unpaid receipts to calculate the debt of each flat.

[Achineche]

I can't make it.

I've tried but I don't know how to do it. I have tried to make your second option but it's too much for my knowledge by now.

I post the sample file but I don't know if I'm near the solution or far far away, lol.

I will apreciate some help.

Thank you.

Community.zip

[comment]

Here's a sketch of what I meant (the first option). It needs more work on (a) generating the months and ( prorating the first and the last month.

MonthlyFee.zip

[Achineche]

Thank you very much for the file. I'm working on it.

I'd ask for you advise in a few things:

1. How could I make a script to fill all months of the year at once?

2. I need the first month not to be counted if the day in the FromDate field is < than 15, and counted if it's 15 or greater.

3. The current month represented by cToday is not counted untill expires, and I need to be counted from the first day.

Thank you very much for your help. I really apreciate you are spendding your time giving me a hand.

[comment]

1. Roughly:

Check if there are related records in the MonthlyFees table. If not, set a $variable to the year in question, go to the MonthlyFees and loop to create 12 records. Set each record's Month field to the appropriate date.

Re 2 & 3, I believe this should become easy once you understand how the demo file works.

[Achineche]

I have done this script that works, but I can't increase the date in one month.

If [ YearlyBudgets::Year ]

Set variable [ $year; Value:YearlyBudgets::Year ]

Go to Related Record [show only related records; From table: “MonthlyFees”; Using layout “MonthlyFees” (MonthlyFees) ]

Go to Layout [“MonthlyFees” (MonthlyFees) ]

End If

Set variable [ $months; Valor:12 ]

Set variable [ $newdate; Valor: Date ( 1 ; 1 ; $year ) ]

Loop

Exit Loop If [ not $months ]

New Record/Request

Set Field [ MonthlyFees::year; $year ]

Set Field [ MonthlyFees::month; $newdate ]

Set variable [ $months; Valor:$months - 1 ]

End Loop

[comment]

Try (untested):

If [ not MonthlyFees::Year ]

Set Variable [ $year ; YearlyBudgets::Year ]

Go to Layout [ MonthlyFees ]

Loop

Set Variable [ $i ; $i + 1 ]

Exit Loop If [ $i > 12 ]

New Record/Request

Set Field [ MonthlyFees::Year ; $year ]

Set Field [ MonthlyFees::Month ; Date ( $i ; 1 ; $year ) ]

End Loop

Go to Layout [ original layout ]

Else

Beep

End If

[Achineche]

Your script is tested and works PERFECTLY. It’s awesome, simple, clear and I can understand it!, that’s even more awesome, lol.

Working on it and trying to generate 12 months for apartment, I got stucked.

My idea was to place the loop that generates the 12 months into another loop that rules the apartment numbers, the one inside makes for it the 12 months and then start over again with the next apartment number, but I can’t make it work

If [ not MonthlyFees::Year ]

Set Variable [ $year; YearlyBudgets::Year ]

Go to Layout [ MonthlyFees ]

Loop

Set Variable [ $aptmt; Apartments::AptmtID + 1 ]

Exit Loop If [ not $aptmt ]

Loop

Set Variable [ $i; $i + 1 ]

Exit Loop If [ $i > 12 ]

New Record/Request

Set Field [ MonthlyFees::Year; $año ]

Set Field [ MonthlyFees::Month; Date ( $i ; 1 ; $year ) ]

Set Field [ MonthlyFees::AptmtID; Apartments::AptmtID ]

End Loop

End Loop

Go to Layout [ original layout ]

Else

Beep

End If

Thank you for your help.

[bruceR]

You will have to initialize $i in every outer loop:

If [ not MonthlyFees::Year ]

Set Variable [ $year; YearlyBudgets::Year ]

Go to Layout [ MonthlyFees ]

Loop

Set Variable [ $aptmt; Apartments::AptmtID + 1 ]

Set Variable [$i; 0]

Exit Loop If [ not $aptmt ]

etc

[comment]

Loop

Set Variable [ $aptmt; Apartments::AptmtID + 1 ]

Exit Loop If [ not $aptmt ]

1. This loop will never exit.

2. I don't think Apartments::AptmtID will return a value in this context: you are on a layout of YearlyBudgets and there are (yet) no related records in Apartments.

You could do something like:

If [ not MonthlyFees::Year ]

Set Variable [ $year ; YearlyBudgets::Year ]

#

Go to Layout [ Apartments ]

Show All Records

Go to Record [ First ]

#

Loop

Set Variable [ $aptID ; Apartments::AptmtID ]

Go to Layout [ MonthlyFees ]

#

Loop

Set Variable [ $i ; $i + 1 ]

Exit Loop If [ $i > 12 ]

New Record/Request

Set Field [ MonthlyFees::Year ; $year ]

Set Field [ MonthlyFees::AptmtID ; $aptID ]

Set Field [ MonthlyFees::Month ; Date ( $i ; 1 ; $year ) ]

End Loop

Set Variable [ $i ; "" ]

#

Go to Layout [ Apartments ]

Go to Record [ Next, Exit after last ]

End Loop

#

Go to Layout [ original layout ]

#

Else

Beep

End If

[Achineche]

I apologise for the "gap", I've been on holidays.

Comment, you are right, the loop never exits, now works perfectly and does what i was looking for.

I have learned a lot.

Thank you ALL.