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
------------------------------
