How to suggest a value based on condition

[bethany_d]

I'm aiming to show only to a dropdown 'Taken from Unit #' the first batch  just as long as its not yet used up. , so I made a sample below ie Batch 1 with remaining 75 units, and Batch 2 with Remaining 80 units.

So my idea is on this dropdown currently I use Ref for this, but what I'm hoping to achieve is that it WILL NOT SHOW ALL THE LIST OF Batches, but rather Only show 1 batch with enough units, or just as long as the units hasn't ran out yet. 

Is this possible? Any help is appreciated.

[Suvrutt_Gurjar · Accepted solution]

In general you can evaluate using valid_if in the "Taken from Unit #" column to narrow down your selection.

1) For showing units greater than  your valid_if could have a SELECT() statement where remaining is greater than 0

2) You could add another Enum column called "Batches" where the user can first select a batch and then you can narrow down selections by using valid_if with an expression something like

SELECT( Ref Table[Key Column], [Batch No]=[_THISROW].[Batch No])

Drop-down from Valid_If - AppSheet Help

[bethany_d]

Thanks for your suggestion, I appreicate it, I was able to get the outcome that I wanted by adding Index() on my Select () as well 🙂