Home : VINFO
Q11225 - INFO: VINFO

VINFO returns variable information such as Name, Units, etc for the specifed variable.

SYNTAX:

VINFO(VarNum, Field, optional Offset, optional Decimal Places) 

VarNum: The variable number to search for.  Can be a number (i.e. 12), a cell reference (i.e. C2) to a cell that contains a number, a quoted string that is a Variable WHERE Clause, or a cell reference to a cell containing a Variable WHERE Clause

Field: Which field to retrieve.  Valid settings include:

"VarNum"
"Name"
"VarDisplay" - Returns the default variable display based on the Facility's Default Variable Display Setting.
"VarDisplayxx" - Returns the last xx characters of the default variable display based on the Facility's Default Variable Display Setting.   Example VINFO(1031,"VarDisplay30") returns "...\AerTank 1 Dissolved Oxygen",VINFO(1,"VarDisplay30") returns "Influent Flow" (the whole VarDisplay since it was less than 30 characters.
"VarDispxx" - Returns the last xx characters of the default variable display based on the Facility's Default Variable Display Setting. Same as VarDisplayxx besides it replaces the "\" with "\ " (\<space>), usefull when you have long location paths and are placing the text in a cell that has Wrap Text Enabled.  
"Name.Units" - Returns "Variable Name {units}"
"Location.VarName" - Returns Variable Location.Variable Name
"Area.Location.VarName"' - Returns Area.Location.Variable Name
"Location:VarName" - Returns Location Path<space>Variable Name
"ShortName"
"ShortName.Units" - Returns the ShortName (Heading) {Units}
"VarType" - Returns the Variables internal Vartype value (i.e. P for Daily Parameter, N for Hourly Calc...)
"Frequency" - Returns the variable's frequency - "Day", "4 Hours","Hour", "15 Minutes"...
"Type" - Returns the variable's type - "Parameter", "Calculated","Text Parameter"
"DataTable" - Returns the table that stores the variables data (i.e. datatbl, dataddh, etc...)
"ScadaTag" - The Interface ScadaTag.
"Statistic" - The SCADA Interface Statistic (i.e. Average, Max,...)
"LIMS_LOC" - The LIMS Interface Location.
"LIMS_TEST" - The LIMS Interface Test or Analyte
"XREF" - The interface cross reference.  If SCADA returns ScadaTag (Statistic), ex "FIT_RAW_WATER (AVERAGE)".  If LIMS returns LIMS_LOC::LIMS_TEST, ex "RAW::PH"
"StoretCode" - Returns the StoretCode for the variable (i.e. 50050, 00310)
"StoretCodeDesc" - Returns the description for the variable's storet code (i.e. "FLOW, IN CONDUIT OR THRU A TREATMENT PLANT MGD")
"StoretCode-Desc" - Returns the storetcode a dash and then the description (i.e. "50050-FLOW, IN CONDUIT OR THRU A TREATMENT PLANT MGD")
"AnalyteCode" - Returns the AnalyteCode for the variable (i.e. 0100, 3100)
"AnalyteCodeDesc" - Returns the description for the variable's AnalyteCode (i.e. "COLIFORM (TCR)")
"AnalyteCode-Desc" - Returns the AnalyteCode a dash and then the description (i.e. "3100-COLIFORM (TCR)")
"AllowSymbol"
"DecPlaces"
"SigFigs"
"EntryMin"
"EntryMax"
"Entry Range" - Returns EntryMin and Max with a dash between.  IE 0-14, if only an Entry Max returns <=14, only Entry Min returns >=0.
"Daily Min"
"Daily Max"
"Weekly Min"
"Weekly Max"
"Monthly Min"
"Monthly Max"
"Quarterly Min"
"Quarterly Max"
"Annual Min"
"Annual Max"
"Limit.????.Description" - ???? is the limit name entered in Variable Setup, Limits.
"Limit.????.Compare"
"Limit.????.StartDate"
"Limit.????.EndDate"
"Limit.????.Grouping"
"Limit.????.STATISTIC"
"Limit.????.Limit" - Limit Value for ???? Limit.
"QC STARTDATE"
"QC UCL"
"QC UWL"
"QC MEAN"
"QC LCL"
"QC LWL"
"VGROUP" - Returns the Variable Group name
"VGROUPPATH" - Returns the full path of the Variable Group
"USERVGROUPACCESS" - Returns the logged in user's Variable Group access level (Edit, View, No Access)
"Location"
"Location.fieldname" - fieldname is any fieldname from the Location or Area table.  Examples "LOCATION.SITEADDRESS","LOCATION.PERMITNUMBER","LOCATION.AREANAME","LOCATION.PWSID" etc...
"LOCATION.LEVELx" - The Location at Level x (1-9) of the Location tree.
"LOCATION.LVLx_ID" - The ID of the location at Level x (1 - 9)
"PARENT" - The Location Name of the Parent of the variable's location.
"PARENTID" - The LocId of the parent of the variable's location.
"GRANDPARENT" - The Location Name of the Grandparent in the Location tree.  See Location Tree Example below.
"GRANDPARENTID" - The LocId of the grandparent of the variable's location.
"PATH" - The full location path seperated by \.  See Location Tree Example below.
"VARDESC_I.fieldname" - The Variable Additional Info field specified by fieldname.
"LOCATION_I.fieldname" - The Location Additional Info field specified by fieldname.

 

Offset: Optional.  Used with limit fields ( "Daily Min", "Daily Max", "Weekly Min", "Weekly Max", "Monthly Min", "Monthly Max", "Quarterly Min", "Quarterly Max", "Annual Min", "Annual Max", "QC STARTDATE,"QC UCL", "QC UWL", "QC MEAN", "QC LWL","QC LCL") which specifies which month to display the limit for.  The Offset sets the month to be retrieved relative to the specified report start date.  For example, with a report start date of 1/1/96, in Offset of 1 would define January, 2 February, etc..  If the Offset is a date string (i.e. "07/16/2004") or refers to a cell with a date in it, it sets the specific date to find the limit for.

Decimal Places: Optional.  Used with limit fields ("Daily Min", "Daily Max", "Weekly Min", "Weekly Max", "Monthly Min", "Monthly Max", "Quarterly Min", "Quarterly Max", "Annual Min", "Annual Max", "QC STARTDATE,"QC UCL", "QC UWL", "QC MEAN", "QC LWL","QC LCL", "ENTRYMIN","ENTRYMAX").  The VINFO function returns text as a default.  If you need to format the number to a certain number of decimal places it can be specified with this parameter.  You can also use the VAL function to change the cell to a number and then use Format, Cell to set decimal places. 

NOTES:

Use Locate, Variable Info to locate this formula.

EXAMPLES:

VINFO(1,"Name")  returns Influent Flow

VINFO(1,"Name.Units") returns Influent Flow {MGD}

VINFO(C4,"Units") returns mg/L (cell C4 equals 4 which refers to variable 4)

VINFO(4,"Units") returns mg/L

VINFO(4, "Daily Max") returns 30, returns the Daily Max Limit for the 1st month of the report  since offset was not specified.

VINFO(4, "Daily Max",2) returns 20, returns the Daily Max Limit for the 2nd month of the report (offset of 2)

VINFO(4,"Daily Max",B6) returns the Daily Max limit in effect for the date in B6.  If B6 is a number, uses B6 as the month offset (i.e. if B6 = 2, returns the limit on the 1st day of the 2nd month of the report. 

VINFO(4,"ENTRYMAX",1,2) - Returns the Entry Limit Maximum for variable 4 to 2 decimal places.  The Offset (3rd parameter) is ignored in this case. 

VAL(VINFO(4,"ENTRYMAX")) - Returns the Entry Limit Maximum for variable 4.  The VAL function converts the cell to a number. 

VINFO(4, "LIMIT.RAA.STARTDATE")  Returns 1/1/2000, the start date of the limit named RAA.   See Variable Edit - Limits for information on entering your limits.

VINFO(4,"LIMIT.RAA.LIMIT,"2/4/2016") - Returns the Limit value in effect for 2/4/2016 for the limit named RAA.

VINFO(4,"LOCATION_I.POPULATION_SERVED") - returns the LOCATION_I.Population_Served (Additional info field) value for the location variable 4 is assigned to.

VINFO(4,"VARDESC_I.EXTSYSTEMID") - returns the VARDESC_I.EXTSYSTEMID (Additional info field) value for variable 4.

VINFO("STORETCODE='50050' AND VARNUM>1000", "Name") - Returns "RAS FLOW".  Would execute the query "SELECT TOP 1 VARNUM FROM VARDESC WHERE STORETCODE='50050' AND VARNUM>1000 ORDER BY VARNUM" to find the varnum and return the Name for that varnum.

VINFO(B2,"Name") - Returns "F1 Filter Prod".  B3 = VARNUM > 1000 AND NAME LIKE '%Filter Prod'

The following example finds the minimum CL2 in several distribution sites and then finds the TDS value on the same date for the same site.

V9001,V9011,V9021,V9031,V9041,V9051 and V9061 are the distribution Cl2 results.  The associated TDS variable for the same site is always greater than the CL2 variable and it's name ends with TDS:

Working with the Location Tree

In the following example we have 4 levels in our location tree. 

For Variable 2001,assigned to the Flow Totals location in Level 4:

Formula Result Notes
VINFO(2001,"LOCATION") Flow Totals  
VINFO(2001,"Parent") Easton WTP Treatment Plant  
VINFO(2001,"GRANDPARENT") Treatment Plant  
VINFO(2001,"LOCATION.LEVEL3") Easton WTP Treatment Plant Level 3 
VINFO(2001,"LOCATION.LEVEL1") Main System Level 1
VINFO(2001,"PATH") Main System\Treatment Plant\Easton WTP Treatment Plant\Flow Totals   

 

 

 

 

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/11/2008 8:38 AM.
Last Modified on 2/6/2024 9:06 AM.
Last Modified by Steve Fifer.
Article has been viewed 36004 times.
Rated 6 out of 10 based on 7 votes.
Print Article
Email Article