Hey - I haven’t forgotten about you. I’m pushing a bunch automation updates last night and today.
The description field is primarily used by payment integrations to store transaction data. There is a post by QMcKay from way back about storing manually-inputted data after a payment transaction.
I don’t think there’s an event for change payment (maybe on a new version - not sure) and having a user input the value is tedius and error-prone.
I have some ideas I will try later on today.
Credit Card payment info is stored in the PaymentData field.
There is an action to update Payment Description:
I used this action to update payments on PreSales (Reservations, down payments, etc.)
Thank you, I was able to use the code that I have created to produce good results. But I very interested in any solution you have.
I don’t think we will do any payment integration so this field would work great for us.
Thank you.
@Bob_be very interesting I will see what automated options available, then try inputting info in the description field.
The code is a no-go, did some other test and the result was off. I will review and provide an updated code.
This is the way I had to present the report currently
@{REPORT PAYMENT DETAILS:P.User,P.Amount.Sum::{0}:,}
[Settled by $1:2, 1, 2]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Sum:(PU=$1)}
>Change issued by $1
({REPORT CHANGE PAYMENT DETAILS:P.Name, P.Amount.Sum:(PU=$1)})
>Total Income|[=F(TN('{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU=$1)}')-TN('{REPORT CHANGE PAYMENT DETAILS:P.Amount.Sum:(PU=$1)}'))]
So had to show the change separately so the manager will need to do manual calculations to get the amount for each transaction type
@Memo wow think I cracked it, using your same code above
long but working
DECLARE @UserName NVARCHAR(255) = N'@1';
DECLARE @StartDate NVARCHAR(25) = N'{Start}';
DECLARE @EndDate NVARCHAR(25) = N'{End}';
DECLARE @UserId INT;
SELECT @UserId = Id
FROM dbo.Users
WHERE Name = @UserName;
IF (@UserId IS NOT NULL)
BEGIN
select pp.Name, sum(pp.pamount - ISNULL(cp.pamount,0)) as payment
from (select p.UserId, p.Name, CONVERT(DECIMAL(16, 2), ROUND(SUM((p.Amount) / p.ExchangeRate), 2)) as pamount
from dbo.Payments p where p.Date between @StartDate AND @EndDate
group by p.UserId, p.Name) pp left join (
select cp.UserId, cp.Name,CONVERT(DECIMAL(16, 2), ROUND(SUM((ISNULL(cp.Amount, 0)) / cp.ExchangeRate), 2)) as pamount
from dbo.ChangePayments cp where cp.Date between @StartDate AND @EndDate
group by cp.UserId, cp.Name) cp
on pp.UserId = cp.UserId and pp.Name = cp.Name
where pp.UserId = @UserId
group by pp.UserId, pp.Name
END;
sorry for not capitalizing the sql keywords.
Long 2 days for me
If it works it works! Well done.
I was thinking about seeing if there was a way to grab the payment account from the payment transaction then aggregate/join on change payments. Or just mash everything into a temp table and select/sum from there. I just haven’t gotten around to it.
I don’t either. I use SQL Prompt to format the DDL/DML.