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
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__You
Qrew Legend
Qrew LegendI 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
------------------------------
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
------------------------------