Using Replace in Custom Field

I'm trying to set up a custom field that calculates mg/tablet but will also show "NA" or "Not detected" in the report. How would I set this up? Is this a separate custom field from my mg/tablet calculation? Thanks!!

Answers

  • Could you tack on a Bool formula at the end of your custom field used to calculate mg/tablet? For example: (Amount/CConst1)+EQ(Peak Type,"Missing")*-1*60002. That way, any peak that's missing and not detected will return the Not Detected in report. Make sure to have All or Nothing and Missing Peak options ticked for this to work. 

  • Thanks for your response! I added this to the end of my calculation but "Not Detected" is not showing in the report.
  • How did you calculate the mg/tablet custom field, whats the formula?
  • It's -  ((Amount*CConst1*CConst2*MFactor*100)/Number_Tabs)+EQ(Peak Type,"Missing")*-1*60002
  • A few things to consider...

    Pare down each side of the equation and test each individually.

    Make sure any dependent variables are calculated before this equation is evaluated.

    If any of the variables in this equation, such as MFactor, are calculated, they also need to have All or Nothing enabled. I usually encounter this only with boolean tables.

    REPLACE may be the best option in this case as it will only replace the contents of an empty field. Though, I have never implemented in the past and cannot verify that limitation.
  • All or Nothing needs to be activated when you use special values like -60002. Maybe try:

    EQ(Peak Type,"Found")*CF+NEQ(Peak Type,"Found")*-1*60002 where CF is the name of your custom field above.

    Another but longer way around this is to create two custom fields. Call one custom field LOQ (Peak, Real, Calculated and the formula is just CConst1). Enter into CConst1 of your processing method the lowest acceptable value for mg/tablet or your LOQ. Then create a second peak, real, calculated CF called Reported Assay or something like that and code it: GTE(Assay,LOQ)*Assay+LT(Assay,LOQ)*-1*60002. Tick all or nothing on this because of the -60002. Also tick missing peak.

  • you also need to ensure the custom field is set to run on missing peaks as well as found peaks. There is a check box for Missing Peak in the custom field creation screen where you define sample and peak types.