Overlapping disbursements

[David Nelson]

Attached sample. Payment is for rental period which can overlap months. But I need to get the 'total per month'. All I can think is to create a table with months or days?

So I have $1845 for period of 12/14/2011 - 2/3/2012 which is 36.1764705882352941 per day. I need to figure out how much applies to December, 2011, January, 2012 and February 2012. I think I can get that but ... where do I store that information? Do I need a month table or a day table and if so, how do I transfer that information to it?

I have not answered on that other thread yet, which is different question, but I was pulled onto this first. Ideas appreciated. How can I take a rental range payment and provide reporting for the month?

Disburse.zip

[comment]

I need to figure out how much applies to December, 2011, January, 2012 and February 2012. I think I can get that but ... where do I store that information?

That's a very good question. Ideally, nowhere - because that information is not information at all; the original data already contains all the facts. So the real question is why, where and for what purpose do you require this split? For example, if I wanted to report on the month of January 2012 alone, I would make all records report only their January 2012 portion (based on January 2012 being selected in a global field or variable).

[David Nelson]

So the real question is why, where and for what purpose do you require this split? For example, if I wanted to report on the month of January 2012 alone, I would make all records report only their January 2012 portion (based on January 2012 being selected in a global field or variable).

Our Bookkeeper wants the date the payment is made to be the month that all the payment dollars apply. This is, he explains, standard bookkeeping rules - that the full payment applies to the month the payment was made, so for him it works as expected. But the Sales Manager wants to compare the rentals against the expenses which are entered as they happen in real time throughout the rental period. Some rentals are three days, some six months and a few rentals are more than a year. He says he wants a record for every day so we can compute the way he needs. Luckily I do not listen to what he wants but I am trying to listen to what he needs.

I did not want to split the date range into day records, creating one record per day for the rental period because the rental payment is not flat rate per day, weekdays are different than weekends, group rates, etc. The payment is for a RANGE and I have studied templates and posts here, I am sure by you, and I have read that a record for each date is not recommended and I am trying to avoid creating a day table.

I will need to give him his report. Owner wants him happy because he keeps us in business. So it looks like I will need to create month records to hold this split and use script to write to it somehow? This is the worst situation I have faced so far.

What about if I write the split to the invoice as multiline like this:

12 - 325.46

01 - 1422.37

02 - 114.00

?

I think that would probably be worse than a month table because I won't be able to create a report using those figures in a three-month report as

December 2011

....

January 2012

....

I have been learning and reading but it seems relationships still boggle me.

[comment]

He says he wants a record for every day so we can compute the way he needs. Luckily I do not listen to what he wants but I am trying to listen to what he needs.

I completely agree - but it's still not clear what he needs. What exactly is the format of "his report"?

[David Nelson]

For the year or month requested and all rentals are pay in advance. He drew this out for me as example what he wants

December, 2011

Gas ............. 300.00

Parts ............125.25

Labor ........... 820.00

Payments ....-1,245.00 <--- pro-rated all payments made for just December's days of rental

January, 2012

Gas ................ 450.00

Cleaning ..........55.21 ...etc

Payments .......-1,250.00 <----- pro-rated all payments made for January's days of rental

He says he wants to 'spread the payments for comparison purposes so he knows month to month the ratio.' He showed me the printouts he gets from AccPac now and he hand-types them into excel. He is taking the totals which appear to be summaries of only company INTERNAL and it holds maintenance charges as plus and payments as minus but the payments all skew onto their first month because they pay at the start. So he has to manually divide them across their periods and re-add them.

I asked Owner if we couldn't take the original payment and create multiple payment records one for each month if more than one month, splitting into multiple but no, we can't. I would want to use sub-summary report, no body, and only one customer = "internal" and only some Categories AND I would need to omit payments but include a calculation in the month leading part which sums only payments... this is where my thinking breaks down. I will need to get a day average per invoice, apply that math to the month as a (what?).

Maybe repetition calculation at Invoice level with payment spread across then summing rep 1 for January. Maybe merge variable in leading part. I have standard invoices I am designing. Nothing like this. Yes. I am grasping straws.

[comment]

What, only 2 months at a time?

Where are the other items (gas, parts, etc.) coming from?

[David Nelson]

Sorry, I was lazy and only showed two but it could be a year's worth. From what I saw, it looks like he adds the month columns on himself and there were 15 columns so it was showing last year plus two months this year and a category column. He said he mostly wants to know the trend change per month but to know the 'change' means knowing spans of time so one month wouldn't make sense and at least three prior months would make more sense as minimum.

The categories are account summaries report by company from AccPac. In FileMaker they will be the invoice lineitems table. Every lineitem will be required to have a Category and these Categories all are Type = "Maintenance" (I just discovered this). So 'where from' response will be invoice LineItems. This is also where Payments are applied against invoices, just in the wrong spots according to him. But bookkeeping insists payment is the date paid period and none of them will budge.

I hope this answered your question. It isn't designed yet in FileMaker other than standard invoices like your invoice demo. They just want to be sure that I design it so this piece can be accomplished.

[comment]

So 'where from' response will be invoice LineItems. This is also where Payments are applied against invoices

That's not quite clear. Are the actual payments in the same table?

[David Nelson]

Yes, sorry. Each payment is added as a lineitem and applied to its original invoice. It may cover three months but full payment is recorded the first day of the rental period. This is part of books requirement. Payment dated first day of rental period even if entered few days before which is why I cannot even use creation date for the payment, I must use 'DatePaid.'

[LaRetta]

As an aside as you work through this, David ...

So when you enter a Payment, you use auto-enter calculation on the LineItem::DatePaid as:

Case ( LineItems::Category = "Payment" ; Invoices::DateFrom )

... is this correct? So your rental period is actually stored in Invoices and not LineItems?

[comment]

Each payment is added as a lineitem

OK, so would something like this work for you? You will need to make an adjustment for line items that are not payments.

ProRataPerDiemR.zip

[David Nelson]

Actually, I had not thought of doing that, LaRetta. The range is in the invoice so that would work and it will save me a step when payment entered to use auto-enter.

wow Comment. I do not understand it but I am convinced that this is it. I changed cMonthAmountsR to cDaysR to view and I see the way it works but I have no idea how.

I will let you know if I get stuck but it looks just right. Calculation Max(Min(... who but you would think of that. You are brilliant at this stuff. Thank you both for the help you have given me.

[David Nelson]

That worked perfectly in my file, Comment. :king:

[David Nelson]

Hi there Comment,

Is possible to adjust this file for a mistake I made? Our managers now are testing this file. We are finally served at least for testing. And was recently asked if number of days can be on report and I readily obliged by placing cDaysR on the layout and extending its repetitions. At least I had figured that much out. But Manager says it gives one day too many and I just realized I am sure it is because of how we figure things. I did not think about that.

Rentals begin at noon and must be returned at noon. Example of: A rental period which is from 12/31/2011 through 1/1/2012 is not considered two days but rather one day for total rental of $250.00 so it should show $250 on Dec 2011 and nothing in January. The charge starts against the start date day, if that makes sense.

I have tried changing it myself but everything I do ruins it. I can not attach our file but it is identical to yours from post #11 above. Ideas on adjusting it would so appreciated.

[comment]

This way?

ProRataPerDiemR2.fp7.zip

[David Nelson]

Hello Comment,

I had to place the calcs side by side to compare to see what was different. Seems you added 1 day in days and removed one day in month amounts. I never would have figured that out although I knew I needed to remove one day somehow.

My sincere gratitude for providing me this concept It is almost magical and it impresses our sales manager and that takes a lot. More importantly it gives him something which saves him 10-15 hours a week, he said. And that impresses his boss which impresses me to the max.

Best wishes,

David