Hi
I wanted to know if the table component or the dataset has functions with which it is possible to find elements, remove elements, add elements (element by element) to it.
Could you give me a full list of functions for data sets and tables (such as Lookup(), range, colomncount, …). I miss these in the manual. Is it true there is no rowcount function for the dataset. I wanted it to loop through the whole dataset.
Here is what I need the functions for.
To reduce large amounts of small select queries my idea was to load a large dataset with all what the client might need during a session. I would for example load a dataset with the following columns using a select query to the database:
FactoryID TagID TagName
1 1 xxx
1 2 xxxx
1 3 xxx
2 4 yy
2 5 yyy
3 6 yyyy
Then I would loop through the whole dataset and look up what the user needs. For example all tagID’s and TagNames for the factoryID = 1. My idea then is to put the result into another dataset and work with it (e.g. display it in a drop down etc.) here comes my need for an add element function for the dataset. Is it possible? Do you have a better way of solving this?
Kamal,
Why not set up user filtering (Drop down list, user search box on a column, etc) then apply that input to a WHERE clause - you would filter the data as you run the query? This would be much more straightforward than scripting. Am I missing something in your requirement?
I agree with nathan - it sounds like you are trying to re-invent what databases area already natually good at - retrieving data based on conditions.
However, you are correct that sometimes you want to bring data in in ‘chunks’, and let FactoryPMI sort out the fields for performance reasons. This is typically done with expressions. In particular, look at:
So, for your example, I would first let the database filter out by FactoryID. This gives you a dataset full of TagID, TagName columns. You can then look up the value you need using the “lookup” expression.
I might have been to brief in my description. My project is a project where different users can see one or more factories. A user is only allowed to see factories he has a relation/allowed to. Each factory has 2 or more tags. A user is typically allowed to see 1 or 2 factories unless he is a service that can see all. Below is an example of the factories and tags that a specific user is allowed to see (This is a simplified dataset, there are more columns):
FactoryID TagID TagName
1 1 xxx
1 2 xxxx
1 3 xxx
2 4 yy
2 5 yyy
3 6 yyyy
He would have 1 drop down in FPMI displaying FactoryIDs 1, 2 and 3. He would have another drop down with all TagID’s that correspond to the FactorID he has chosen. Assume now that the user chooses for example factoryID = 1 then he will get the available tags which are 1, 2, 3 in the TagID dropdown. He can then choose either of the tags to see a chart of it. I would have a query that uses the above factoryID, TagID, StartDate, EndDate, to query the database. He can then change to factory 2 and see a curve of tag 5. and so on. What i want to reduce is the continous database query of what factoryIDs and what TagID he is allowed to see every time he changes his factory selection or moves to another window …
If i use the look up function then i would look up a TagID corresponding to FactoryID = 1 (for example). This will only give me TagID = 1 and not 2 and 3. Or is there another way?
The above dataset will have more colomns such as Country, FactoryName, FacilityName, … which i will be using in chart title, …
Kamal,
You could create 2 dropdown lists and some form of date selection to accomplish this task. The dropdown options are determined by user security, and the selection of the first dropdown, respectively. The key is to do the data filtering in steps as the user selects data.
First dropdown list (Factory Selection) - Bind the list’s dataSet to a query that determines which factories the user has access to. You might need to use dynamic properties and/or a little Jython to make this happen, depending on how you store your users/groups/access permissions. Post specifics to a separate topic if we need to get into ways of accomplishing this. You probably want to UNION the result with an empty or none response, which is the default value. You could also use other columns or a lookup table to present the data in nice strings. In your case the user should be able to choose between: None, Factory one, and Factory 2.
The second dropdown list (Tag Selection) - Bind the list’s dataSet to a query that determines which tags correspond to the selected factory. You will then UNION it will a default value of no selection. It will be a simple SELECT query that has a "WHERE FactoryID = {FPMI_Path_to_first_dropdown_selected_value}
Your date range selection method and associated query will be bound to that second dropdown that tells you which Tag to use. The graph’s dataSet will be bound to that.
A few additional recommendations:
Turn polling off. A change in dependencies (changing the first dropdown list) will fire a new query in the second.
If you want the selection to stick between closing the window, under the window expert properties, set the Cache Policy to Always.
Consider using a date range component instead of 2 calendars or a calendar and a drop down list.
Make sure you consider the none selected (default) case for each level of these bindings. In most cases you can use a WHERE clause that resolves to false. Make sure that your graph displays properly with no data.