Compound LOD and LOQ in OpenLab CDS-Chemstation

Hello,

There are several ways to do this including complex solution like custom extension and external constants files but the most accessibly way is to use the compound name. One possible other solution if values are the same for all compounds and do not change that often use a report parameter. I am guessing for your report each peak has its own LOD and LOQ so that is not an option. In this case, I would suggest naming your peaks as peakname_LOD_LOQ, this might look like Methanol_0.525_1.101. Now when you need the compound name for an expression you would use Choose(1,Split(Compound_Name,"_")). When you need the LOD or LOQ for an expression, like the IIF comparison for reporting compound amount, use Val(Choose(2,Split(Compound_Name,"_"))) or Val(Choose(3,Split(Compound_Name,"_"))). 

Marty Adams

That’s a decent idea...seems kind of like a cheap hack, but if that’s what it takes. I’d probably use the pipe as my delimiter, but it would work the same. I didn’t know about the Val() function, so thanks for that. The Compound Custom Fields still seems more “right” if it weren’t for poor interface to populate values.

Is there an updated macro programming guide for OpenLab CDS Chemstation somewhere? The latest one I could find online has no mention of Compound Custom Fields.

Compound Custom Fields will work and likely would the correct solution, but you have already found the workflow issue that it causes. We have gotten around this with some customers by creating a template sequence for the method with all the values filled in including the compound custom fields for more sample than they will likely ever do in one run. Then to run the system they open that sequence, update any necessary fields, and the cut out the extra lines. The new update custom field values entry in the run time checklist was placed there to allow data analysis to write a value into a custom field for reporting. An example of this is the compound mulitplier which is not available in IR for reporting. You could create a custom field for that value in the method, write a macro to copy that value during DA into the custom field, and then add that macro to your method. Now in IR you could report compound multiplier using that custom field.

Marty Adams

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