Condensing named queries

Ignition
,
1 
status = system.tag.readBlocking('[default]RECIPE/TRIG START PROCESS')[0].value
if status == 0:
	if system.gui.confirm("Are you sure you want to start a new process run?", "User Acknowledgement"):
		layer = system.gui.inputBox("Enter starting layer number:", "")
		system.tag.writeBlocking('[default]RECIPE/START LAYER', layer)
		system.tag.writeBlocking('[default]RECIPE/CURRENT LAYER', layer)
		if layer != None: 
			txt = system.gui.inputBox("Enter run number:", "")
			if txt != None:
				date = system.date.now()
				system.tag.writeBlocking('[default]RECIPE/START LAYER', layer)
				system.tag.writeBlocking('[default]RECIPE/TRIG ABORT PROCESS', 0)
				system.tag.writeBlocking('[default]Internal/Trending/Stop Process Trends', 0)
				system.tag.writeBlocking('[default]Internal/TimeStamp2', date)
   				system.tag.writeBlocking('[default]RECIPE/TRIG PROCESS RUN NUMBER', txt)
   				#set constants
   				hmi_ActiveProcess= system.tag.readBlocking("[default]Internal/ActiveProcessRecipe")[0].value
   				system.tag.writeBlocking('[default]RECIPE/TRIG PROCESS NAME', hmi_ActiveProcess)
				hmi_ActiveLayer = system.tag.readBlocking('[default]RECIPE/START LAYER')[0].value
				activeheatrecipe = system.tag.readBlocking('[default]Internal/PairedHeatRecipe')[0].value
				#rowcount
				parameter = {"hmi_ActiveProcess" : hmi_ActiveProcess}
				rowCount = system.db.runNamedQuery("PROCESS NQ/GET_ROWCOUNT", parameter)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/ROW COUNT', rowCount[0][0])
   				#layername
				parameters = {"hmi_ActiveProcess" : hmi_ActiveProcess, "hmi_ActiveLayer" : hmi_ActiveLayer}
				layerName = system.db.runNamedQuery('PROCESS NQ/GET LAYER', parameters)
				hmi_ActiveMaterial = layerName[0][0]
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER NAME', layerName[0][0])
				#layer rate
				layerRate = system.db.runNamedQuery("PROCESS NQ/GET LAYER RATE", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER RATE', layerRate[0][0])
				#layer thickness
				layerThickness = system.db.runNamedQuery("PROCESS NQ/GET LAYER THICKNESS", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER THICKNESS', layerThickness[0][0])
				#layer codep
				layerCoDep = system.db.runNamedQuery("PROCESS NQ/GET LAYER CODEP", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER CODEP', layerCoDep[0][0])
				#layer assist
				layerAssist = system.db.runNamedQuery("PROCESS NQ/GET LAYER ASSIST", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER ASSIST', layerAssist[0][0])
				assistTrue = layerAssist[0][0]
				#layer assist recipe
				assistRecipe = system.db.runNamedQuery("PROCESS NQ/GET LAYER ASSIST NAME", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER ASSIST NAME', assistRecipe[0][0])
				assistName = assistRecipe[0][0]
				#layer apc
				layerAPC = system.db.runNamedQuery("PROCESS NQ/GET LAYER APC", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER APC', layerAPC[0][0])
				#layer pause
				pause = system.db.runNamedQuery("PROCESS NQ/GET LAYER PAUSE", parameters)
				system.tag.writeBlocking('[default]RECIPE/PROCESS/LAYER PAUSE', pause[0][0])
				matparam = {"hmi_ActiveMaterial" : hmi_ActiveMaterial}
				#material source
				matSource = system.db.runNamedQuery("PROCESS NQ/GET MAT SOURCE", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/SOURCE', matSource[0][0])
				#material pocket
				matPocket = system.db.runNamedQuery("PROCESS NQ/GET MAT POCKET", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/POCKET', matPocket[0][0])
				#material sweep
				matSweep = system.db.runNamedQuery("PROCESS NQ/GET MAT SWEEP", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/SWEEP', matSweep[0][0])
				#material voltage
				matVoltage = system.db.runNamedQuery("PROCESS NQ/GET MAT VOLTAGE", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/VOLTAGE', matVoltage[0][0])
				#material oxygen
				matOxygen = system.db.runNamedQuery("PROCESS NQ/GET MAT OXYGEN", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/OXYGEN', matOxygen[0][0])
				#material xtal
				matXtal = system.db.runNamedQuery("PROCESS NQ/GET MAT XTAL", matparam)
				system.tag.writeBlocking('[default]RECIPE/MATERIAL/XTAL', matXtal[0][0])
				if assistTrue == 1 :
					assist = {"hmi_ActiveAssist" : assistName}
					assistBC = system.db.runNamedQuery("PROCESS NQ/GET ASSIST BIAS C", assist)
					system.tag.writeBlocking('[default]RECIPE/ASSIST/BIAS CURRENT', assistBC[0][0])
					assistEC = system.db.runNamedQuery("PROCESS NQ/GET ASSIST EMISSION C", assist)
					system.tag.writeBlocking('[default]RECIPE/ASSIST/EMISSION CURRENT', assistEC[0][0])
					assistDC = system.db.runNamedQuery("PROCESS NQ/GET ASSIST DISCHARGE C", assist)
					system.tag.writeBlocking('[default]RECIPE/ASSIST/DISCHARGE CURRENT', assistDC[0][0])
					assistDV = system.db.runNamedQuery("PROCESS NQ/GET ASSIST DISCHARGE V", assist)
					system.tag.writeBlocking('[default]RECIPE/ASSIST/DISCHARGE VOLTAGE', assistDV[0][0])
				tag = {"hmiActiveHeat" : activeheatrecipe}
				#p1
				P1HOURS = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP1 HOURS POPULATE',tag)
				adjP1Hours = P1HOURS[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P1 RAMP UP HOURS', adjP1Hours)
				P1MINUTES = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP1 MINS POPULATE',tag)
				adjP1Minutes = P1MINUTES[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P1 RAMP UP MINUTES', adjP1Minutes)
				P1SETPOINT = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP1 TARGET POPULATE', tag)
				adjP1Setpoint = P1SETPOINT[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P1 RAMP UP SETPOINT', adjP1Setpoint)
				P1STEP = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP1 STEP POPULATE', tag)
				system.tag.writeBlocking('[default]RECIPE/HEAT/P1 RAMP UP STEP', P1STEP)
				#p2
				P2HOURS = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP2 HOURS POPULATE',tag)
				adjP2Hours = P2HOURS[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P2 PREPROCESS HOURS', adjP2Hours)
				P2MINUTES = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP2 MINS POPULATE',tag)
				adjP2Minutes = P2MINUTES[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P2 PREPROCESS MINUTES', adjP2Minutes)
				P2SETPOINT = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP2 TARGET POPULATE', tag)
				adjP2Setpoint = P2SETPOINT[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P2 PREPROCESS SETPOINT', adjP2Setpoint)
				P2STEP = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP2 STEP POPULATE', tag)
				system.tag.writeBlocking('[default]RECIPE/HEAT/P2 PREPROCESS STEP', P2STEP)
				#p3
				P3DEVIATION = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP3 DEVIATION POPULATE',tag)
				adjP3Deviation = P3DEVIATION[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P3 PROCESS DEVIATION', adjP3Deviation)
				P3OFFSET = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP3 OFFSET POPULATE',tag)
				adjP3Offset = P3OFFSET[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P3 PROCESS SETPOINT OFFSET', adjP3Offset)
				P3SETPOINT = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP3 TARGET POPULATE', tag)
				adjP3Setpoint = P3SETPOINT[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P3 PROCESS SETPOINT', adjP3Setpoint)
				P3STEP = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP3 STEP POPULATE', tag)
				system.tag.writeBlocking('[default]RECIPE/HEAT/P3 PROCESS STEP', P3STEP)
				#p4
				P4HOURS = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP4 HOURS POPULATE',tag)
				adjP4Hours = P4HOURS[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P4 POSTPROCESS HOURS', adjP4Hours)
				P4MINUTES = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP4 MINS POPULATE',tag)
				adjP4Minutes = P4MINUTES[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P4 POSTPROCESS MINUTES', adjP4Minutes)
				P4SETPOINT = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP4 TARGET POPULATE', tag)
				adjP4Setpoint = P4SETPOINT[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P4 POSTPROCESS SETPOINT', adjP4Setpoint)
				P4STEP = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP4 STEP POPULATE', tag)
				system.tag.writeBlocking('[default]RECIPE/HEAT/P4 POSTPROCESS STEP', P4STEP)
				#p5
				P5HOURS = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP5 HOURS POPULATE',tag)
				adjP5Hours = P5HOURS[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P5 RAMP DOWN HOURS', adjP5Hours)
				P5MINUTES = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP5 MINS POPULATE',tag)
				adjP5Minutes = P5MINUTES[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P5 RAMP DOWN MINUTES', adjP5Minutes)
				P5SETPOINT = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP5 TARGET POPULATE', tag)
				adjP5Setpoint = P5SETPOINT[0][0]
				system.tag.writeBlocking('[default]RECIPE/HEAT/P5 RAMP DOWN SETPOINT', adjP5Setpoint)
				P5STEP = system.db.runNamedQuery('PROCESS NQ/HEAT/STEP5 STEP POPULATE', tag)
				system.tag.writeBlocking('[default]RECIPE/HEAT/P5 RAMP DOWN STEP', P5STEP)
				system.tag.writeBlocking('[default]RECIPE/TRIG START PROCESS', 1)
			else : 
				system.tag.writeBlocking('[default]RECIPE/START LAYER', 1)
		else : 
			system.tag.writeBlocking('[default]RECIPE/START LAYER', 1)
if status == 1:
	system.gui.warningBox("Process is actively running.", "User Alert")

In the above code, I have a host of named queries running which are pulling recipe data relevant to the user-defined process. In total, there are roughly 5 different SQL tables that these are reading from, and each table might have 5-10 tags assigned to it. This is a manual transfer of data from the SQL tables into OPC tags for process control.

I figure there has to be a better way of doing this because I occasionally get unresponsive moments on my client. I’m assuming this is because of the amount of querying that’s going on? I’m looking for a way to group these queries by SQL table and shrink my total system.db.runNamedQuery to a handful. I figure this would help speed up my data transfer and not cause client issues.

2

First issue, you are running this on the foreground thread, don't do that. Run it in an async thread.

Second issue, combine system.tag.writeBlocking and system.tag.readBlocking into as few calls to each as possible. You have 50 calls to writeBlocking in your script.

Additionally, look into creating a view in your database that combines all your step details into a single row per step. Query that from Ignition to get the data for configuration in a single NQ call.

Importance of SQL Views

7 Likes
3

Not sure about the queries, but reading and writing a bunch of tags like that individually is very inefficient. For example, just that first block of writes should be:

system.tag.writeBlocking([
	'[default]RECIPE/START LAYER',
	'[default]RECIPE/TRIG ABORT PROCESS',
	'[default]Internal/Trending/Stop Process Trends',
	'[default]Internal/TimeStamp2',
	'[default]RECIPE/TRIG PROCESS RUN NUMBER'], [
	layer,
	0,
	0,
	date,
	txt])
3 Likes
4

This is great advice. I'd take it a step further and say to move the logic into your project library as a single defined function. Then it becomes very easy to move execution of the recipe management from the running client (where it does not belong) to the gateway, by using system.util.sendMessage and a message handler on the gateway.

4 Likes
5

Consider running the recipe retrieval named query (preferably as Ryan describes) in a named query binding in your UI. Similarly, consider binding the tags you need to read into UI properties, perhaps with labels to display them.

This has two key advantages:

  • The user can review any of this material for which you choose to include display components in your UI, and

  • All content to be written is already present in the foreground (in component props) when you click your button, allowing you to construct a single system.tag.writeAsync() to send it all to the PLC. No UI hiccups ever. If you disable the button within its action script, and re-enable the button in a callback attached to the writeAsync(), you'll even have natural feedback of the completion of the write. (Perhaps with an error popup if any write status is not good.)

Using a callback avoids blocking the UI thread.

3 Likes
6

Also, consider making a dedicated popup/window to configure and then execute this process, instead of relying on system.gui.confirm and system.gui.inputField (Both of which will also block the foreground thread)

If you want to confirm the operator's intent, instead of a popup, use a toggle button to enable the button that will run the script. Have something to turn off this toggle button after a period of inactivity, to prevent someone from leaving the action button enabled by accident. Also make sure that the action button turns off this toggle as well.

2 Likes
7

From an initial look (and mild asumption on your table setup), you can probably combine all your Layer, Assist, and Mat queries into a single query, and combine all your P1 through P5 queries into another, for a grand total of two queries (as opposed to 39).

If you are able to share more details on your table structure or some examples of your queries, we may be able to help guide you on combining them.

1 Like
8

Hopefully this helps illustrate what is going on:

The ‘Layer’ data is basic single “SELECT” - Ex: LAYER THICKNESS

image
image1150×182 13.5 KB

These queries pull data from the SQL table into OPC tags for other functions:

image
image1362×461 105 KB

The ‘Mat’ data is the same:

image
image1121×106 8.25 KB

from a different table:

image
image1391×466 142 KB

and the assist is more of the same but on a different table

So 3 different sql tables here, pulling 5-10 variables each. I like your idea of condensing these by making a list and using that list to create one query per table, so 3 NQ’s for the above. I will also look into the async function.

Really appreciate the input from anyone. This will get me started in the right direction but anything else after seeing these tables is also great.

9

Looking into views now. I see your point!

10

You can use JOINs to join data from two tables as long as they contain columns that are related to each other. (such as MaterialName in the two tables you have shown). For exmaple, something along the lines of:

SELECT
	LayerNumber,
	ProcessName,
	userprocess.MaterialName,
	MaterialRate,
	MaterialThickness,
	MaterialPocket,
	MaterialSweep,
	MaterialCoDep,
	MaterialIonEnabled,
	MaterialAPC,
	MaterialPause,
	ProcessHeat,
	ProcessParameters,
	matAssist,
	matEBSource,
	matHighVoltage,
	matEmissionCurrent,
	matOxygen,
	matPocket,
	matSweep,
	matCrystal
FROM
	userprocess
WHERE
	ProcessName = :hmi_ActiveProcess
	AND layerNumber = :hmi_ActiveLayer
INNER JOIN(
	SELECT
		materialName,
		matAssist,
		matEBSource,
		matHighVoltage,
		matEmissionCurrent,
		matOxygen,
		matPocket,
		matSweep,
		matCrystal
	FROM
		usermaterials
) materials
ON materials.materialName = userprocess.materialname

Would give you the needed data from userprocess and usermaterials. Make sure you have indexes defined on the columns you will be using to join tables. You can also perform multiple joins in the same statement.

Looking more at your tables, namely the material table, what are matOxygen, matEmissionCurrent and matHighVoltage holding? Are they floating point numbers with a unit attached(ie 3.6kV)?

1 Like
11

I started refactoring, because I like refactoring, but I feel like there's no point.
I'm sure there's a better way.

I can help you rewrite this into something that won't make people want to hang themselves, but I just can't guess what I don't see.

So here are a few things that could do with some polishing:

  • early returns. I know some people don't like them. These people are wrong.
    Instead of nesting ifs, short-circuit the logic in the failing case.
    Example:
if some_condition:
    if some_other_condition:
        do_something()

can become

if not some_condition:
    return
if some_other_condition:
    do_something()

with your code:

	status = system.tag.readBlocking('[default]RECIPE/TRIG START PROCESS')[0].value

	if status == 1:
		system.gui.warningBox("Process is actively running.", "User Alert")
		return
	elif status != 0:
		system.gui.warningBox("Process is in an unknown state. Please contact maintenance.", "User Alert")
		return

	start_process = system.gui.confirm("Are you sure you want to start a new process run?", "User Acknowledgement")
	if not start_process:
		return

	start_layer = system.gui.inputBox("Enter starting layer number:", "")
	if start_layer is None:
		system.tag.writeBlocking(
			['[default]RECIPE/START LAYER', '[default]RECIPE/CURRENT LAYER'],
			1, start_layer
		)
		return

	txt = system.gui.inputBox("Enter run number:", "")
	if txt is None:
		system.tag.writeBlocking(
			['[default]RECIPE/START LAYER', '[default]RECIPE/CURRENT LAYER'],
			1, start_layer
		)
		return

Then you can write the big wall of code at the function's indentation level.
It's also easier to follow.

  • datatypes. It seems like a lot of your variables can only take 0 or 1. Use booleans instead.
  • scalar queries. You seem to be pulling a whole dataset, then extracting the value of the first row, first column. If your queries should only return one value, use scalar queries.
  • merge your queries. It seems like several queries use the same parameters. Maybe you can write just one query that pulls all the data you need instead ? edit: I believe that's what @ryan.white is hinting at.
  • batch tag operations. It's been explained already, but you should really try to make as few calls as possible to the system.tag.write/read functions. Fetch all the values, then write them all at once. It will make things more efficient, and A LOT easier to read. If you have a lot of values to write, you can even introduce a layer of abstraction to make things easier. ie:
tags = {
	'foo': {
		'tagpath': '[default]foo',
		'value': 42
	},
	'bar': {
		'tagpath': '[default]bar',
		'value': None
	},
	'baz': {
		'tagpath': '[default]baz',
		'value': 'Hello, World!'
	}
}
tags['bar']['value'] = 3.14

tagpaths = [tag['tagpath'] for tag in tags.values()]
values = [tag['value'] for tag in tags.values()]
system.tag.writeBlocking(tagpaths, values)

This may or may not be adequate to your particular use case, there are other ways. You could maintain 2 lists, appending as you go.
The point is: structure. Structure your code to make things easier.

  • structure, again. This code is all over the place. You're asking the user for layer, which you write to a tag, then you read that tag and put the value into hmi_ActiveLayer, and then re-write that same tag again... It's really confusing.
  • consistency. The variable naming is also all over the place. Pick one style and stick with it. You're even mixing different styles into one variable name !
    By convention, variable names are either snake_case or camelCase. PascalCase is for classes and FULLCAPS is for constants.

I think the main offender is the lack of structure. When reading this, it felt like the person who wrote it didn't have a plan, and just laid out things as they came, like they were going over a spreadsheet row by row and adding python lines to match. Take a step back, make sure you have a clear view of what you have and what you need to do with it, then make a plan.
Now, I understand that it's just a function, and you may not want to invest too much time rewriting it. But the fact you're there is a hint that at least some degree of refactoring would be a good investment.

7 Likes
12

Hi Ryan,

What are matOxygen, matEmissionCurrent and matHighVoltage holding?

Yes these are holding floating point numbers attached to OPC tags to control PLC outputs, like 3.6kV = 10ma, or 2.5sccm = 1VDC

13

That got me wondering what GPT and Claude would come up with if I asked them, since refactoring is one thing AI is potentially good for. Neither one seemed to understand the importance of batch tag read/writes, and came up with blocks like:

GPT:

hmi_ActiveMaterial = read_tag('[default]RECIPE/PROCESS/LAYER NAME')
assistTrue = read_tag('[default]RECIPE/PROCESS/LAYER ASSIST')
assistName = read_tag('[default]RECIPE/PROCESS/LAYER ASSIST NAME')

Claude:

def initialize_process_tags(layer_num, run_number):
    """Initialize process control tags."""
    write_tag(TAGS['start_layer'], layer_num)
    write_tag(TAGS['current_layer'], layer_num)
    write_tag(TAGS['abort'], 0)
    write_tag(TAGS['stop_trends'], 0)
    write_tag(TAGS['timestamp'], system.date.now())
    write_tag(TAGS['run_number'], run_number)
14

Tip: Warren, use the > prefix to quote something from another source, not bold. This results in,

What are matOxygen , matEmissionCurrent and matHighVoltage holding?

with the quotation bar on the left and a lightly shaded background. There's even a quotation button on the editor toolbar.

1 Like
15

You have to give them explicit directives, things like "Make sure to batch tag operations".
I've been working on the same project for almost a year now, half of it using Claude. It picked up on this kind of things and I don't even need to ask for it anymore. It copies the style of what's already there, so after 15k lines of code the output is pretty consistent.
Also, they're probably do a better job at refactoring if there was a clear logic. Structuring your code is even more important in the age of AI.

2 Likes
16

There is a ignition specifc trained chatgpt 4o that is ok-ish. I had it try to refactor down to as few read/writeBlocking calls as possible and to exit early when it can, it still loves f-strings no matter how much you ask it not to so this will not work but seems much more readable at least. Emojis in the comments too seem way too common with ai coding

try:
    # Early exit if already running
    status = system.tag.readBlocking(['[default]RECIPE/TRIG START PROCESS'])[0].value
    if status == 1:
        system.gui.warningBox("Process is actively running.", "User Alert")
        return

    # Get user input
    if not system.gui.confirm("Are you sure you want to start a new process run?", "User Acknowledgement"):
        return

    layer = system.gui.inputBox("Enter starting layer number:", "")
    if layer is None:
        return

    runNumber = system.gui.inputBox("Enter run number:", "")
    if runNumber is None:
        return

    now = system.date.now()

    # Read required input tags
    readTags = [
        '[default]Internal/ActiveProcessRecipe',
        '[default]Internal/PairedHeatRecipe'
    ]
    readValues = system.tag.readBlocking(readTags)
    hmi_ActiveProcess = readValues[0].value
    activeHeatRecipe = readValues[1].value

    # Named query parameters
    parameters = {"hmi_ActiveProcess": hmi_ActiveProcess, "hmi_ActiveLayer": layer}

    # DB reads - process layer values
    rowCount = system.db.runNamedQuery("PROCESS NQ/GET_ROWCOUNT", {"hmi_ActiveProcess": hmi_ActiveProcess})[0][0]
    layerName = system.db.runNamedQuery("PROCESS NQ/GET LAYER", parameters)[0][0]
    layerRate = system.db.runNamedQuery("PROCESS NQ/GET LAYER RATE", parameters)[0][0]
    layerThickness = system.db.runNamedQuery("PROCESS NQ/GET LAYER THICKNESS", parameters)[0][0]
    layerCoDep = system.db.runNamedQuery("PROCESS NQ/GET LAYER CODEP", parameters)[0][0]
    layerAssist = system.db.runNamedQuery("PROCESS NQ/GET LAYER ASSIST", parameters)[0][0]
    assistName = system.db.runNamedQuery("PROCESS NQ/GET LAYER ASSIST NAME", parameters)[0][0]
    layerAPC = system.db.runNamedQuery("PROCESS NQ/GET LAYER APC", parameters)[0][0]
    layerPause = system.db.runNamedQuery("PROCESS NQ/GET LAYER PAUSE", parameters)[0][0]

    # DB reads - material
    matParams = {"hmi_ActiveMaterial": layerName}
    matSource = system.db.runNamedQuery("PROCESS NQ/GET MAT SOURCE", matParams)[0][0]
    matPocket = system.db.runNamedQuery("PROCESS NQ/GET MAT POCKET", matParams)[0][0]
    matSweep = system.db.runNamedQuery("PROCESS NQ/GET MAT SWEEP", matParams)[0][0]
    matVoltage = system.db.runNamedQuery("PROCESS NQ/GET MAT VOLTAGE", matParams)[0][0]
    matOxygen = system.db.runNamedQuery("PROCESS NQ/GET MAT OXYGEN", matParams)[0][0]
    matXtal = system.db.runNamedQuery("PROCESS NQ/GET MAT XTAL", matParams)[0][0]

    # DB reads - assist (optional)
    assistValues = []
    if layerAssist == 1:
        assistParams = {"hmi_ActiveAssist": assistName}
        assistValues = [
            system.db.runNamedQuery("PROCESS NQ/GET ASSIST BIAS C", assistParams)[0][0],
            system.db.runNamedQuery("PROCESS NQ/GET ASSIST EMISSION C", assistParams)[0][0],
            system.db.runNamedQuery("PROCESS NQ/GET ASSIST DISCHARGE C", assistParams)[0][0],
            system.db.runNamedQuery("PROCESS NQ/GET ASSIST DISCHARGE V", assistParams)[0][0],
        ]

    # DB reads - heat steps
    heatParams = {"hmiActiveHeat": activeHeatRecipe}
    heatTags = []
    heatValues = []

    def addHeat(path, query):
        val = system.db.runNamedQuery(query, heatParams)[0][0]
        heatTags.append(path)
        heatValues.append(val)

    heatMap = {
        'P1': 'RAMP UP',
        'P2': 'PREPROCESS',
        'P3': 'PROCESS',
        'P4': 'POSTPROCESS',
        'P5': 'RAMP DOWN'
    }

    for step, label in heatMap.items():
        num = step[-1]
        addHeat(f'[default]RECIPE/HEAT/{step} {label} HOURS', f'PROCESS NQ/HEAT/STEP{num} HOURS POPULATE')
        addHeat(f'[default]RECIPE/HEAT/{step} {label} MINUTES', f'PROCESS NQ/HEAT/STEP{num} MINS POPULATE')
        addHeat(f'[default]RECIPE/HEAT/{step} {label} SETPOINT', f'PROCESS NQ/HEAT/STEP{num} TARGET POPULATE')
        stepVal = system.db.runNamedQuery(f'PROCESS NQ/HEAT/STEP{num} STEP POPULATE', heatParams)
        heatTags.append(f'[default]RECIPE/HEAT/{step} {label} STEP')
        heatValues.append(stepVal)

    # Final consolidated writeBlocking
    writeTags = [
        # Process tags
        '[default]RECIPE/START LAYER',
        '[default]RECIPE/CURRENT LAYER',
        '[default]RECIPE/TRIG ABORT PROCESS',
        '[default]Internal/Trending/Stop Process Trends',
        '[default]Internal/TimeStamp2',
        '[default]RECIPE/TRIG PROCESS RUN NUMBER',
        '[default]RECIPE/TRIG PROCESS NAME',
        '[default]RECIPE/PROCESS/ROW COUNT',
        '[default]RECIPE/PROCESS/LAYER NAME',
        '[default]RECIPE/PROCESS/LAYER RATE',
        '[default]RECIPE/PROCESS/LAYER THICKNESS',
        '[default]RECIPE/PROCESS/LAYER CODEP',
        '[default]RECIPE/PROCESS/LAYER ASSIST',
        '[default]RECIPE/PROCESS/LAYER ASSIST NAME',
        '[default]RECIPE/PROCESS/LAYER APC',
        '[default]RECIPE/PROCESS/LAYER PAUSE',

        # Material tags
        '[default]RECIPE/MATERIAL/SOURCE',
        '[default]RECIPE/MATERIAL/POCKET',
        '[default]RECIPE/MATERIAL/SWEEP',
        '[default]RECIPE/MATERIAL/VOLTAGE',
        '[default]RECIPE/MATERIAL/OXYGEN',
        '[default]RECIPE/MATERIAL/XTAL',
    ]

    writeValues = [
        layer, layer, 0, 0, now, runNumber,
        hmi_ActiveProcess, rowCount, layerName, layerRate, layerThickness,
        layerCoDep, layerAssist, assistName, layerAPC, layerPause,
        matSource, matPocket, matSweep, matVoltage, matOxygen, matXtal
    ]

    # Add assist tags if needed
    if layerAssist == 1:
        writeTags.extend([
            '[default]RECIPE/ASSIST/BIAS CURRENT',
            '[default]RECIPE/ASSIST/EMISSION CURRENT',
            '[default]RECIPE/ASSIST/DISCHARGE CURRENT',
            '[default]RECIPE/ASSIST/DISCHARGE VOLTAGE'
        ])
        writeValues.extend(assistValues)

    # Append heat tags
    writeTags.extend(heatTags)
    writeValues.extend(heatValues)

    # Final trigger
    writeTags.append('[default]RECIPE/TRIG START PROCESS')
    writeValues.append(1)

    # 🚀 Single tag write call
    system.tag.writeBlocking(writeTags, writeValues)

except Exception as e:
    system.gui.errorBox("Failed to initialize process:\n{}".format(str(e)), "Process Start Error")
1 Like
17

Late to the party here, apologies.

Something to consider here, for the view you create is to UNION the queries to present you a single table return with a pair for param and value.

SELECT param1, value from Table1 where xxxx = param1
UNION
SELECT param2, value from Table2 where xxxx = param2
UNION
SELECT param1, value from Table3 where xxxx = param1

etc,

You can then run a loop against the returned dataset to build your tag and data lists.

S