Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Add-ins

Lookup List scripts

The GoFileRoom Lookup List Sync Utility synchronizes fields such as
Client Name
and
Client Number (ID)
 with your time and billing software.
You can also assign clients to staff in the time and billing software and sync this information to make custom ClientFlow notifications, which notify the staff when a client uploads a document to GoFileRoom.
You can use the following scripts to create a Lookup List Sync:
  • Lookup List scripts for Practice CS and NetClient CS
  • Lookup List scripts for Excel and NetClient CS
  • Lookup List scripts for CCH Practice and NetClient CS

Lookup Lists scripts for Practice CS and NetClient CS

  1. Pull in Client Name, Client Number, Partner email address, Manager email address, Associate email address, and Client email address to the Client's draw 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
  2. Pull in Client Name, Client Number where client status is active: 
    Select description, client_id from Client where client_status_KEY = 1
  3. 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
  4. 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
.
  1. 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'
  2. 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.
  1. Pull in Client Sort Field, Client Number from CCH Practice when client status is active: 
    SELECT CltSort, Cltnum + '.' + CltEng FROM Clients WHERE engstatus = 0
  2. Pull in 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’
  3. 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'
  4. 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'

This article applies to:

  • Product: GoFileRoom
  • Subject: setup

Related content

error-icon

error-close