Pro Trust Reports and Adding Branch Grouping

[MJA-99]

On many of the custom reports we are writing, the client would like to add grouping on Branch level which corresponds to a Profile.ProfileID --> Profile.Name Fields. For the Pro Trust Transaction Reports, if one wanted to do same reports yet group by branch in addition to Trust Account, what would be the proper way to join Profile.ID to the existing Report data?

For example, is TrustAccountsByProfile the correct Place to join ProfileID which would then enable Branch grouping in custom reports? It seems when I do this that I end up with Branches that are NULL which then causes problems for data on reports.

Is there any documentation that explains how all the Transaction, Adj, Ledger, etc Tables, Views, and Functions relate to each other? I have an idea how these tables relate to each other from existing reports, yet still a little bit unclear on reasoning of Adjustments, Transactions, Ledger Tables? Where I would think Transactions would be the main table to drive a report, I many times see Adjustments? If this is confusing let me know and I rephrase my question.

[Lisa Ennis]

We do not have documentation regarding the database objects used in the ProTrust reports. SoftPro provides two different sets of standard reports, one by trust accounting date, the other by transaction date. By trust accounting date you are using the actual timestamp of the transaction and any adjustments to the transaction. The trust accounting date is not editable, however, the transaction date is (by permission).

If you are going to reconcile by transaction date, then you will want to utilize the transaction table. If you are going to reconcile by trust accounting date, you will want to you the Adjustments table. When using the adustments table, there is a record written every time the transaction is changed in some manner. Typically when using the Adjustments table, you are going to be totaling the Amount Difference field.

There is functionality in the program that lets you move ledgers in one trust account to a different trust account. The trust account reflected on the ledger is the current ledger. Use the LedgerToTrustAccount table or LedgersInTrustAccount function to determine the ledgers in a specific trust account.

Below is how I would go about getting the branch data of the order.

Code: Select all

SELECT
	L.ID AS LedgerID, 
	L.Name AS LedgerName,
	O.OwningProfileID,
	P.LocationName,
	P.CompanyCode
FROM 
	dbo.LedgersInTrustAccount('YourTrustAccountGuidHere') AS LTA
	INNER JOIN dbo.Ledger AS L ON LTA.LedgerID=L.ID
	LEFT OUTER JOIN dbo.[Order] AS O ON L.OrderID=O.ID
	LEFT OUTER JOIN dbo.[Profile] AS P ON O.OwningProfileID=P.ID

You will also need to link to the dbo.TrustAccountsByProfile to limit data returned to the reports based on the user's permissions of the person running the reports.

Also, when running reports based on date ranges, always set your date range to the minimum and maximum datetime. The dbo.DateTimeWithMinTime and dbo.DateTimeWithMaxTime functions set the time to 00:00:00.000 and 23:59:59.990.

[MJA-99]

Thank you! This is extremely helpful Lisa and great information to be aware of.

[MJA-99]

One more thing. Yes, I have made great use of your dbo.DateTimeWithMinTime and dbo.DateTimeWithMaxTime Functions.