List details from 3 subgrids into one list 

create view Discovery as  

select page0.security, page0.serial, page0.clientnm, page0.clientmatter, page14a.CLDesc as Description, page14a.CLRespDue as RespDue, 'Client' as Type from page0 

left outer join page14a on page14a.caseserial = page0.serial 

where CLComp='N' 

union 

select page0.security, page0.serial, page0.clientnm, page0.clientmatter, page14b.OPDesc, page14b.OPrespDue, 'Opposing Party' as Type 

from page0 

left outer join page14b on page14b.caseserial = page0.serial 

where OPComp='N' 

union 

select page0.security, page0.serial, page0.clientnm, page0.clientmatter, page14c.ODDesc, page14c.ODrespDue, 'Other' as Type 

from page0 

left outer join page14c on page14c.caseserial = page0.serial 

where ODComp='N' 

GO 

In SmartFolder use the following SQL 

select page0.security, page0.serial, Discovery.* from page0 inner join Discovery on Discovery.serial = page0.serial 

create view InsuranceCoConflict as 

select page0.serial,page0.clientno, page0.fname, page0.lname, page8.insuranceco as link, contact0.lname as inscarrier, 'InsuranceCo' as type from page0 inner joinpage8 on page8.caseserial = page0.serial inner join contact0 on page8.insuranceco = contact0.linkserial 

union all 

select page0.serial, page0.clientno, page0.fname, page0.lname, page8.Holderinsuranceco as link, contact0.lname as inscarrier, 'HolderInsuranceCo' as type frompage0 inner join page8 on page8.caseserial = page0.serial inner join contact0 on page8.holderinsuranceco = contact0.linkserial 

GO 

 The SQL of the SmartFolder is: 

select page0.serial, page0.security, Insurancecoconflict.* from page0 inner join insurancecoconflict on insurancecoconflict.serial = page0.serial 

This is a way to use a view to especially cross FlexBases.  If you have information that can use a union (name number and names of columns), create a view with the union and then create the SmartFolder.  NOTE:  One issue with this – the SmartFolder results will not use security.  So if there is a case that the user doesn’t have rights to, they will see the case on the results BUT they will not be able to double click to get into the case.  So potentially security on the SmartFolder should be used if necessary. 

Create the View but needs three fields:  serial, security and flexbase. 

CREATE view [dbo].[TODAYSNOTES2] AS  

select  page0.serial, security, lname as name,noteby,memo, 1 as FlexSerial, 

notetype, notespent, 

Cast(notespent/60 as varchar)+' Hours'+'  '+ Cast(notespent %60 as varchar)+' minutes' as SpentHours,duedate,  

substring(convert(varchar(20),duedate,8),1,5) as d1, 

substring(cast(duedate as varchar(18)),13,8) as d2, 

'Client' as Source, 

'url:lawbase:commonitem(notes)='+cast(notes.serial as varchar)+ '~' + 

substring(convert(varchar(20),notes.duedate,8),1,5) +' ' + isnull(notes.notetype,'')  

as link, notes.notebill, notes.consult 

from  Page0 

inner join notes on PAGE0.SERIAL = NOTES.CaseSerial  

where NOTES.DUEDATE >= dateadd(dd,0,datediff(dd,0,getdate())) and NOTES.DUEDATE < DATEADD(day,1,getdate()) 

union all 

select  contact0.serial, security, name,noteby,memo, 2 as FlexSerial, 

notetype, notespent, 

Cast(notespent/60 as varchar)+' Hours'+'  '+ Cast(notespent %60 as varchar)+' minutes' as SpentHours,duedate,  

substring(convert(varchar(20),duedate,8),1,5) as d1, 

substring(cast(duedate as varchar(18)),13,8) as d2, 

'Contact' as Source, 

'url:lawbase:commonitem(notes)='+cast(notes.serial as varchar)+ '~' + 

substring(convert(varchar(20),notes.duedate,8),1,5) as link, notes.notebill, notes.consult 

from  Contact0 

inner join notes on  Contact0.SERIAL = NOTES.CaseSerial  

where NOTES.DUEDATE >= dateadd(dd,0,datediff(dd,0,getdate())) and NOTES.DUEDATE < DATEADD(day,1,getdate()) 

The SmartFolder SQL Rule would be just select from the view (don’t need the flexbase join since it was included in the view) 

Select * from TodaysNotes2 

SQL can combine multiple result rows into a single field separated by commas.  Starting with SQL 2017 there is a built in SQL function named string_agg to do this.  Previous versions of SQL require a somewhat complex combination of functions.

Example of SQL used with the older combination of functions versus using the built in string_agg function.

Prior to SQL2017:

select page0.serial, security, typelaw.typename, dept, vendorco, page0.conname, status, contno,

   STUFF((SELECT ', ' + isnull(Keyplayers.contactname,'') 

                        FROM KeyPlayers 

   WHERE

                        KeyPlayers.Serial = page0.serial order by KeyPlayers.Contactname

                        FOR XML PATH('') 

                        ), 1, 1, '' )

                                   AS [KeyPlayers]

from page0 inner join typelaw on typelaw.serial = page0.typelaw

order by page0.conname desc

SQL 2017 or newer:

select page0.serial, security, typelaw.typename, dept, vendorco, page0.conname, status, contno,

  String_AGG(Keyplayers.contactname,', ') as KeyPlayers

from page0 inner join typelaw on typelaw.serial = page0.typelaw

left outer join keyplayers on keyplayers.serial = page0.serial

group by page0.serial, security, typelaw.typename, dept, vendorco, page0.conname, status, contno

order by page0.conname desc

It might be desirable to calculate an age of a person.  The following SQL function can calculate this.  (Assumes that dob and dod are on the same table of a page that belongs to a flexbase.) 

Create FUNCTION [dbo].[CalculateAge]( 

@serial int 

) RETURNS int 

BEGIN 

--declare @serial int = 1 

DECLARE @result int, @DOB datetime, @END datetime 

select @DOB = isnull(DOb, getdate()) from page1 where serial = @serial 

select @END = isnull(DOD, getdate()) from page1 where serial = @serial 

Select @result = (CONVERT(int, CONVERT(varchar, @END, 112)) - CONVERT(int, CONVERT(varchar, @DOB, 112)))/10000  

RETURN @result 

To use, you'd something like: 

Select [dbo].[getage](serial#) 

Where serial# is the serial number of the page in question. 

When you have a calendar entry displayed on the screen, you may need to get at all the whofor list that is currently on screen.
WorkFlow from a calendar item has a special variable called <<Var.WhoForList>> available after 11/29/2023 build. This variable is a comma separated list of whofor list that is in memory before the calendar is saved.    You can use the <<Var.WhoForList>> to obtain information such as the email from the user_Profile. 

Example: Get a comma separated list of emails. (Using SQL2014 or lower) 
<<Sql(SELECT STUFF((SELECT ',' + email FROM user_profile WHERE CHARINDEX(',' + login + ',', ',' + '<<var.WhoForList>>' + ',') > 0 FOR XML PATH('')), 1, 1, ''))>> 

Example: Get a comma separated list of emails. (Using SQL2017 or better) 

<<sql(select String_AGG(email,', ') from User_Profile where login in (select value from string_split('<<var.WhoForList>>',',')))>> 

Example: Using SQL2017 or higher, to get the data returned with separate rows that can be used in a WorkFlow loop step to act on each one separately. 
<<sql(select email from user_profile where lower(login) in (SELECT lower(value) FROM STRING_SPLIT('<<var.WhoForList>>', ',')))>>

Use an executable WorkFlow step. Put LBURL.exe in the program entry (might need to use the full pathname) and place lawbase:SmartFolder=100064 in the parameter field where 100064 is the serial number of the SmartFolder you want to focus. Don't use quotes in the actual entry. This can be used for any type of LBUrl allowed. For instance to open another case file, you could place lawbase:caseserial=21489 in the parameter field. Replace 21489 with the file's serial #.

WorkFlow and some other places (like ask variables) in LawBase allow built in functions to run as part of a script. All functions are replaced by the result of the function. All variables are simply replaced by their value at the time of execution. All functions require commas between each parameter. This is a list of the functions and variables and their definitions.

Description: 

This function performs date arithmetic by adding or subtracting a specified number of days, business days, weeks, months, or years to a given date. 

Syntax: 

datemath(date, period, number) 

Parameters: 

• date: The starting date in 'MM-DD-YYYY' or 'MM-DD-YY' format. 

• period: A single character indicating the type of period to be added or subtracted. Can be 'd' (days), 'w' (weeks), 'b' (business days), 'm' (months) or 'y' (years).  

• number: The number of periods to add or subtract. Positive values add to the date, while negative values subtract from it. 

Replacement Value: 

Returns a string representing the new date after the arithmetic operation, in 'MM-DD-YYYY' format. 

Examples: 

• Add 5 days to 01-01-2023: 'datemath(01-01-2023, d, 5)' returns "01-06-2023". 

• Subtract 2 months from 06-15-2023: 'datemath(06-15-2023, m, -2)' returns "04-15-2023". 

Description: 

This function converts any provided text to upper case letters. 

Syntax: 

Uppercase(text) 

Parameters: 

• text: The text to convert to upper case.  

Replacement Value: 

Returns the provided text in all uppercase letters. 

Examples: 

• Convert 'this text' to upper case: 'uppercase(this text)' returns "THIS TEXT".

Description: 

This function converts any provided text to lower case letters. 

Syntax: 

LowerCase(text) 

Parameters: 

• text: The text to convert to lower case. 

Replacement Value: 

Returns the provided text in all lowercase letters. 

Examples: 

• Convert 'This Text' to lowercase: 'lowercase(This Text)' returns "this text". 

Description: 

This function converts any provided text to Title Case letters.  This means capitalize the first letter of any non-trivial words. 

Syntax: 

TitleCase(text) 

Parameters: 

• text: The text to convert to title case. 

Replacement Value: 

Returns the provided text in Title Case format. 

Examples: 

• Convert 'homer jay simpson' to title case: 'titlecase(homer jay simpson)' returns "Homer Jay Simpson". 

Description: 

This function returns the substring of a longer string. 

Syntax: 

Substr(text,startindex,length) 

Parameters: 

• text: The text to get the substring from. 

• startindex: The position to start the index from (first character is position 1) 

• length: How many characters to get for the substring 

Replacement Value: 

Returns the substring with the requested startindex and length. 

Examples: 

• Get the first 3 characters of 'Hello there': 'substr(Hello there,1,3)' returns "Hel". 

• Get the 5 characters at position 7 of 'Hello there': 'substr(Hello there,7,5)' returns "there". 

Error Handling: 

If 'text' is blank or 'startindex' > 'text' length or 'startindex' + 'length' > 'text' length returns "INVALID SUBSTRING" 

Description: 

This function returns the length of a string.  

Syntax: 

strlen(text) 

Parameters: 

• text: The text to find the length of. 

Replacement Value: 

Returns the length of the supplied string. 

Examples: 

• Get the length of 'Hello there': 'strlen(Hello there)' returns "11". 

• Get the length of a blank string: 'strlen()' returns "0". 

Description: 

This function takes a string which may be in RTF (Rich Text Format) and returns its plain text version. If the provided string is not in RTF format, it returns the provided text as is. 

Syntax: 

plaintext(text) 

Parameters: 

• text: The text to turn into plain text. 

Replacement Value: 

Returns the plain text version of the supplied string. 

Examples: 

• Get the plain text of '{\rtf1\ansi\deff0\nouicompat{\fonttbl{\f0\fnil\fcharset0 Calibri;}}{\*\generator Riched20 10.0.19041}\viewkind4\uc1 \pard\sa200\sl276\slmult1\f0\fs22\lang9 This is plain\par}': 'plaintext({\rtf1\ansi\deff0\nouicompat{\fonttbl{\f0\fnil\fcharset0 Calibri;}}{\*\generator Riched20 10.0.19041}\viewkind4\uc1 \pard\sa200\sl276\slmult1\f0\fs22\lang9 This is plain\par})' returns "This is plain". 

• Get the plain text of 'This is not rtf': 'plaintext(This is not rtf)' returns "This is not rtf". 

LawBase WorkFlow variables and functions can be used singly or together or nested anywhere in a WorkFlow step where variables are allowed: 

Examples:  

• Subtract <<var.num>> (a previously set integer value) months from <<doi>> (a date of injury date on the current screen):  

• DateMath(<<doi>>,m,<<var.num>>) 

• Find out the text length of an RTF field named <<memo>>: 

• Strlen(plaintext(<<memo>>)) 

You can create JSON string packages using VAR workflow component. We currently support 2 formats: Individual Item(s) and Item Lists.  

To create a single item, create a var named JSONxyx 

Where “JSON” is the first part of the variable name (case does not matter). The “xyz” is any user-defined descriptor used to help differentiate between JSON variables. 

In the body of that VAR, declare the type of JSON component you want. In this example, we are creating an individual single name-value pair item: 

item^name=favio  

To create a list of name-value pairs, use the following format: 

list{listname=address^itemlist=add1=1010 somewhere street^add2=apt. b2^city=somewhere^state=NC^zip=28101}list{listname=address^add1=2020 nowhere street^add2=suite 300^city=denver^state=CO^zip=80222} 

Once a Json variable is used, you can use it wherever you need in standard var format like: 

<<var.JSONxyz>>.  If your provided information contains single quotes (') you can optionally elect to escape them in the output so that id doesn't cause problems in SQL or elsewhere.  To optionally do this, simply add ".escape" to the end of the usage.  <<var.JSONxyz.escape>> 

NOTE: LawBase will automatically ADD new items to the collection based on the name of the VAR. 

For example, if there are 2 VAR components both named, JASONxyz; the first one with the individual item and the 2^nd one with the list, LawBase will add the results to a single JSON string in the correct format. 

Sample Output: 

{ 

  "name": "favio", 

  "address": [ 

    { 

      "itemlist": "add1=1010 somewhere street", 

      "add2": "apt. b2", 

      "city": "somewhere", 

      "state": "NC", 

      "zip": "28101" 

    }, 

    { 

      "add1": "2020 nowhere street", 

      "add2": "suite 300", 

      "city": "denver", 

      "state": "CO", 

      "zip": "80222" 

    } 

  ] 

} 

If you ever need to clear a JSON variable, use a VAR WorkFlow step, and set the value of a JSONname var to "clear" (without the double quotes).   

In WorkFlow variables, you can use "Input=" to ask the user for information.  If you want to ask for multiple items of information you can combine {ask} variables.  Here's an example: 

input={ask=calc^text=input your number^IsCurrency=true^Default=0.0^AskName=var1} {ask=Memo^Text=Enter some long text^AskName=var2} 

{ask=picklist^PickListName=sql(select login from user_profile where activeuser = 'Y' order by login)^Text=Who entered the notes?^default=%} 

{ask=text^text=Straight Text^Default=hi there^AskName=var4} 

This asks four questions.  The AskName=var# tells the resulting values of each ask which variable to name to place the result in.  So in this instance, the first "calc" ask will place the input results in <<var.var1>> and so on.   If any AskName=var# is omitted, the result will be automatically named LB_ASK# where # is replaced by which sequential place the {ask} was in. 

The DateMath function can be used to calculate the first and last day of selected periods. 

Using a WorkFlow executable step, you can tell LawBase url protocol (LBURL) to perform any of it's available actions.  To do this set the Executable Program of the Exec step to LBURL.exe (Note: This may require a full path name to LBURL.exe to work depending on your system setup).  In the Parameters section, place the lawbase: url definition for what you wish to do. 

Examples for the Parameters: 

Take the user to a specific SmartFolder (example SmartFolder with a serial of 100064) 

lawbase:SmartFolder=100064 

Launch the file with a serial # of 123. 

lawbase:caseserial=123