Axon Queries and How to

 

Examples and How To write Axon queries

Name

Axon query

Description

Name

Axon query

Description

Find Unused Bacnet Connectors

readAll(bacnetConn).findAll(x=> readCount(point and bacnetConnRef==x->id)==0)

Finds all bacnet connectors that are not being used

Contains Search

Examples:

readAll(point).findAll point => point.dis.contains("Temp")

readAll(equip).findAll equip => equip.dis.contains("Vav")

looks for records that 'contain' the text specified in the 'contains' query

Take point descriptions from the Connector and copy it onto a property tag on the point in FIN Stack.

readAll(point and bacnetConnRef).each x=> x.diff({description:bacnetReadObjectProperty(x->bacnetConnRef,x->id,"description").colToList("value").toStr}).commit()

This query takes every point coming from a bacnet connector and adds a string property tag called description and fills in the value of this tag with the description inside the BACnet device.

priority for points

read(writable).pointWriteArray

This will show you what level, and who wrote to a certain priority for points.

create records via commit

commit(diff(null, {dis:"actions testing",actions:"",finActions}, {add}))

This will allow the user to add a new record to the dataBase with a dis tag, action string, and a finActions

marker

add / edit tags on records

readAll(jason).toRecList.map(r => diff(r, {numberTest:88})).commit

This will let you find a list of records that match the filter (Jason), and it will add the numberTest:88

tag to them. Or it will edit the tag if it already exists.

Commit Records from Trio or just a grid

ioReadTrio(`io/finCommunity.trio`).toRecList.map(r => diff(null, r,{add})).commit



remove tags

readAll(jason).toRecList.map(r => diff(r, {numberTest:99,stringTest:"test Me",-rochelle})).commit

-rochelle will find and remove all the rochelle tags, that have a jason marker

delete/remove recrods

readAll(jason).toRecList.map(r => diff(r, null,{remove})).commit

Finds all records that have the jason marker, and will remove them out of the dataBase.

keep Cols

funcs().keepCols(["name","lib","id"])

This takes a grid, in this example I just used the funcs function, and then only kept columns name,lib, and ID

readAll then Fold

readAll(point and unit=="%").foldCol("curVal",avg)

This finds all points, that are %, and it gives you the average for the curVal tag. Using the foldCol function.

foldCols

readAll(point and unit=="%").keepCols(["id","curVal","hisSize"]).foldCols(["curVal","hisSize"],"newCol",sum)

This finds all point and unit, and then keeps just the id, curVal, and hisSize columns. Then it uses a foldCols function, and

sums the curVal, and hisSize columns.

uriPath

`http://host:81/a/b/file.txt`.uriPath

Takes a URI, and breaks out the path into a number of rows

get date info

now().format("MMM")

This query gets the current time, and then formats it so it's Month.

Time Formats

format(today()-9mo, "MMMM") This will show you what 9 months ago today was, and show you the Month only

YY Two digit year 07 YYYY Four digit year 2007 M One/two digit month 6, 11 MM Two digit month 06, 11 MMM Three letter abbr month Jun, Nov MMMM Full month June, November D One/two digit day 5, 28 DD Two digit day 05, 28 DDD Day with suffix 1st, 2nd, 3rd, 24th WWW Three letter abbr weekday Tue WWWW Full weekday Tuesday

find all discover sessions, or find the devices that were previsouly discovered

finDiscoveryRef==@1da849e8-612c4a16 finDiscovery



start from a gird

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid

Take a grid, and start working with it

read a string and remove 1st 5 lines, then turn it back into a grid

ioReadStr(`io/BV506600.txt`).split("\n").get(4..-1).concat("\n").ioReadCsv()



Size

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid.size()

.size() from the grid will tell you how many rows you have

First row

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid.first()

Gives you the first row

first and second row

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid[0..1]

[0..1] will return the first 2 rows

foldCol

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid.foldCol("area",sum)

This takes the area column and folds it using sum

sort

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid.sort("area")

This sorts it by the area column. sortr does a reverse sort

Rename Col

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid.renameCol("area","sqFT")

This will rename the column to a different name

Find a tag on a record ref

readAll(equip and siteRef->dis=="Short Pump")

Finds all equipments and then looks for all of their siteRefs that the tag name dis = Short Pump.

The -> is what allows you to goto that siteRefs record and find a tag that matches the dis name

math on columns

readAll(site).map(r => r.set("delta",r["area"]*r["storeNum"])).keepCols(["id","area","storeNum","delta"])

Find sites, and do a map, then create a new column called delta, and multiple the area by the storeNum. Then

we just keep 4 columns.

find all tags on a record

readAllTagNames(equip)

Find all the tags on a certain record

Nav Move

navMove("equip",@18c1160e-a1bfafe2,@18b79056-84738485)

This uses a equip tree to move on record to a different record. IE... To take a point from one equipment, and move it to

another equipment. navMove will look at navMeta, and it will make sure that the correct refs are on each record.

contains

readAll(site).findAll site => site.dis.contains("Car")

This one finds where the dis name contains "Car"

sqlConn

read(sqlConn).sqlQuery("select * from ae_s_bld_c where fac_id='PHX'")

sql Query that finds all of Assetworks buildings, where it == PHX

goto and find tags

read(point)->equipRef->siteRef

This read something, and find tags, or find refs, and then find tags.

read point

read(point)->equipRef->siteRef->area

find a point, then goto the equip, then goto the site, and then return the area tag

try and map

readAll(point).map(row => {area: try row->equipRef->siteRef->area catch 0})

What this function does is look and find every point, then use map to go through every row, and goto the equip, then the

site, and return the area. The reason I put try there is because it was throwing an error, because one of the rows didn't have

an equipRef, so now if it gets an error, it just returns 0. The reason we put area was only because we wanted to name the column in

the grid.

find crap, and use map

readAll(point and equipRef).map(row => {jason: try row->equipRef->siteRef->area catch 0, id: row-> id, equipName: try row->equipRef->dis catch 0})



Multiple valuations and

comparing each other

readAll(point and kind =="Number" and kw).map(row => {site: try row -> equipRef->siteRef->dis catch 0, currentValue: try row -> curVal catch 0}).foldCol("currentValue",sum)

readAll(point and kind =="Number" and kw).map(row => {site: try row -> equipRef->siteRef->area catch 0}).foldCol("site",sum)





I fold the first axon query, and then I divide the 2nd axon query by itself. This is just cool :}

Understanding maps is very important

haystackCall

http://localhost/haystack/read?filter=axType == "kitControl:Ramp"



haystackCall Read

read(haystackConn and dis == "nh_large").haystackReadAll(axType == "kitControl:Ramp")



more

read(haystackConn and dis == "nh_large").haystackReadAll(axSlotPath == "slot:/Drivers/NiagaraNetwork/AHU1/points/SAT")





read(haystackConn and dis == "nh_large").haystackReadAll(his)

finds histories that exist in nHaystack



read(haystackConn and dis == "nh_large").haystackHisRead(@c.c2xvdDovRHJpdmVycy9OaWFnYXJhTmV0d29yay9BSFUxL3BvaW50cy9TQVQ~, today)

This let's you read a history from a haystack server, in this case nHaystack, and that do normal history stuff.

nav Uri

navReadByUri(`equip:/Jason/tom`)

This is the way you can query a point or equip by using the navID... It will check the dis:"" or the navName:"" and return the result. :}

Sync all Haystack histories

readAll(haystackHis).haystackSyncHis(null)

This will find all haystack histories, and it will sync them. IE... Pull the history out of the haystack client, and store it in FIN Stack

finNavChildren

finNavChildren("equip")

This will let you walk the tree

finNavChildren fo down

finNavChildren("equip",180082a9-a72ca020)

This looks for the equp tree, and you pass in an ID

finNavLevels

finNavLevels("equip",@180082a9-a72ca020)

This shows you where you are at in the tree,

NIagara Haystack query

read(niagara).haystackReadAll(siteUri=="site:/UCLA/")

This is how to read n Haystack SiteUri

Haysatck url query

http://174.129.123.55:81/haystack/read?filter=site

This is how to query direct from the browser

List and function in a list

[1,2,3,4,5,6,2+4,today()]

This is a list with functions in them



[1,2,3,4,5,6,2+4,today()].get(2..5)





[1,2,3,4,5,6,2+4,today()].get(-1)



Dict

{dis:"jason",person: marker(),bday:today()}





{dis:"jason",person: marker(),bday:today(),-tagToBeRemoved}



Grid

[{dis:"jason",person: marker(),bday:today()},{dis:"jason",person: marker(),bday:today(),jason, frank, feorge}].toGrid



add meta data

[{dis:"jason",person: marker(),bday:today()},{dis:"jason",person: marker(),bday:today(),jason, frank, feorge}].toGrid.addMeta({title:"the title",jason,test: today()})

Run this and you will see how to add meta data to a grid, and how to run a function in a grid

get Meta data

read(point).hisRead(lastWeek).hisRollupAuto.meta->hisRollupDis

This will get you data from the meta data

convert units

40°C.to("°F")

.to converts things. ONly like dimensions. This exists in the units text



40m.to("ft")



add unit

10.as("ft") or you can use this too 10m.as("ft")

add a unit or switch a unit with no conversion



today()-4day

Time Units- s,min,h,day,wk,mo,yr



today()- 2013-01-01

I can take today and - it from something else. Very cool

read csv file

ioReadCsv(`io/import-points.csv`)

Read csv file in my dataBase



"ABCDEFGHIJKLMNOP".get(5..-2)





{dis:"BOB",day:1980-12-12,person}.get("dis")



trap

{dis:"BOB",day:1980-12-12,person}.trap("dis") {dis:"BOB",day:1980-12-12,person}.->dis

get is [] and trap is -> ..... Get

no exception

read(siteMeter and kw and notFound, false)

false makes it not return an error



do x: true; if (x) "x is true" else "x is false"; end



error message

throw "somthing really bad just happened, and I sent it to the error"

Here is how you can throw an error message

map

[2,3,4,5,6,10,12].map( val => val * 2)





[2,3,4,5,6,10,12].findAll(val => val > 8)



find the spread of something

[2,3,4,5,6,10,12].fold(spread)

This is great to see how much something hunted during a certain time. Great way

find

readAll(site).map row => {dis: row->dis, areaTest: row->area}





readAll(site).findAll row => row ->area > 10_000



find points in every row

readAll(point).findAll row => row->navName.contains("Temp")

find point names by looping through a lot of crap

add Haystack database

finCreateHaystackDb([@1902cc8c-adde74db], "sep:/" , true)

@... the id of the haystack connector and the true parameter is adding history to the points that don;t have it

you can use it without the true part

the "sep:/" is needed after Jarmy new changes - this is the default for Niagara and otherwise it can be: "/equip"

default excluded tags when adding a database

finHaystackExcludedTags()

you will get all the default excluded tags

you just need to add a new record in folio with finHaystackExcludedTags and after it the other markers that you want to exclude(so that you add some more to the excluded list)

the default excluded ones are: "axSlotPath",axHistoryRef,siteUri,id" 

contains query

connExts().findAll val => val->ext.contains("haystack")

Take a grid, and findAll that contains haystack

Debugging a connector

logSetLevel("haystack", "debug")

This puts the debugger on in skyspark. You then can look at the data in the console.

Read Tag Values

readAllTagVals(point and temp and air,"equipRef")

This one will show you all the values of a tag, based on a filter. It will show all the unique values. It's pretty helpful actually

Lint for a record

lint().find r => r->targetRef == 180082a9-d9aecffe

This will find the record Id for each lint error

finActionsOn

read(point and actions).finActionsOn()

Now Actions can be dynamic on any record. With a tag called actionsOn see this link for more detail. actionsOn function

each rec

readAll(point and equipRef->ahuRef->id==@191d48c4-95263c67).each rec => rec.pointEmergencyOverride(67%)

write each point in a readAll

map example

readAll(point and equipRef->ahuRef->id==@191d48c4-95263c67).map(rec => {result: rec.pointEmergencyOverride(67%)})

The above each example is better then the above solution which is a map solution. The map solution outputs a dict, so that why we need the { } here

boxWisker Test function below

readAll(point and sensor and temp and hisSize).map(r=>r.boxWisker(2012-05))

Make sure the point has a history

finPointSummary

readAll(site).finPointSummary(ahu and not rooftop,point and summary)

This will let us build a summary page for equipment and points. Kind of like FQL. The function takes 3 paramteres. 1. site or floor, or any record. 2. Takes a filter for equipment. IE... rooftop. 3. Takes a filter for a point. Typically this will be like floorSummary.

So the the query to left does this. Finds the first site, and then finds equipment under that site that is an ahu and not a rooftop, then it finds all the points that have a summary tag on it. This will then return a grid with equips as rows, and points as colums

readAllTagNames find kind

readAllTagNames(point).findAll r => r["kind"] == "Ref"

This will find all the tags, and it will let you find all the ref tags. This will be helpful in our search for tags.

random grid

["Jan", "Feb", "Mar", "Apr", "May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec"].toGrid.map((r,i)=>{dis:r->val, value:random(0..100), index: i+1})

This let's you name a bunch of crap, and get a demo grid that comes out. :}

week for a year

read(kw and hisSize).hisRead(lastYear).hisRollup(sum,1wk).map((r,i) => {ts: r->ts.format("MMM"), v0: r->v0, week:"Wk " + (i+1)})

This won't show the right week if you don't do the entire year. IE...

taskVarTree

taskVarTree(@195357e2-faf5560f,@19042bad-f79d61b9 )

This query shows you a list of all Points/Tags/ and Variables on a particular program, and target. It finds the target, and then

shows every point/tag/variable on the target, then it finds every ref on the target, and finds all of it's points/tags/varaibles. This is

used in the UI to be able to auto complete creating variables. The function 1st parameter takes the program ID, the 2nd parameter takes the taret ID.



do foo:read(projMeta); if (foo.has("dis")) foo->dis else context()->projName end



taskVarsLinkedTo

taskVarsLinkedTo(@19a02c31-673132ee, @19042bad-73b30a28, "cool1")

This function is currently working on 1000, and will tell you all the places that this var is being used in.

findAll Example

readAllTagNames(point).findAll r => r ->kind.contains("Number")

This is an example on how to go through a grid, and find all things that contains a certain text

findAll with or

readAllTagNames(point).findAll r => (r ->kind.contains("Number")) or (r ->kind.contains("Str"))

This is an example of a find all with an or.

nHaystack and haystack Sever

http://localhost:85/api/demo/read?filter=site

This shows you how to query all the sites in a haystack server. Doing a get out of haystack to fo a filter on points

hisRead

http://localhost:85/api/demo/hisRead?id=@Carytown.RTU-1.DischargePressure&range=today

Example of getting a his out of haystack via a get.

hisRead date range

http://localhost:85/api/demo/hisRead?id=@Carytown.RTU-1.DischargePressure&range=%222013-01-05,2013-01-09%22



eval and expr

http://localhost:85/api/demo/eval?expr=readAll(site)

Axon query in the url

set points in monitor mode

taskSetMonitorMode(@19a167ef-14270409, @19042bad-73b30a28, {setPoint: true, zoneTempSp: true, returnTemp: true, blah: true, writeTempPID: true, link1: true})

This is used in Control logic and it lets you set points into a monitor mode. You can only set 8 points in at once.

poll the points from monitor

taskMonitorPoll(@19a167ef-14270409, @19042bad-73b30a28, null)

This is how you poll the points in monitor mode

tasksVarsLinkedTo

taskVarsLinkedTo(@19c3d30f-63117dc0, @19042bad-73b30a28, "zoneTempSp")

This finds all the other programs tasks that are using this Variable. Very helpful for troubleshooting

programVarLocs

programVarLocs(@19c3d30f-63117dc0)

This finds all the locations in a program that the variable is used, and tells you if it's being set or not.

one liner if statement

if (read(projMeta).has("dis")) read(projMeta)->dis else context()->projName

if this then tha

obix Server

/api/demo/ext/obix

This is the path to the obix server. FIN Stack is a obix client and obix server.

obix server

Server URIs

The following table summarizes the URIs of the Obix server API:

Uri Object ------------------------------- ------------- /api/{proj}/ext/obix/ lobby /api/{proj}/ext/obix/about/ about /api/{proj}/ext/obix/batch/ batch op /api/{proj}/ext/obix/watchService/ watch service /api/{proj}/ext/obix/watch/{id}/ watch operations /api/{proj}/ext/obix/query/{filter} query recs using filter /api/{proj}/ext/obix/rec/{id|name}/ record /api/{proj}/ext/obix/rec/{id|name}/{tag} tag /api/{proj}/ext/obix/icon/{id}/{uri} icon tunnelling

Here are the options



http://finanalytics.j2inn.com:999/api/demo/ext/obix



query or filter

http://finanalytics.j2inn.com:999/api/demo/ext/obix/query/curVal%3E5

This is how you can query obix. query/and it takes a filter

gridColKinds(grid)

[{dis:"Site-A", area:2300ft²},     {dis:"Site-B", area:3100ft²},     {dis:"Site-C", area:1950ft²}].toGrid().gridColKinds()

This will take any grid and return you a list of the unique tags, and tag counts

scalar

age:34



dictionary

{jason,brihhhs,name:"ddddd"}



list

["dfsd","dsfsdf","SSSSS"]



Grid "list of Dictionaries"

[{age:45,lastName:"Briggs",ahu,temp,sensor},{age:45,lastName:"Briggs",sensor}]



Muliple points

readAll(point and equipRef->rooftop and hisSize>20).hisRead(2012-12-01).hisInterpolate()

This shows how you can show multiple points on a chart

related graphics

readById(@19042bad-0aa06641).finGraphicOn(true)

This function will show you all the related graphics for a record

grid to commit

ioReadCsv(`io/UF_Devices.csv`).floridaImport().toRecList.map( row => diff(null,row,{add})).commit()

this takes a grid, and commits it to the database

read files out of a directory

ioDir(`io/`)

This let's us read files right out of a directory

read a bacnet Schedule

This will read all the events from a bacnet Schedule

 write a weekly schedule to a bacnet Schedule point

 

 This will write all the events for a week into a bacnet Schedule



finPointSummary

readAll(site).finPointSummary(ahu and rooftop,cmd and point)

Here is a cool way where point summary will just work :}

zip selected records

ioZipEntry(`io/haystack/Archive.zip`,`/vav.trio`).ioReadTrio()



read csv file and return number of unique records

ao: ioReadCsv(`io/test/bacnetList.csv`).findAll(row => row->dis.contains(":")).map(r => {new:r->dis.split(":").get(-2)}).map(r => {test:r->new.split(".").get(-1)}).unique("test").size()



rebuildDocs()

rebuildDocs()

This should just do a fresh rebuild of the docs

finBackupRecord()

finBackupRecord(@Gaithersburg.RTU-1,{files:true, references:true})

This will create a backup of all the records with the specified equip ref

@Gaithersburg.RTU-1 is the equip ref that will be present on all imported files in the graphicOn tag ( graphicOn: "equip and id==@Gaithersburg.RTU-1")

finRestoreRecord()

finRestoreRecord(@1a026bc8-a4879a4b)

This will restore all the records with the specified id

@1a026bc8-a4879a4b is the ref of the backUp created with the above function

trio and commit it

ioReadTrio(`io/HUEBackup.trio`).toGrid().removeCols(["dis","mod"]).toRecList.map( row => diff(null,row,{add})).commit()

remove the dis tag first, before re-committing

toAlarm()

read(point and temp).toAlarm( "myAlarm", {priority:77,alarmText:"This is a bad alarm",instructions:"1. Do this 2. Do that 3. Also this 4. Foo foo and foo",alarmType})

this will put a target into alarm.

toNormal()

read(point and temp).toNormal( "myAlarm", {priority:77,alarmText:"This is a bad alarm",instructions:"1. Do this 2. Do that 3. Also this 4. Foo foo and foo",alarmType})

This will put the alarm back to normal

find task for a certain target

tasks().findAll r=> r->targetRef == @19042bad-73b30a28



find alarms by ID/target

navFilter("alarmTarget", readById(@19042bad-1077bbbc), null).alarms(2014-01-29)

navFilter("alarmTarget", readAll(equip), null).alarms(today)

Find alarms for a particular target

Find all alarms today, and the 3rd paramter is the last read timeStamp

navFilter("alarmTarget", null, null).alarms(today,null,"1739583508091440") or navFilter("alarmTarget", null, null).alarms(today,id==@20151006:overHeatedZone.overHeatedZone:1a17dcf9-7f7947ab,"1739583508091440")

The last paramter here allows you to see what alarms are new. The result of this query will give you a new number each time

How to ack alarms

alarmAck([@20151006:overHeatedZone.overHeatedZone:1a17d935-2e1ab4be,])

This is how to ack alarms

find notes for a particular target

navFilter("noteTarget", @19042bad-73b30a28, null).notes({  }).noteSummary



find notes that are open for a target

navFilter("noteTarget", @19042bad-73b30a28, null).notes({ state:"open" }).noteSummary



delete an alarm or alarms

alarmRewrite({id:@20140226:ddd.testAlarm:19042bad-a440e9b2})

You can also put that into a list, and delete multiple alarms at a time too

findAll tags that exist on a grid

finNavChildren("equip",19042bad-73b30a28).findAll r => r.has("schedulable")

This "has" fucntion looks on a grid and figures out if the schelable tag/column exists, and filters out the ones that don't

findAll and I use not

finNavChildren("equip",19042bad-73b30a28).findAll r => r.has("schedulable") and not(r.has("scheduleRef"))



r.dis, finds dis on IDs

readAll(schedulable and point).findAll r => r.dis.lower().contains("Cary")

This finds converts the dis to lower case, and

keepCols and find enums

readAll(enum).unique("enum").keepCols(["enum"])

Find all enum points, and return the unique vales of enums, and keep only the enum column

connector point details

connAppDetails("haystack",1aaa6dcc-238b1ec6)

will return you the details of what's going on with the connector. It tells you if the point is currently in a watch, and what the status of the cur,his, and write is

taskBundles shows the bundle grid

taskBundle(1aaa1f80-1133a8b3, 1a169f42-a1d26b60, "vavTesting")

taskBundle(programId, targetId, "variableBundleName")

NavMeta Tag List

finNavMetaTagList(@id) - this will do drillUP by default and not show the values

finNavMetaTagList(@id,{values:true}) - this will do drill up by default and show the values of the tags

finNavMetaTagList(@id,{drillDown:true}) - this will do drill down and not show the values 

finNavMetaTagList(@Carytown.Misc.Occupancy, {values:true})

the id has to be of a site, floor, equip, or point. The goal of this is to show a list of all the possible tags, so that we can let shally use this in an auto complete UI for finding things similiar to what the user picks. IE... If I"m on a Room Temp pont, then I can click batch edit, and it will show me all the tags that I want to search for based on the ROom Temp I'm at.



The drillDown is also an autoCOmplete function that Shally can use, that will show us all tags from this point down. It also tells us how many tags there are.



readAllTagNames(equip).findAll r => r->kind.contains("Marker")



finds all fin files that are using that asset

finAssetUsedBy(assetID)

Given an asset or list of asset records or refs return a grid with fin project records that use those assets

deletes all finp files and their assets

finFileAndAssetDelete(projects)

Given a finProject or list of finProject records or refs the function will delete the finProject(s) and assets that are use only by this project. Returns a grid with all records that where deleted

Sample writing axon on one line

do equipSize:readAll(equip);  if (equipSize.size() > 250) true else equipSize end

This checks on the server to see if there are more than 250 equips before it returns me all of them.

Remove Meta ON a Grid.

read(kw and hisSize).hisRead(lastYear).hisRollup(sum,1wk).map((r,i) => {ts: r->ts.format("MMM"), v0: r->v0, week:"Wk " + (i+1)}).addMeta({-ts,-hisEnd,-hisInterval,-hisLimit,-hisStart,-view}).foldCol("v0",max)





readAll(haystackConn).each((row) => finNHaystackApplyBatchTags(row->id, "point and axSlotPath", "switzerland", true))



Read json data

`https://itunes.apple.com/lookup?amgArtistId=468749,5723&entity=album&limit=5`.ioReadJson->results

read json data from Itunes example

nHaystack search and replace

read(haystackConn).finNHaystackSearchAndReplace("point", "COM_123_", "")

Make sure its a dict, and not a grid. So don't do a readAll. This goes into nHaystack, and finds all points, and searches for COM_123 then it replaces it with nothing. THis edits the point name

equipment

readAll(what you want to search).map (r => r.set("treePath", getNavURI(r->id, "equip")))

This is how you can get the equipment path that Rochelle uses for relativized grahics

find unique tags

finUniqueTags("ahu")

This will find all records with the specified string tag and group them

bacnet point priority array

bacnetReadObjectProperty(@bacnetConn, "bv1", "priority_array")
bacnetReadObjectProperty(@bacnetConn, @bacnetPoint, "priority_array")

To just read one value you can do:
// read level 8 value
bacnetReadObjectProperty(@bacnetConn, "bv1", "priority_array")[7]

This function will give an array with all the priorities and their values for a bacnet point and connector

Find and Replace

readAll(actions).findAll(r => r->actions.contains("\\\\")).toRecList.map(r => diff(r, {actions: r->actions.replace("\\\\", "")})).commit

Find and replace in the actions grid.

Schedule Copy



finScheduleCopyDays(@1b24af89-91f8f414, "tue", "mon") - we copy that schedule from tue to monday

finScheduleCopyDays(@1b24af39-10fc2beb, "mon", "workWeek") - will copy that schedule from mon to the hole work week

finScheduleCopyDays([@1b24af89-91f8f414, @1b24af6d-4e0962bd], "tue", "workWeek") - this willcopy tue to the hole work week for both schedules

This functions will copy paste one schedule day to another time interval or day

find and then delete alarms

navFilter("alarmTarget", null).alarms(today).toRecList().alarmRewrite()

This will allow you to delete all the alarms for today

walks up the tree and shows you all tags and values

read(point and temp).finNavMetaTagList({values:true})

This walks up the tree from where you are at, and gives you all the tag values

Walk down the tree and show tags

finNavMetaTagList(@Carytown, {drillDown:true})

This will walk down a tree and find all the tags. This is used with the filter

Sync histories for haystack

read(haystackConn).haystackCall("scheduleWrite", scheduleHis(@1b48a018-1a825237, today()..today()+7day, {timeline}).addMeta({id:@S.Winterfell.Equip1.BooleanWritable}))

readAll(haystackHis).haystackSyncHis(null)



[4,5,6,6,7,7].get(-1)

This will get the index of the list/array



{age:99,tom:88,show:"Lake"}.get("tom")

This will get the tag value in a dictionary based on tag name



[{age:99,tom:88,show:"Lake"},{age:44,tom:1,show:"Celtics"}].get(0) -> show

This will get the first dictionary, in the list of dictionaries, and then it will return the tag value in the dictionary of show



[{name: "j", value: 10},{name: "j", value: 13},{name: "j", value: 14},{name: "j", value: 15}].map r => if (r->value > 13) {name: "new", value: r->value * 2} else r

This will let you map a dict. The map function always returns a list of dictionary's.



map on a list with a else

[4,45,45,45,567,56].map r=> if (r > 45) "Jason" else "Whalen"

Map a list. Go through each item in the list, and if it's great than 45 then create a new Jason, otherwise create whalen



readAll(point and temp and discharge).map r => try {id: r->id, curVal: r->siteRef->area / r->curVal} catch null

This is a map on a list of dictionaries or a grid, and we use the try function to see if a certain value isn't there then just do null.



(readAll(point and temp and discharge).map r => try {id: r->id, curVal: r->siteRef->area / r->curVal} catch {id: "nothing", tim:"dfgdfg"}).findAll j => j->id == @1a17dccc-c04ee0b0

take a grid , use a map and create a new list of dictionaies, then findAll the dictionaies where the value in one of the items == an id



[1,2,3,4,5,6,7,8].map() (r,index) => r * index

Get the index of the row you are currently on in a map.



find curVal alksdjflks;dfjlk;asd

readAll(demand).findAll() (row => row.has("curVal")).sortr("curVal").get(0..4)



Read a file from the bin.

readById(@1b6bd09b-0858a23d).ioBin("file").ioReadTrio()

Just pass the record, and the tag name and it will automatically give you an URI that can go into the ioRead



read(id==@1b6c4554-4d0b28c8).toRec().ioBin("file").ioReadTrio()



Take a grid, and blacklist a certain about of tags in that list

readAllTagNames(ahu).keepCols(["name"]).findAll(row => (["ahu", "equip"].find(testRow => testRow == row->name)) == null )



Take a list, and blacklist certain values

["test", "test1", "test2", "test3", "test4"].findAll(row => (["test2", "test3"].find(testRow => testRow == row)) == null )



name contains some in the list

readAllTagNames(ahu).keepCols(["name"]).findAll(row => (["ahu", "equip","id","Ref"].find(testRow => row->name.contains(testRow))) == null )



find and add a tag for something

finUniqueActions(actions).map row => if ((["override", "set"].find(testStr => row->expr.lower.contains(testStr))) != null) {dis:row->dis,expr:row->expr,canHaveDuration} else {dis:row->dis,expr:row->expr}



black list if contains, or black list if endsWith

readAllTagNames(ahu).keepCols(["name"]).findAll(row => (((["ahu", "equip","id","Ref"].find(testRow => row->name.contains(testRow))) == null) or ((["Cur", "Write","His"].find(testEnd => row->name.endsWith(testEnd))) == null)))



use set to add a key:value to a dict

finUniqueActions(id==1b6d65e9-a963bc7d).map row => if ((["override"].find(testStr => row->expr.lower.contains(testStr))) != null) row.set("canHaveDuration", marker()) else row



Set a number property on a dict

{lastName:"Briggs",firstName:"Jason"}.set("age",34)



Set a marker on a dict

{lastName:"Briggs",firstName:"Jason"}.set("age",marker())



find tags, then search and replace, and create a filter

finUniqueTags("equip").map row => {count:row->count,markers: row->markers.replace(","," and ")}



take navMeta and create a list to see what all is there

read(navMeta).get("equipPath").split("/").remove(0)



This one is same as above except it removes the options bracktes [ ]

read(navMeta).get("equipPath").split("/").remove(0).map() row => row.replace("[","").replace("]","")



readByIds()

readByIds([@Gaithersburg.RTU-1,@Gaithersburg.RTU-2])



finEquipPontTagClone

finEquipPointTagClone(@1a17da4b-5e0ad22f, vav, [{navName:"DAT"},{navName:"AirFlow"}], {jason,foo,test,myAge})

This function will look at a equip, and it's points, and allow the user to pick what he wants to clone

dynamic path in a function for an image

logo: ("/api/" + currentProject + "/rec/myLogo/file").parseUri

put this in a function for dynamic paths. myLogo is the name of the image file

join or merge, basically join to grids together

readAll(point).keepCols(["id", "curVal"]).join(readAll(point).keepCols(["id", "curStatus"]), "id")



read a file out of a pod

http://localhost:8085/pod/finStackMobileExt/mobile/assets/identity/fin_stack.png



write Zinc or csv or trio etc without writing to disk

ioWriteZinc(customAhuActions(capitalize(row->kind)), ""))

Leave the string empty and it will just write the string to the blah blah :}

ioWrite if you leave the 2nd paramter as an open string will just return the string into folio as a val

read(energy).ioWriteJson("")

read(energy).ioWriteTrio("")



Not Querying Equipment:

finNavChildren("equip",@1a169921-22d9c3b8).findAll(row => not row.has("equip") or not row.has("equipRef"))



Querying Equipment:

finNavChildren("equip",@1a169da0-ef590b20).findAll(row => row.has("equip"))



try and catch and image

readAll(siteMeter).map(row => row.set("imageRef",try row->siteRef->imageRef catch null)).keepCols(["dis","id","kwhThisWeek","submeterDownCount","submeterCount","meterStatus","siteRef","imageRef"]).sortr("kwhThisWeek").get(0..4)



finGridFilter(area > 1000)

finGridFilter(area > 1000)

Now you can use a filter on any grid. Typically you wouldn't do this on a folio database, you would do this on a grid that is not in the folio database. On things like csv files, etc...

Copy a point to all equip with the "copyTo" tag

finCopyBoundPoints([@1baf7453-3e9a0965], copyTo)

The way it works is that you pass it a list of points that you'd liked to copy as the first parameter, and a query as the second parameter that should find all of the equipment that you'd like to copy the points to.



What this does it copy that point to all of the equipment with the "copyTo" tag. This can be any axon filter to select the target equipment.

This function can handle multiple points at the same time. by adding them to that list of record ids.

use equip tree, and find all equips for all sites

navFilter("equip", readAll(site), {targetTag: "equip"}).toRecList



use nav tree to find chidren objects

finFindRecordByChildDeep(equip, kw)

If you want to find every equip, that has a point that has kw, then this query will work

not sure why you would use this one :}

finFindRecordByChild(equip, kw)

Whalen wanted this one, but not sure why you would want it :} This would does exactly the same as the above one, except it only goes 1 level deep. So if you are wanting to find equips that have these points it will work. This one is way more effiecient than the above one, so it's not a bad idea to use.

find anything in an entire grid

readAll(equip).finFindAnyText("tower vav 1")



how to write zinc to string not disk

[{jason:12,frank:12},{jason:12,frank:12},{jason:12,frank:12}].ioWriteZinc("")

Works for json, etc too

showWatches in Niagara

http://localhost/haystack/extended?function=showWatches



showPointsInWatch in Niagara

http://localhost/haystack/extended?function=showPointsInWatch&watchId=@b67c022f-07bd-4b6a-84e3-3c19e06291bc 



cool

read(his and fan).hisRead(thisWeek).hisRollupAuto(1day).toGrid().map row => {starts:row->v0.parseDiscretePeriods().size(),duration:row->v0.parseDiscretePeriods().foldCol("dur",sum),ts:row->ts}



emailTestDigest("alarm")

You can test the digest with this function. It also works for spark and note and alarm



topicOn:"topicOn==priority == 107 and targetRef->vav"

topicOn a filter that runs on things, like alarm records. The targetRef just looks on the alarm, then the target and sees if it's a vav



read(id==@1c1cd1d0-c583d447).haystackSyncHis()

This will let us do a haystackSyncHis, I think you can read multi points at a time to do this



Query to just see if things are cool



This one will take a tag, and copy to another tag on the same record.



Here is a function that will read xml in FIN STack, make sure that the xml connector is enabled.



This function let's you read any property out of a device. Param-1 is the connector, and the Param-2 is the Connector in this case only if you want to read the properties directly out of the device, if you want to get the properties out of the object like AV-1, then you can enter that too



This will query Nhaystack and find all points, and show all unique tags that are axType



finNHaystackReadSize(@1c924748-c7555d17, "point")

This will do a read count for nHaystack



read(point and his and kind == "Bool").hisRead(lastWeek).hisRollup(durTrue, 1day).foldCol("v0",sum)

read(point and his and kind == "Bool").hisRead(lastWeek).hisRollup(count, 1day).foldCol("v0",sum)

This is to calculate how long the point has been on/true for specified period



haystackCall(read(haystackConn and dis=="localhost_91"), "extended", toGrid([{function:"uniqueEquipTypes",filter:"equip", percentMatch:50}]))

This shows you how to call an op from FIN Stack, and how to pass paramters. This is mainly the extended op functionailty



read(haystackConn).haystackCall("read",{filter:"equipType"})

simple haystack call that is doing a read



read(haystackConn).haystackReadAll(point).findAll point=> point.dis.contains("Fan")

contains query for your haystack connector



actions 1 for 1

 

ver:"2.0"

 

expr,dis

 

"pointSetDef($self, $val)","Set"

"haystackInvokeAction($self->haystackConnRef, $self->haystackWrite, \"override\", {value: $val, duration: $duration})","Override"

the 3rd parameter needs to match the name of the action

The dictionary needs to match the format of the action in Niagara.

This is just the dis for FINStack

finExec

finExec(`io/finExecProperties.trio`,{foo:5},false)

1st param = file path

2nd param = declare a variable (leave as null if variables are declared in file).

3rd param = true or false to return or not to return a grid

readAll(foo).each x => diff(x, {equipRef: read( navName=="Relay "+x->greenMaxRelay.toStr and equipRef==@1d96e8fd-0258991c )->id}).commit

This gets all the points and puts them inside of their equipment foobag

Koppany made this, ask him.

readAll((equipRef->navName!="AHU-1") and (point and equipRef->ahu))

This gets the points from every ahu except for AHU-1

How to do a not using a trap

finWriteBinTag(rec, tagname, handle, mime)

If you want to write a file to the bin, use this function. It will take the record, the tagName, like file, or foo, and the handle. The handle could be a string, in the case you wanted to write json or something. The mime type is the file type, so it could be a png or binary file etc... The mime type is optional as well.



finReadBinTag(rec, tagName)

This will read a file, it will either return a string if the mime type is text, and if it's a binary file it will return the handle.



get points that are overridden

readAll(point and writable and equipRef==@1eeb0307-95d36539).findAll(v => v.pointWriteArray().find(r => r.has("val") and (r->level == 1 or r->level == 8)) != null)

or

readAll(point and equipRef==@1eeb0307-95d36539 and (write1 or write8))

or

readAll(point and equipRef==@1eeaff69-30d84ee5 and (writeLevel==1 or writeLevel==8))

This returns every point that is overridden from priority 1 or 8 on equip. If you want to just count, them then just do .size at the end.

The top one includes overrides set with a duration.

The middle one excludes overrides with duration.

The last one includes overrides set with a duration.

delete alarms

finDeleteAlarms(today)

this is the function to be able to delete alarms

Delete alarms based on a filter

navFilter("alarmTarget", null).alarms(today).finGridFilter(alarm and foo).toRecList().map(x=> {id:x->id}).alarmRewrite()

  • alarms(today) can be changed to any other available dateRange param

  • finGridFilter(alarm and foo) can be changed to fit the filter for desired alarms.

Send history report (CSV) manually
(FinHtmlReporting extension needs to be enabled and make sure SMTP settings are configured)

Example 1:

Example 2:

Function parameters:
title: report title (string),
filter: point filter (string),
dateRange: today(), thisWeek(), now()-24hr..now() (last 24hrs) etc,
user id’s: [@u:ricky, @u:support] (user record must contain a valid email),
whenToRun: "every 1day" (daily), "every 7day" (weekly), or "" (monthly),
parameters if enabling rollup else not taken into consideration:
{enableRoll: true,
fold: "auto" (options: auto, min, max, avg, sum),
interval: "1min" (options: auto, or whatever interval with suffix min, hr, day, week, mo, yr)}
precision: decimal precision (number)

Functions

This function takes a csv file, and creates a grid. Then later I can then create it directly in my database. By usig this query. ioReadCsv(`io/UF_Devices.csv`).floridaImport().toRecList.map( row => diff(null,row,{add})).commit()

Another function

/////// createContacts(10,true,false) This is a sample query.

 

The below function takes a list, and a setpoint. If the min value is > the setpoint, then the fan is true, else it's false. Below is a sample query.

Simple Simple fuuncctionion

DrTargeDrTargetKw(Kw()

****
** Demand Response function for a grid of sites
**/
** Demand Response function for a grid of sites
**/

boxWisker()

This is a function we wrote with Assetworks that allows you to create a chart. The data is in a grid, so we still need to make a chart.

If you want to run this normally in folio us this query.

read(point and sensor and temp).boxWisker(2012-05)

The below query will take a list of point, and then go through every row, and run the boxWisker() function

readAll(point and sensor and temp).map(r=>r.boxWisker(2012-05))



siteMeterKwhWrite ---- ex: siteMeterKwhWrite(thisWeek)

What this function takes a date range and then takes the last record minus the first record then it will write to a property tag on your siteMeter with "kwh" in front of whatever you typed within the axon. For example the one above was for thisWeek so it will write to a property called kwhThisWeek

(rangeFunc) => do

meters: readAll(siteMeter)
rangeFuncName: rangeFunc.toStr[0..-3]
rangeFuncName = "kwh" + rangeFuncName[0..0].upper + rangeFuncName[1..-1]

meters.each(meter =>
do

kwhPoint: read(kwh and equipRef == meter->id)
kwhTrend: kwhPoint.hisRead(rangeFunc).hisClip().toGrid()

if (kwhTrend.size > 0)
do

firstRecord: kwhTrend.toRecList.get(0).get("v0")
lastRecord: kwhTrend.toRecList.get(-1).get("v0")


value: lastRecord - firstRecord
meter.diff({}.set(rangeFuncName, value)).commit()
end

end)

end

countDownSubmeters:

() => do
siteMeters: readAll(siteMeter)

siteMeters.each(siteMeter =>
do
downCount: 0
submeters: readAll(submeterOf == siteMeter->id)

submeters.each(submeter =>
do
kwhPoint: try read(kwh and equipRef == submeter->id) catch null

 

if (kwhPoint != null and kwhPoint.has("hisEnd") and (now() - kwhPoint->hisEnd) >= 2)
downCount = downCount + 1

end)

siteMeter.diff({submeterDownCount: downCount}).commit

end)
end

countSubmeters:



() => do
siteMeters: readAll(siteMeter)

siteMeters.each(siteMeter =>
do
submeters: readAll(submeterOf == siteMeter->id)

siteMeter.diff({submeterCount: submeters.size}).commit
end)
end

finDateSpan:

(numberUnits) => do

currentDate: now().date() + numberUnits;
currentDateTime: now() + numberUnits;

day: currentDate + ", day";
week: currentDate + ", week";
month: currentDate + ", month";
year: currentDate + ", year"

 

dayString: currentDateTime.format("MMM - DD - YYYY");
weekString: currentDateTime.format("VV - YYYY");
monthString: currentDateTime.format("MMM - YYYY");
yearString: currentDateTime.format("YYYY");

 

{
dayCol:day,
weekCol:week,
monthCol:month,
yearCol:year,
dayStringCol:dayString,
weekStringCol:"Week - " + weekString,
monthStringCol:monthString,
yearStringCol:yearString,


}
end

finCommit:

(item, changes: null) => do

dicts: []

if (item.isGrid or item.isList)
do
if (item.isGrid)
dicts = item.toRecList
else
dicts = item
end
else
if (item.isDict)
do
dicts.add(item) 
end

 

dicts.each(dict => 
do
if (changes != null)
do
diff(dict, changes).commit()
end
else
do
diff(null, dict, {add}).commit()
end
end)

dicts.toGrid
end



Example: ["janLow","janHigh","febLow","febHigh"].each( row => read(meter and foo).finCommit({}.set(row,1)))



function that takes the weather apps sunrise/sunset tag and the condition tag to get a computed tag called “sunny”. So basically if sunrise/sunset = sunrise and weather condition = 1 (clear), then my computed tag “sunny” = true



(theEquip, dates) => do

  w: read(id==theEquip->siteRef)->weatherRef

  

  read(temp and weatherPoint and weatherRef==w->id)

    .hisRead(today)

  

end

 (calculateMeteredData) This function gives you the difference in value for each hour then what you can do is you can run a foldCol at the end to get the total value if you want that total.

ex:  read(his and kw).hisRead(today).calculateMeteredData

(hisGrid) => do

 lastValues: null

    

    result: hisGrid.map(row => 

            do

                if (lastValues == null)

                do

                    lastValues = {}

                        

                    row.each((value, key) =>

                    do

                        if (key[0] == 118) // "v"

                             lastValues = lastValues.set(key, value)

                    end)

                    return row

                end

                else

                do 

                    row = row.map((value, key) =>

                    do

                        if (key[0] == 118) // "v"

                        do

                            lastValue: lastValues.get(key)

                            newValue: value - lastValue      

                            lastValues = lastValues.set(key, value)

                            return newValue

                        end

                        return value

                    end)

                    return row

                end

                return row

            end)

 return result

end

finCommit example