SQL Query Help

Discussions related to integration with SoftPro Standard or SoftPro Enterprise editions.
Post Reply
DKane-Element
Posts: 5
Joined: Wed Mar 20, 2019 3:59 pm
Location: Minneapolis, MN
Contact:

SQL Query Help

Post 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.
Gil McNeill
Posts: 16
Joined: Mon Oct 06, 2008 9:29 am

Re: SQL Query Help

Post 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.
Gil McNeill
SoftPro Software Engineer
DKane-Element
Posts: 5
Joined: Wed Mar 20, 2019 3:59 pm
Location: Minneapolis, MN
Contact:

Re: SQL Query Help

Post by DKane-Element »

Thank you for the information.
DKane-Element
Posts: 5
Joined: Wed Mar 20, 2019 3:59 pm
Location: Minneapolis, MN
Contact:

Re: SQL Query Help

Post 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?
Gil McNeill
Posts: 16
Joined: Mon Oct 06, 2008 9:29 am

Re: SQL Query Help

Post 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.
Gil McNeill
SoftPro Software Engineer
DKane-Element
Posts: 5
Joined: Wed Mar 20, 2019 3:59 pm
Location: Minneapolis, MN
Contact:

Re: SQL Query Help

Post 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.
DKane-Element
Posts: 5
Joined: Wed Mar 20, 2019 3:59 pm
Location: Minneapolis, MN
Contact:

Re: SQL Query Help

Post 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.
Gil McNeill
Posts: 16
Joined: Mon Oct 06, 2008 9:29 am

Re: SQL Query Help

Post 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
Gil McNeill
SoftPro Software Engineer
Post Reply