Changing from SQL server to ignition historian

[Conn_Mccurdy]

So I have a database in SQL that only shows data for the moth that we are currently in. I am looking for a way to either have the name of the database update automatically every month or just use ignition itself to store and pull the data from.

This is the name of the database.

The problem is that next month i will have to go into SQL and change the name of the database to ‘sqlt_data_1_2022_08’ or else the table will not record any data because it is still looking for the data form the previous month.

Any help would be great.

[bschroeder]

What is pulling the data from the SQL Database?

What you are seeing is the normal table partitioning for the Ignition historian. Typically you wouldn’t pull directly from the tables. You would use the internal system.tag.queryHistory function: system.tag.queryTagHistory - Ignition User Manual 8.1 - Ignition Documentation

Or if you have a 3rd party system that is reading data, then you would store that data specifically for that system using the Transaction Manager and generating your own specific tables for that system.

[Conn_Mccurdy]

So i am using a SQL query to pull the data from the database and then am coping that query into a named query in Ignition so that i can link it to tables in Ignition.

I tried using transaction groups but they didnt work for my task

[lrose]

The point is you shouldn’t need to pull from the historian tables manually like this at all. If you need to just query the historical data then you should use system.tag.queryTagHistory() it will take care pulling data from the correct table and joining across partitions if needed.

[bschroeder]

Could you use a historian binding on the table instead of a query? That would take care of the table issue.

Like @lrose is saying… if you are querying the historian tables directly, especially in Ignition, then something is amiss in the approach.

[Conn_Mccurdy]

So the reason I am pulling the data manually is because I have multiple markers on a map. When I click on the marker it brings up a popup of the table that shows the data for the marker I have clicked on.

So in the query we are looking for the selected_tag_path because I made a custom property with that name to be used in the query.
So the query is being used to find the specific data for a specific marker so when the marker is clicked on it will bring up the data on the popup for that marker.

here is the script for the map:

I am only using one table but the table will only show the data of the marker that has been clicked even though all the tags are connected to it. It just looks for the correct tagpath

Is there another way of doing this while still achieving the same thing?

Is it possible to do what @bschroeder said and use an expression to find the correct tagpath so I can achieve the same thing where the marker clicked brings up the data relating to the marker?

[lrose]

I’m not sure I completely understand what you’re doing, what I think is that you’re selecting a marker on the map which sets a custom property to the tagPath for that marker, and then you’re wanting to use that tag path to display historical data.

Is that correct?

[Conn_Mccurdy]

yes that is correct

[bschroeder]

And you are displaying the historical data in a table?

I would then have a custom property on the popup/view that is using a Tag History binding that uses that tagPath parameter to generate a dataset you can use in other parts of the view.

Something like this?

[Conn_Mccurdy]

so something like this?

I am just confused on how to use an expression to do what the query was doing where the tagpath = :tagselected.

[bschroeder]

You don’t need to add the where statement… the expression takes care of getting the data from the historian based on the tag you pass.

what happens if you have a valid path to a tag in the selected_tag_path custom property? IE just keep everything between the {} curly braces and see what happens.

[Conn_Mccurdy]

so this is the valid path to a tag in the selected_tag_path

This is what i get from doing that.

[Conn_Mccurdy]

this is the error I am getting if that helps

[bschroeder]

OK take a look at this: Tag History Bindings in Perspective - Ignition User Manual 8.1 - Ignition Documentation

Sorry it’s been a hot minute since I’ve done this in Perspective.

So you will need to do one of two things.

1. Change your selected_tag_path property to be an array and have a single array member under it as an object that has an aggregate, alias and path member. Then when you click on the map update the path member.
2. Have a custom property that is bound to the current selected_tag_path property and then use a script transform to generate the array of dictionaries to generate the list of tags to send to the history binding.

[lrose]

Here is a similar example, that shows how to set up the property for use with the expression.

System.tag.querytaghistory return error

I think that the binding without the transform is the correct way to do this. You have to set it up first though. I will assume that you already have properties for Parameter_1 and Parameter_2. Add a custom prop to the view or coponent that this binding will be on. It must be an Array type. Add an array element of object type for each tag that you want to collect history for. For this example I’m assuming only 1 tag. Add three values to each array element they must be named ‘aggregate’, `ali…

[Conn_Mccurdy]

Thanks a million guys for helping me with this. Works great!!