Translation table using SQLTags

Archived Topics
1

I have an input that can be one of a range of integer values. I need to translate this value into a corrected value as shown in the following table:

Raw Corrected
1 1
2 2
3 3
4 4
5 4
6 5
7 6
8 7
9 8
10 8
11 9
12 10
13 11
14 12
15 12
16 13
17 14
99 Error

Any idea how I can achieve this using SQLTags?

2

I would bring it in as an OPC tag, and then create an expression-based DB tag that was bound to an expression like:

if([.]RawValue <= 4, [.]RawValue, if([.]RawValue<=9, [.]RawValue-1, if([.]RawValue<=14, [.]RawValue-2, if([.]RawValue<=17, [.]RawValue-3, 99))))

You’d have to use the 99 code to signify “error” somehow, either using colors or text.

Its kind of a brute force approach, but it would work. Another idea is to put the table you describe into a 2-column static DataSet property on the window, and then bind the on-screen objects to a lookup expression involving the raw value SQLTag and the lookup dataset. That approach would be cleaner, especially if the translation table is bound to change, or there are lots of things to put through this translation.

Hope this helps,

3

Thanks for that Carl.

I also need to log the corrected value. Does this mean I’ll have to carry out a similar kind of operation in FactorySQL? This may be a good time to ask what is meant by SQLTags History in the proposed features for FactoryPMI v3.2.

4

Your intuition is correct - SQLTags History will make logging an expression-based tag trivial. Until then, yes, you’ll have to do similar logic in a FactorySQL group that is logging the history.