I am trying to create a standard process library where processes are organized into “modules”. A module is a collection of process layers, a layer is a collection of process blocks, and a block is a collection of process steps. Steps, blocks, and layers are re-usable, so the one-to-many relationships are managed in tables called; layers_in_modules, blocks_in_layers, and steps_in_blocks.
I am trying to show ALL the steps in a process module in the module details UX by creating a virtual column with this expression, but it doesn’t work (returns no results):
SELECT(
steps[step_id],
AND(
IN([_thisrow].[mod_id], layers_in_modules[mod_id]),
IN(layers[layer_id], layers_in_modules[layer_id]),
IN(layers[layer_id], blocks_in_layers[layer_id]),
IN(blocks[block_id], blocks_in_layer[block_id]),
IN(blocks[block_id], steps_in_blocks[block_id]),
IN(steps[step_id], steps_in_blocks[step_id])
))
If this were an SQL query it would look like this.
SELECT
modules.mod_name,
layers_in_modules.lim_order as layer_number,
layers.layer_name,
blocks_in_layers.bil_order as block_number,
blocks.block_name,
steps_in_blocks.sib_order as step_number,
steps.step_short_desc,
tools.tool_name
FROM
modules,
layers,
layers_in_modules,
blocks,
blocks_in_layers,
steps,
steps_in_blocks,
tools
WHERE
layers_in_modules.mod_id = modules.mod_id AND
layers_in_modules.layer_id = layers.layer_id AND
blocks_in_layers.layer_id = layers.layer_id AND
blocks_in_layers.block_id = blocks.block_id AND
steps_in_blocks.block_id = blocks.block_id AND
steps_in_blocks.step_id = steps.step_id AND
steps.tool_id = tools.tool_id
ORDER BY
layers_in_modules.lim_order,
blocks_in_layers.bil_order,
steps_in_blocks.sib_order;
