List Operation

[John_Henry_Jose]

I have Items Master and I have Issuance Table, where the items are issued, as child.

When I select a particular Item saved in child table, again when I select the items, i should not see the item which was selected already.

I used the below code but it does not give me the correct result. Any help please.

SELECT(Tbl_LGMaster[SerialNo],AND([xStatus] = "Not Assigned", [TpDueDate]>= NOW())) -
SELECT(Tbl_TranMaster[SerialNo],
AND(
[TranBigID] = [_THISROW].[TranBigID], [_RowNumber] <> [_THISROW].[_RowNumber]))

@WillowMobileSys @Steve 

[WillowMobileSys · Accepted solution]

---

@John_Henry_Jose wrote:

i should not see the item which was selected already

---

The phrase "selected already" could mean a few things.  I'll assume you mean to remove from the dropdown list items already selected in the other SIBLING rows - i.e. child rows of the this same parent.  I also assume the chosen item is captured on the Child row in a column named [Item] and it is a Ref column.

The concept here is to start with the original list then subtract those items chosen on the child rows.

Making additional assumptions about tables and column names, the expression needs to be something like this:

SELECT(Tbl_LGMaster[SerialNo],AND([xStatus] = "Not Assigned", [TpDueDate]>= NOW())) 
-
SELECT(Child Table[Item], [Parent] = [_THISROW].[Parent])
+
[_THIS]

You will need to replace the "Child Table", "Item" and "Parent" name references with actual names from your app. 

The "+ [_THIS]" part adds back to the list the current rows' selected value so it is considered valid for editing.  Otherwise, when editing the row later, it would get removed by the second SELECT and be considered invalid.

[John_Henry_Jose]

Thank you so much.

You saved my life again.

John