Enum List show Unused Items Only

[ZOCO_Unlimited]

Hello, I'm looking to find a way for my dropdown list to only show unused records only.

How would you write an expression that creates a list by Filtering the [TSheets ID] values to not include any values listed in Ticket Details[Ticket ID] ?

Filter, Lookup, Contains, Not ?

Ticket Details allows the user to select multiple Time Entries from the TSheets Table. I would like the dropdown to only show Time Entries that have not been selected before.

Enum List: Ticket Details[TSheets ID]  from TSheets Export[TSheets ID]

I tried List Subtraction in Suggested values but it didn't work T Sheets Export[Tsheets ID]}-{Ticket Details[Tsheets ID]

Tickets

[Suvrutt_Gurjar]

a

---

@ZOCO_Unlimited wrote:

({T Sheets Export[Tsheets ID]}-{Ticket Details[Tsheets ID]}).  

---

At least syntactically , the expression could be 

 T Sheets Export[Tsheets ID] -Ticket Details[Tsheets ID]

One does not need curly brackets is the list is made out of expressions based on functions.

[ZOCO_Unlimited]

Thank you, I corrected that part. It did have an affect on what is displayed but not the desired results..  

[TeeSee1]

It helps if you describe how it is not working. (ie desired results vs what you are actually getting.)

[ZOCO_Unlimited]

Desired results are to only show unused values. I've tried multiple formulas listed below but every time the list still reflects the values that have already been used.

Formulas Tried:

SELECT(T Sheets Export[Timesheet ID],NOT(CONTAINS([TSheets Id],T Sheets export[Timesheet ID])))

SELECT(T Sheets Export[Timesheet ID],NOT(IN([TSheets Id],T Sheets export[Timesheet ID])))

 T Sheets Export[Timesheet ID] -Ticket Details[Tsheets ID]

[TeeSee1]

---

@ZOCO_Unlimited wrote:

T Sheets Export[Timesheet ID] -Ticket Details[Tsheets ID]

---

This should work.

Any common factors among the id's that remain displayed?

What happens if you create two separate VCs, for T Sheets Export[Timesheet ID]  and Ticket Details[Tsheets ID] and create another for the difference between those two?

Need more analysis as to what specific values do not behave as expected.

[ZOCO_Unlimited]

Well, It is partially working now, not sure what all changed. It did not work with existing records but did with new records (which is fine). Here's where I'm at:

I have created another column in the Ticket Details Table, Tsheets ID Enum to further test what is going on..  (The Enum list is preferred if possible and the one I'm still having troubles with)

TSheets Id, type Ref, table: T Sheets Export, Suggested values: T Sheets Export[Timesheet ID] -Ticket Details[Tsheets ID]    😁 Results work as desired

Tsheets ID Enum, type EnumList, base type Ref, table T Sheets Export, Suggested values: T Sheets Export[Timesheet ID] -Ticket Details[Tsheets ID Enum]

Enum still allows chosen values to be selected... I have disabled Allow other values as well as  Auto-complete other values and get the same results. 

Per your suggestion, "What happens if you create two separate VCs, for T Sheets Export[Timesheet ID]  and Ticket Details[Tsheets ID] and create another for the difference between those two?"       

I'm struggling,   here are the results: 

Virtual Columns Created:

[T Sheets Export ID] , App formula: t sheets export[Timesheet ID]

[Ticket Details Tsheets ID], App formula: Ticket Details[Tsheets ID Enum]

[Difference in VCs], App formula: [T Sheets Export ID]-[Ticket Details Tsheets ID Enum])  

Getting stuck here, what do I choose for the EnumList to populate off of?   Column Tsheets ID Enum : EnumList, base type ref, ticket details,  Suggested Values: [Difference in VCs]    results in broken link list of ID's (not the label) however it is filtering out the used items.. so close!

[TeeSee1 · Accepted solution]

You need to be aware of the fact that table[enum list col] returns a list of lists.

So computations between a list of single values and a list of lists do not work.

try

 T Sheets Export[Timesheet ID]  - SPLIT(Ticket Details[Tsheets ID],",")

and see if it works.

[ZOCO_Unlimited]

I see what you're saying..  I wondered if it was an automatic feature that would split it out with an enum list or if it had to be in the formula. Low and behold, SUCCESS!  You figured it out! I can't thank you enough for your assistance with this, VERY helpful!  

T Sheets Export[Timesheet ID]  - SPLIT(Ticket Details[Tsheets ID Enum],",")  in suggested values did the trick!