I created a means to mimic a fuzzy lookup and store the results in a data table column using a lambda function with a regular expression. However, I’ve run into an issue with the results when there is no match found.
Staying close to the overall functionality in this demonstration code, how should I script GetMyStuff
such that CriticalItems.Count -eq 0
when there are no matches?
If there are posts that I should review, please point me to those as my search of forum topics was inconclusive.
Background
The variable matchStuff
stores a lambda script block to match key terms against members of an array (props
). This is passed into the named function GetMyStuff
which creates an ArrayList object.
The problem appears in GetMyStuff
when no matches are made. I’ve attempted different approaches that would ignore the Add() method when zero matches are produced. However, the count on CriticalItems
is never less than 1 (the appropriate value when Add($null)
). There may be something bound up in &$Expression $item
or inherent with the column’s datatype [Object]
that in my mind works like T-SQL’s sqlvariant
type.
Unexpected: the script block returns matched (or zero matched) items, and not
$true|$false
. I adjusted my original code from a flag logic to compensate for this, which I came to prefer. #auditing tool
Demo Code
using namespace System.Collections;
using namespace System.Data;
$Bc = [Datatable]::new('Product','Production')
[void]$Bc.Columns.Add('Color',[string]);
[void]$Bc.Columns.Add('DaysToManufacture',[string]);
[void]$Bc.Columns.Add('ListPrice',[decimal]);
[void]$Bc.Columns.Add('Name',[string]);
[void]$Bc.Columns.Add('ProductID',[int]);
[void]$Bc.Columns.Add('ProductNumber',[string]);
[void]$Bc.Columns.Add('ReorderPoint',[int]);
[void]$Bc.Columns.Add('SellEndDate',[datetime]);
[void]$Bc.Columns.Add('SellStartDate',[datetime]);
[void]$Bc.Columns.Add('Style',[string]);
[void]$Bc.Columns.Add('CriticalItems',[Object]);
$B = Import-Csv A:\resultsB.csv -Header Color,DaysToManufacture,ListPrice,Name,ProductID,ProductNumber,ReorderPoint,SellEndDate,SellStartDate,Style
function SetDateTime($d1)
{
switch ($d1)
{
{$_ -eq 'NULL'}{'1900-01-01'; break}
{[string]::IsNullOrEmpty($_)}{'1900-01-01'; break}
default {$d1}
}
}
$matchStuff = {param($x) $x -match "(Black|BK|\bGrip Tape\b|adjust)"}
function GetMyStuff
{
Param(
[Scriptblock] $Expression,
[Array] $item
)
$results = [ArrayList]::new()
&$Expression $item | ForEach-Object{
if( !([string]::IsNullOrEmpty($_)))
{
[void]$results.Add($_);
}
}
$results;
}
$Error.Clear();
try
{
foreach($Bn in $B)
{
$props = @($Bn.Color,$Bn.Name,$Bn.ProductNumber);
$row = $Bc.NewRow();
$row.Color = $Bn.Color;
$row.DaysToManufacture = $Bn.DaysToManufacture;
$row.ListPrice = $Bn.ListPrice;
$row.Name = $Bn.Name;
$row.ProductID = $Bn.ProductID;
$row.ProductNumber = $Bn.ProductNumber;
$row.ReorderPoint = $Bn.ReorderPoint;
$row.SellEndDate = SetDateTime($Bn.SellEndDate);
$row.SellStartDate = SetDateTime($Bn.SellStartDate);
$row.Style = $Bn.Style;
$row.CriticalItems = GetMyStuff -Expression $matchStuff -item $props;
$Bc.Rows.Add($row);
}
}
catch
{
$Error.InvocationInfo.ScriptLineNumber;
$Error.InvocationInfo.OffsetInLine;
$Error.Exception;
$Error.InvocationInfo.PositionMessage;
}
cls;
$Bc.Rows.Where({$_.CriticalItems.Count -eq 1}) #produces many rows
$Bc.Rows[$Bc.Rows.Count-1];
Results
Sample of CriticalItems.Count -eq 1. Empty and Single Match. Note - no {} appear.
Sample of CriticalItems storing multiple matches based on the regex key terms.
Many thanks in advance!