I have an action designed to add a row to another table from table A to table B… but has the following conditions:
The status field in table A must be “Needs Attention”
There can’t already be a record with the same id from Table A in the orig_checklist_Id field of Table B
The record cannot be added to table B, if there’s already a record in the table containing the same checklist_item_id and cms_sites_id as the record in Table A
I’ve attempted the following expressions and It either doesn’t insert any records at all, inserts a few records but not all expected or inserts all records.
I suspect I’m overthinking this. Any help would be amazing. Thanks so much!
Option 1:
AND(
[_THISROW].[status]=“Needs Attention”,
NOT(
AND(
IN([_THISROW].[checklist_item_id],drop_checklist_activity[checklist_item_id]),
IN([_THISROW].[cms_sites_id],drop_checklist_activity[cms_sites_id]),
IN([_THISROW].[id],drop_checklist_activity[ori_checklist_id])
)
)
)
Option 2
AND(
[_THISROW].[status]=“Needs Attention”,
NOT(IN([_THISROW].[id],drop_checklist_activity[ori_checklist_id])),
ISBLANK(
SELECT(drop_checklist_activity[id],
AND(
[checklist_item_id]=[_THISROW].[checklist_item_id],
[cms_sites_id]=[_THISROW].[cms_sites_id]
)
)
)
)
I seem to recall having had issues with using [_THISROW] when not used in a SELECT statement. I am wondering if Option 1 would work with eliminating each occurence of ‘[_THISROW].’?
1 Like
Thanks Markus…trying it now…
Wanted to mention real quick, sometimes when using expressions to determine if an action should be performed, I will add a temporary virtual column with the same expression to see if it returns the result I am expecting. Once I tweaked the expression in the VC that it gives me consistent results, then I will implement in the action for further testing.
1 Like
Ma’am,
The action which you have created belongs which table ? A or B ?
If the action belongs to table B , then you can use [_thisrow].[xxx]. But
[_THISROW].[status]=“Needs Attention”
will change to
Table A ([ status], [_ this row]. [xxx- id ]) = “Needs Attention”
I am little confused here
Which is table A and Table b in below expression.
IN([_THISROW].[checklist_item_id],drop_checklist_activity[checklist_item_id]),
IN([_THISROW].[cms_sites_id],drop_checklist_activity[cms_sites_id]),
IN([_THISROW].[id],drop_checklist_activity[ori_checklist_id]
1 Like
Thanks so much @Markus_Malessa and @jaichith . The following is the final expression that seems to work successfully. Again thanks for reviewing the inquiry
AND(
[status]=“Needs Attention”,
NOT(IN([id],drop_checklist_activity[ori_checklist_id])),
ISBLANK(
ANY(SELECT(drop_checklist_activity[id],
AND(
[checklist_item_id]=[_THISROW].[checklist_item_id],
[cms_sites_id]=[_THISROW].[cms_sites_id]
)
))
)
)
1 Like