Compound LOD and LOQ in OpenLab CDS-Chemstation

Hi Beakerboy,

I can understand your frustration regarding the Custom Field Values as I experienced exactly the same thing. Below you can find my solution which I think it works much better when you need to updates LOD and/or LOQ values. Fortunate enough that you are using C.01.09 version which supports "custom code" to read an external file.

1. Create a txt file with the name of your choice (i called it Parameters.txt and will use it throughout the steps below). Note that each column should be separated by a tab (or any other delimiters but tabs just make each column look clear). Enter your compound names and LOD, LOQ values. Save the file.

2. Copy the file to a location which can easily be assessed (usually I choose the RepStyle folder).

3. Go to Report Layour and load your template.

4. Go to Edit --> Report Properties --> Code and copy the codes below. Make sure the A, B and C are spelled exactly the same as in your calibration table.

5. Now you can extract LOD or LOQ values for all compounds by using this expression "=Val(code.LOD(compound_name))" or "=Val(code.LOQ(compound_name))

6. Perform any comparisons you need with IIF statement, for example "=iif(compound_amount < val(code.LOD(compound_amount)), "Below LOD", compound_amount)"

Hope this helps.

Peter Tran.

Hi Beakerboy,

I can understand your frustration regarding the Custom Field Values as I experienced exactly the same thing. Below you can find my solution which I think it works much better when you need to updates LOD and/or LOQ values. Fortunate enough that you are using C.01.09 version which supports "custom code" to read an external file.

1. Create a txt file with the name of your choice (i called it Parameters.txt and will use it throughout the steps below). Note that each column should be separated by a tab (or any other delimiters but tabs just make each column look clear). Enter your compound names and LOD, LOQ values. Save the file.

2. Copy the file to a location which can easily be assessed (usually I choose the RepStyle folder).

3. Go to Report Layour and load your template.

4. Go to Edit --> Report Properties --> Code and copy the codes below. Make sure the A, B and C are spelled exactly the same as in your calibration table.

5. Now you can extract LOD or LOQ values for all compounds by using this expression "=Val(code.LOD(compound_name))" or "=Val(code.LOQ(compound_name))

6. Perform any comparisons you need with IIF statement, for example "=iif(compound_amount < val(code.LOD(compound_amount)), "Below LOD", compound_amount)"

Hope this helps.

Peter Tran.

Judging just from the look of it petertran's post is the way to go.

Here is Peter's code as copy-able text:

Public Function getparameter(compound_name as string) as object
   Dim comp_name(3) as string 'Numbers of compounds
   comp_name(0) = "A"
   comp_name(1) = "B"
   comp_name(2) = "C"
   Dim i as integer
      For i = 1 to 3 'Numbers of compounds
      Select case compound_name
         Case is = comp_name(i-1)
                  getparameter = System.IO.File.ReadA11Lines("C:\Users\Pub1ic\Documents\ChemStation\RepSty1e\Parameters.txt")(i)
      End select
      Next i
End function

Function LOD(compound_name as string) as single
   LOD = choose (2, split(getparameter(compound_name), vbtab)))
End function

Function LOQ(compound_name as string) as single
   LOQ = choose (3, split(getparameter(compound_name), vbtab)))
End function

If you test it, please let me know if it works as intended.

BR

W.

petertran Has an excellent suggestion that I modified. I made everything a little more generic. My tab-delimited file is created with a row of parameter names, then rows of data like he suggested:

Name   LOD   LOQ   aroma

butyric acid   .2   .8   cheese

diacetyl   .8   .9   butter

Then I can get any value by using =Code.getParameter(Compound_Name, {parameter}), using the name of the column (LOD, LOD or aroma) in this case. The advantage of my snippet is that the VBA does not need to change if the number of compounds in the table changes, or if new parameters are added, or if the table is rearranged. The only requirement is the compound name in in the first column.

' Function: getParameter
'
' Find a value for a compound in a lookup table
'
' Parameters:
'    compound_name - name of the compound
'    parameter_name - name of the parameter
'
' Returns: the value of the parameter for this compound
Public Function getParameter(compound_name as string, parameter_name as string) as String
  Dim parameter_number as integer
  parameter_number = 0

  ' Get the data as an array of arrays
  Dim lookup_table as Object
  lookup_table = buildLookupTable()

  Dim title_row as Object
  title_row = lookup_table(0)

  Dim i as integer
  i = 0

  ' Scan the top row for which column has the requested data
  Dim table_field as String  
  For Each table_field in title_row
   If  table_field = parameter_name Then parameter_number = i
   i = i +1
  Next

  ' Scan the remaining rows for the row that matches the compound name, and extract the correct column
  Dim line_row as Object
  Dim found_parameter as String
  found_parameter = ""

  For Each line_row in lookup_table
    If line_row(0) = compound_name Then found_parameter = line_row(parameter_number)
  Next
  getParameter = found_parameter
End function

' Function: buildLookupTable
'
'   Create an array of arrays of the data in a tab delimited file
'
' Return: array of arrays
Function buildLookupTable() as Object
  Dim text_lines as Object
  text_lines = System.IO.File.ReadAllLines("C:\Users\Public\Documents\ChemStation\RepStyle\compound_parameters.txt")
  Dim rows as integer
  rows = UBound(text_lines)
  Dim lookup_table as object
  Redim lookup_table (rows)
  Dim i as integer
  i = 0
  Dim text_line as object
  For Each text_line in text_lines
    lookup_table(i) = split(text_line, vbtab)
    i = i + 1
  Next
 buildLookupTable = lookup_table
End Function