Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Add-ins

Common Lookup List queries

You can use the GoFileRoom Lookup List sync service to synchronize fields such as Client Name and Client Number (ID) with your time and billing software.
You can also assign clients to a staff in the time and billing software and sync this relationship information to make custom ClientFlow notifications, which notify the right staff when a client uploads a document to GoFileRoom.
You can use the following scripts to configure the Lookup List sync for different purposes.

Lookup Lists scripts for Practice CS and NetClient CS

Pull in Client Name, Client Number, Partner email address, Manager email address, Associate email address, and Client email address to the Clients drawer where Client status is active

SELECT c.description,c.client_id,coalesce (pce.email,'') AS Partner_Email,coalesce (mce.email,'') AS Manager_Email,coalesce (ace.email,'') AS Associate_Email,coalesce (cce.email,'') AS Client_Email FROM Client_View AS c LEFT JOIN Staff AS pstf ON pstf.staff_KEY = c.partner__staff_KEY LEFT JOIN Contact AS pc ON pc.contact_KEY = pstf.contact_KEY LEFT JOIN Contact_Email AS pce ON pc.contact_KEY = pce.contact_KEY AND pce.contact_email_type_KEY = pc.primary__contact_email_type_KEY LEFT JOIN Staff AS mstf ON mstf.staff_KEY = c.manager__staff_KEY LEFT JOIN Contact AS mc ON mc.contact_KEY = mstf.contact_KEY LEFT JOIN Contact_Email AS mce ON mc.contact_KEY = mce.contact_KEY AND mce.contact_email_type_KEY = mc.primary__contact_email_type_KEY LEFT JOIN Staff AS astf ON c.associate__staff_KEY = astf.staff_KEY LEFT JOIN Contact AS ac ON ac.contact_KEY = astf.contact_KEY LEFT JOIN Contact_Email AS ace ON ace.contact_KEY = ac.contact_KEY AND ace.contact_email_type_KEY = ac.primary__contact_email_type_KEY LEFT JOIN Client AS cem ON cem.contact_KEY = c.contact_KEY LEFT JOIN Contact AS cc ON cc.contact_KEY = cem.contact_KEY LEFT JOIN Contact_Email AS cce ON cce.contact_KEY = cc.contact_KEY AND cce.contact_email_type_KEY = cc.primary__contact_email_type_KEY WHERE c.client_status_KEY = 1
Pull in Client Name, Client Number where client status is active
Select description, client_id from Client where client_status_KEY = 1
Pull in Staff Description, Staff ID to drawers such as HR and CPE where staff is active
Select description, staff_id from dbo.Staff where staff_status_KEY= 1
Pull in Staff Description for drawers such as My Files where staff is active
Select description from dbo.Staff where staff_status_KEY= 1

Lookup List scripts for Excel and NetClient CS

These scripts assume that the first tab is Sheet1 and column headers are:
Client_Name, Client_Num, Partner_Email, Manager_Email, Associate_Email, Client_Email, Active
.
Pull in Client Name, Client Number, Partner Email, Manager Email, Associate Email, Client Email from Excel
Select Client_Name, Client_Num, IIF(ISNULL(Partner_Email),'BLANK',Partner_Email), IIF(ISNULL(Manager_Email),'BLANK',Manager_Email), IIF(ISNULL(Associate_Email),'BLANK',Associate_Email), IIF(ISNULL(Client_Email),'BLANK',Client_Email) from [Sheet1$] where Active = 'Y'
Pull in Client Name, Client Number from Excel
Select Client_Name, Client_Num from [Sheet1$] where Active = 'Y'

Lookup List scripts for NetClient CS and CCH Practice

Assumptions: c.engstatus='0' active status and c.engstatus='1' inactive status.
Pull in Client Sort Field, Client Number from CCH Practice when client status is active
SELECT CltSort, Cltnum + '.' + CltEng FROM Clients WHERE engstatus = 0
Pull Client Sort Field, Client Number, Employee Email where client status is active
Select c.Cltsort, c.Cltnum, coalesce(e.EmpEmail,'') from dbo.Clients c inner join dbo.Employee e on c.Engcltptr = e.ID and c.engstatus = ‘0’
Pull in Client Sort Field, Client Number + Client Engagement, Client Email from CCH Practice when client status is active and Deleted = 0
SELECT CltSort, Cltnum + '.' + CltEng as CltNum, coalesce(CltEmail,'') as CltEmail FROM Clients WHERE Deleted ='0' and engstatus = '0'
Pull in Employee First Name and Last Name from CCH Practice when Employee status is active
select empfname + ' '+ emplname , empnum from employee where empstatus = 'A'

Add LookupList fields

You'll need to take the following steps to add Lookup List fields before you can add email fields to the Lookup List sync.
  1. Select
    Administration
    , then
    Manage FileRoom
    .
  2. Select
    Lists
    , then
    Manage Lookup Lists
    .

This article applies to:

  • Product: GoFileRoom

error-icon

error-close