Ledger Transfer
Moderator: Lisa Ennis
-
- Posts: 3
- Joined: Thu Jan 18, 2024 11:26 am
Ledger Transfer
I'm having trouble finding a specific field in the database. It's the "Transferred To" field under Transfer In section of a ledger transaction.
-
- Posts: 7
- Joined: Fri Jan 06, 2017 9:50 am
- Contact:
Re: Ledger Transfer
The ledger transfers To/From (FTO / FTI) are linked by the related transaction ID. For the current transaction you can find the related transaction ID in the pt.TransactionExtened table (RelatedTransactionID). This will join back to the corresponding transaction/ledger to get the other side of the transfer.
Example:
SELECT
T.ID AS TransactionID,
L.Name AS TransferOutFrom,
T.Kind,
T.Amount,
TE.RelatedTransactionID,
L2.Name AS TransferInTo,
TTF.Kind,
TTF.Amount
FROM
pt.[Transaction] T
INNER JOIN pt.[Ledger] L ON T.LedgerID = L.ID
INNER JOIN pt.TransactionExtended TE ON TE.ID = T.[GUID]
INNER JOIN pt.[Transaction] TTF ON TE.RelatedTransactionID = TTF.ID
INNER JOIN pt.[Ledger] L2 ON TTF.LedgerID = L2.ID
WHERE
T.IsCurrent = 1
AND T.Kind = 'FTO'
Example:
SELECT
T.ID AS TransactionID,
L.Name AS TransferOutFrom,
T.Kind,
T.Amount,
TE.RelatedTransactionID,
L2.Name AS TransferInTo,
TTF.Kind,
TTF.Amount
FROM
pt.[Transaction] T
INNER JOIN pt.[Ledger] L ON T.LedgerID = L.ID
INNER JOIN pt.TransactionExtended TE ON TE.ID = T.[GUID]
INNER JOIN pt.[Transaction] TTF ON TE.RelatedTransactionID = TTF.ID
INNER JOIN pt.[Ledger] L2 ON TTF.LedgerID = L2.ID
WHERE
T.IsCurrent = 1
AND T.Kind = 'FTO'
Brandon Ritchie
Reports Technical Team Lead
SoftPro
Reports Technical Team Lead
SoftPro