I’m working with an inherited database that lists new customers on a “subscribers” table; when a customer places a second order, we move the information about their first order (via a script) into related fields (in a portal) that belong to a past transactions table called Transactions. The idea is that all past transactions end up in this Transactions table.
My question involves a calculation field that determines whether the customer has renewed more than once. I think I’ve identified the problem: the calculation only triggers if the first transaction that’s posted to the Transactions table has some data in a field called Renewal Source. Of course, those transactions don't have any renewal information because they're new orders.
The calculation in question is below. I can’t see any problems with it, but that's no surprise to me. I’m not sure if there’s a way to have it determine whether there’s renewal source data in, say, any of the past transactions, or whether there is a way to have it look for the most recent record in the past transactions first (which would have Renewal Source information, if that customer has purchased more than once). Something like that?
If(not (IsEmpty(Transactions::Renewal Source));1;0)
Is this enough information? Anything else I can share that will help?