Page 1 of 1

SQL Query Help

Posted: Fri Mar 22, 2019 10:27 am
by DKane-Element
New to the forum and new to SoftPro. Working on an ENT 31.0.2 version.

I'm trying to develop a SQL query to extract information from SoftPro so that it can be used with an iManage Work DMS. The biggest issue I'm having is the lack of relational links between tables/databases and finding unique data.

I've been instructed that PROFORM.dbo.Search is the primary table. I need to be able to determine the Cost Center that created the File, but can figure out how to link back to PROTRUST.dbo.PTCostCenter. I also need to be able to link to unique relationships between "Search" and PROFORMLOOKUPS.dbo.<Various lookups>.

Any help would be greatly appreciated in understanding the connections.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 1:13 pm
by Gil McNeill
Hi..unfortunately when an order is created in ProForm, the cost center that creates it is not saved. The cost centers are used in order creation only to give the user some options related to available Trust Account Codes and the way orders are named. A "workaround" for that would be to set up (in SPAdmin) a specific order numbering pattern for each cost center. You could preface each order number with the cost center.

For example..you could have it generate order numbers for Cost Center #1 (CS1) and Cost Center #2 (CS2) that look like this..

CS1-20190001
CS2-20190001

That way just looking at (or searching for) the order number in the database (Search table) you know what cost center it's associated with.

As far as a relationship between Search & ProFormLookups tables...there is not really a relationship between the two. The lookups database is used in ProForm when you do a lookup in an order and bring in an entry from the lookup table. Like if you choose a specific lender from the lender lookup, it puts that lender's info in your order directly...there's no link to the record in the lookup table.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 2:13 pm
by DKane-Element
Thank you for the information.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 2:24 pm
by DKane-Element
So this is the situation I'm running into, from a lookup perspective...

On the Search table, it stores in the LenName field the Name1 from the Lender lookup. Unfortunately there are about 10 rows in the Lender Lookup that all have the same Name1. How do you determine which LENDER.CODE or LENDER.ID was selected when it was selected from the Lender lookup?

Re: SQL Query Help

Posted: Mon Mar 25, 2019 2:39 pm
by Gil McNeill
The lender code (from the lookup) is not stored in the database. It's just stored in the LENINI field in the order itself.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 3:21 pm
by DKane-Element
Next stumper... Parcel ID number? I have access to the Brief Legal description (BLegal), but need to be able to query on the Parcel ID.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 3:23 pm
by DKane-Element
I'm starting to get the impression that a lot of what I'm looking for is stored in the physical PFD for the FirmFile and not in the database.

Re: SQL Query Help

Posted: Mon Mar 25, 2019 4:04 pm
by Gil McNeill
Correct...100% of the data is in the ProForm data file. Only a small subset is captured in the database for searching and reporting. You can add columns to the database using the ProForm Data Collector. It allows you to collect data on fields that aren't default in the database.

You can read about the data collector at the URL below: (search for "ProForm Data Collector")

http://help.softprocorp.com/classic/v31 ... CSHID=1429