Summary field for only a certain month
Hi, I have a revenue table that keeps track of the revenue we receive for wells. We receive revenue about 3 months behind, so I want to create a summary total field on our wells table that totals th...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendIt sounds from your post like you only receive one payment per month. So I will answer the question based on that assumption but if that is not the case then we can handle that as well.
Make a summary field on the relationship for maximum payment date and call it [Most Recent Payment Date]. That is a useful feel to have on the Wells record.
Then look up that field down to payments and make a summary field of the payment amount where the payment date equals the value in the field [Most Recent Payment Date]. Call that [Most Recent Payment].
So now the Well record always knows the most recent payment date and the most recent payment amount.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Make a summary field on the relationship for maximum payment date and call it [Most Recent Payment Date]. That is a useful feel to have on the Wells record.
Then look up that field down to payments and make a summary field of the payment amount where the payment date equals the value in the field [Most Recent Payment Date]. Call that [Most Recent Payment].
So now the Well record always knows the most recent payment date and the most recent payment amount.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
I like where this is going for a different issue I was going to ask about.
we need to track when we are missing consecutive payments, and I've been racking my brain on it.
But, back to this issue - we do receive multiple payments for a well in a month, and different amounts too, and it could be from a different operator.
Operator 1 could give 3 different payments, while operator 2 gives one full payment, on the same well. (main operator and JV operator).
------------------------------
Renee Hansen
------------------------------
- MarkShnier__YouI am not sure how the operator figures into your question. You would have to explain if that is a different table in your app or a multiple-choice field somewhere, but if you want to track multiple payments made in the most current month then "no problem".
On the payment table make a formula checkbox field called [Payment was in most current payment month?] with this formula
FirstDayofMonth([Most recent payment date]) = FirstDayOfMonth([Payment date])
Then on your summary field add up the total payments where that check box is "checked".
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------ok i'm going to give that a try.
I think I can do that for Main operator, and JV Operator.
(Operator is the oil company that drills the wells) - so Operator Payment checkbox and JV Operator checkbox. Right?
------------------------------
Renee Hansen
------------------------------- How would I track if we are missing revenue for a # of consecutive months? (3 or 6) and track if we have any months with payment, and then a month without payment?
------------------------------
Renee Hansen
------------------------------- MarkShnier__YouI suggest that you create 12 summary field son our Parent Wells record.
[Payments current month] with a filter of payment date is during the current month.
[Payments last month] with a filter of payment date is during the previous 1 months.
[Payments 2 months ago] with a filter of
ALL
payment date is during the previous 2 months.
payment date is NOT during the previous 1 months.
Continue that pattern up to 12 months ago.
Then you will have buckets on your parent records which show how much payments were made in each of the recent 12 months.
You can also create 1`2 date fields to show the date of he first day of the month for each of the 12 buckets. if you align gthen with your 12 buckets than you will know what the month is for each bucket.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------