REQUEST A DEMO

Using the fcSDK in PowerShell

Years ago, if I wanted to “script” Clarify [formerly linked to www.myclarify.com which no longer exists], I would use UNIX shell scripts, including UNIX mini languages such as sed and awk, that would create dat files that could be imported with dataex. When ClearBasic was introduced, we also got cbbatch, which was a command line interpreter for ClearBasic, so we could script Clarify using CB. When we (First Choice Software) introduced FCFL (First Choice Foundation Library), which was a set of COM objects, we could script using VBScript or JavaScript making COM calls. Same story for the fcSDK. Although the fcSDK is all native .NET, it also exposed a COM interface, so we could still do scripting as we did with FCFL, but we didn’t have all of the .NET capabilities of fcSDK.

But now, with the availability of PowerShell, we can script using the fcSDK natively in .NET.

For those not familiar with PowerShell, wikipedia says:

Windows PowerShell, previously Microsoft Shell or MSH (codenamed Monad) is an extensible command line interface (CLI) shell and scripting language product developed by Microsoft. The product is based on object-oriented programming and version 2.0 of the Microsoft .NET Framework. It is available for Windows XP, Windows Server 2003 and Windows Vista and planned for inclusion with Windows Server “Longhorn”.

A few years ago, I had written a VB6 program that used the original FCFL to create cases for contracts that were coming up on their renewal. These renewal cases could then be worked by our contracts department.

For a while I’ve wanted to port this code away from VB6, so this was a good opportunity to get my feet wet with PowerShell and to retire my old VB6 code. So, let’s go!

The first thing we need to do is to create a ClarifyApplication object and initialize it.

[system.reflection.assembly]::LoadWithPartialName("fcsdk")  $null 
$connectionString = "Data Source=myServer;Initial Catalog=fcclient;uid=sa;pwd=myPassword"
$config = new-object -typename System.Collections.Specialized.NameValueCollection
$config.Add("fchoice.connectionstring",$connectionString);
$config.Add("fchoice.dbtype",$databaseType);
$config.Add("fchoice.disableloginfromfcapp", "false");


$ClarifyApplication = [Fchoice.Foundation.Clarify.ClarifyApplication]

if ($ClarifyApplication::IsInitialized -eq $false ){
   $ClarifyApplication::initialize($config)  $null;
}

Looking at the above code, we first load up the fcSDK assembly. We then create a name/value collection for the configuration. ClarifyApplication is a singleton, so it gets automatically created the first time it is used. Because it’s a singleton, and PowerShell basically acts as a single AppDomain, we can only initialize ClarifyApplication once (we’ll get an error if we try to initialize it again), I’ve added a check to see if its’s already initialized before trying to initialize it. This allowed me to run this script over and over again within the same shell.

Next, we create a ClarifySession. Because we don’t pass in any parameters, it will use the same ClarifyApplication user as the ClarifySession user.

$ClarifySession = $ClarifyApplication::Instance.CreateSession()

Then we actually do the meat of the work.  Don’t worry, we’ll fill in some of these details later. Hopefully, you can understand what’s happening based on the naming.

$renewedContracts = GetExpiringContracts | GetContactForContract | CreateContractRenewalCase | UpdateContractWithRenewalCaseId

What we’re actually doing here is:

  • Get the contracts that are about to expire
  • For each contract, get the contact for that contract (as we we’ll create the case for that contact)
  • For each contract, create a case for tracking its renewal
  • For each contract, update the contract with the id of the case for its renewal (this is how we know not to create another case during the next run of this script)

Each of those statements in the pipeline is a function. Let’s take a look at each function.

The first function is GetExpiringContracts, which will query for open contracts that will expire in the next 60 days, where they haven’t had a renewal case id set.

$numberOfDaysBeforeExpirationToRenew = 60
$futureExpirationDate = (get-date).AddDays($numberOfDaysBeforeExpirationToRenew)
$renewalCaseIdColumn = "x_renewal_case_id"

function GetExpiringContracts(){
   $dataSet = new-object FChoice.Foundation.Clarify.ClarifyDataSet($ClarifySession)
   $contractGeneric = $dataSet.CreateGeneric("contract")
   $contractGeneric.AppendFilter("expire_date", "Before", $futureExpirationDate)
   $contractGeneric.AppendFilter("status", "Equals", "Open")
   if ($ClarifyApplication::Instance.SchemaCache.IsValidField("contract",$renewalCaseIdColumn) -eq $true){
      $contractGeneric.AppendFilter($renewalCaseIdColumn, "Equals", "")
   }
   $contractGeneric.Query()
   $contractGeneric.Rows
}

We’ve done a query for contracts, and the output of the function will be a collection of contract data rows ($contractGeneric.Rows)

Next, we need to get the contact for the contract. This is the contact that we’ll create the case for. For our environment, we’ll just grab the first contact found for the contract. If we don’t find a contract, we’ll use our “dummy” contact.

Ed. Note: Nothing like having to satisfy system constraints (a case MUST have a contact) that don’t exist in the real world. 

$dummyFirstName="Dummy"
$dummyLastName="Contact"
$dummyPhone="0"
$dummySiteId="INT1"

function GetContactForContract(){
 foreach( $contract in $input){

   $dataSet = new-object FChoice.Foundation.Clarify.ClarifyDataSet($ClarifySession)
   $contactGeneric = $dataSet.CreateGeneric("contact")
   $contactGeneric.TraverseFromRoot($contract["objid"], "contract", "contract2contact");
   $contactRoleGeneric = $contactGeneric.Traverse("contact2contact_role");
   $contactRoleGeneric.AppendfIlter("primary_site","equals",1)
   $siteGeneric = $contactRoleGeneric.Traverse("contact_role2site");
   $contactGeneric.Query()
   $contactDataRows = $contactGeneric.Rows

   if ($contactDataRows.Count -gt 0){
       $contact = $contactDataRows[0]
       $contactRoleDataRows = $contact.RelatedRows($contactRoleGeneric)
       $contactRole = $contactRoleDataRows[0]
       $siteDataRows = $contactRole.RelatedRows($siteGeneric)
       $site = $siteDataRows[0]

       $firstName = $contact["first_name"]
       $lastName = $contact["last_name"]
       $phone = $contact["phone"]
       $siteId = $site["site_id"]
       $siteName = $site["name"]
   }else{
      $firstName = $dummyFirstName
      $lastName = $dummyLastName
      $phone = $dummyPhone
      $siteId = $dummySiteId
      $siteName = ""
   }

  add-member -in $contract noteproperty firstName $firstName
  add-member -in $contract noteproperty lastName $lastName
  add-member -in $contract noteproperty phone $phone
  add-member -in $contract noteproperty siteId $siteId
  add-member -in $contract noteproperty siteName $siteName

  $contract
 }
}

Again, we’re doing a query, but we’re also doing traversal queries, which are very common with the fcSDK.

I love this: $contactGeneric.TraverseFromRoot($contract[“objid”], “contract”, “contract2contact”);
You can’t do that in fcSDK using COM. You would first have to create the contactGeneric, then set it up as a traversal. This is just another example of the niceties of using the fcSDK in a native .NET mode.

Notice that we’re taking advantage of one of the features of PowerShell – the ability to dynamically add properties and methods to existing objects. The contract data row doesn’t have a property to hold a firstName, lastName, etc. So, we just add these properties on the fly using add-member. The output of the function ($contract) simply passes each contract along to the next step in the pipeline.

The next step is to create a case for each of these contracts, and dispatch the case to a queue.

$caseType="Support Renewal"
$queue="Support Renewal"
$baselineCaseTitle="Contract Renewal for Contract: "

function CreateContractRenewalCase(){
  foreach( $contract in $input){
    $caseTitle= $baselineCaseTitle + $contract["id"]
    if ($contract.siteName.length -gt 0){
       $caseTitle+= "; Site Name = " + $contract.siteName
    }
    if ($caseTitle.length -gt 79){
       $caseTitle = $caseTitle.substring(0,80);
    }

    $genTimeBombs = True
    $phoneLogNotes = "Contract Expire Date = " + $contract["expire_date"]

    $supportToolkit= new-object FChoice.Toolkits.Clarify.Support.SupportToolkit( $ClarifySession )
    $caseSetup = new-object FChoice.Toolkits.Clarify.Support.CreateCaseSetup($contract.siteId, $contract.firstName,
$contract.lastName, $contract.phone)
    $caseSetup.Title = $caseTitle
    $caseSetup.PhoneLogNotes = $phoneLogNotes
    $caseSetup.Queue = $queue
    $caseSetup.ContractIDNum = $contract["id"]
    $caseResult = $supportToolkit.CreateCase($caseSetup);

    add-member -in $contract noteproperty renewalCaseId $caseResult.IDNum
    $contract
  }
}

 

The interesting code in the above function is the use of the Support Toolkit for creating the case. Again, this is a much cleaner, simpler way to create a case as opposed to using the COM style interfaces.

Again, we dynamically add a property to the contract object, and pass the contract along to the next step in the pipeline.

The final step in the pipeline is to update the contract with the id of the renewal case. This not only allows us to easily see the renewal case when viewing the contract in fcClient, but if this renewalCaseId is present, then the next time this script runs, it will not process this contract again.

function UpdateContractWithRenewalCaseId(){
  foreach( $contract in $input){
    if ($ClarifyApplication::Instance.SchemaCache.IsValidField("contract",$renewalCaseIdColumn) -eq $true){
      $contract[$renewalCaseIdColumn] = $contract.renewalCaseId
      $contract.Update()
    }
    $contract
  }
}

I’m also checking to validate that this custom field exists before trying to set it. This was due to differences in my different test environments.

We’ve now done the bulk of the work. The final step was to close/log out of the Clarify Session:

$ClarifySession.CloseSession();

And that’s it. We’re done.

I save all this code in a script file named CreateCasesForContractsAboutToExpire.ps1. To invoke it from PowerShell, simply type ./CreateCasesForContractsAboutToExpire.ps1

I created a Windows Scheduled Task that runs every Monday that calls powershell.exe and invokes my script.

I have another to-do item of having the script email me with the results, but I’ll tackle that later.

So, in summary, we’ve seen how to do the following in PowerShell:

  • Create a ClarifyApplication object and log into the database
  • Create a ClarifySession object
  • Use ClarifyGeneric objects to perform simple queries
  • Use ClarifyGeneric objects to perform traversal queries
  • Use the high level APIs in the API Toolkits
  • Use ClarifyGeneric objects to Update data

And don’t forget, the fcSDK is Clarify-version and database version independent, so it will even work for those of you on older versions of Clarify.

I’m still very much a PowerShell novice, and there may be better ways to work with the pipeline, and some better error handling could be put in place, but hopefully this should encourage you to take advantage of the fcSDK in the new PowerShell environment. I know my future scripting tasks will be in PowerShell.

Have fun!