Find all occurrences of a value in the same table from a different column

I have a relatively large table. In this table I have the following column definitions.

Table: WORK_ORDER_MGMT
ID || WORK_ORDER_ID || … || ADDONS
1 || 123456 || … ||
2 || 234567 || … || 123456
3 || 345678 || … ||
4 || 456789 || … || 123456

ID is the primary key for the row. Whilst having normalized tables would be desirable, I don’t have my that option because of legacy code.

How can I return a list of all “ADDONS” where the referenced WORK_ORDER_ID is being used?

I’ve tried to use [_THISROW] but am not getting any results.

My expression thus far that I’ve applied to a virtual column on WORK_ORDER_MGMT
SELECT(WORK_ORDER_MGMT[ID],([_THISROW].[WORK_ORDER_ID] = [ADDONS]))

Hi @squatch4014
Did you test your expression?

2 Likes

I sure did. My test is returning zero results.

Is the ADDONS column of type EnumList? If so, use IN() rather than =.

See also:

1 Like

No, ADDONS is a number field on the row.

Please post a screenshot of the table’s column list from Data >> Columns in the app editor, including at least the ADDONS and WORK_ORDER_ID columns.