In outstanding Checks View I have a virtual column REF list (Text) using this expression. ORDERBY(SELECT(Product Serial Numbers In Stock.csv[_RowNumber],[_THISROW].[Product]=[ProductCode]),[SerialNumber])
This shows the correct list of Serial Numbers in a table in the detail view.
Then I’ve added them into suggested values using [Order By Serial Number][Serial & Warehouse]
This shows all serials numbers for all products in the Outstanding checks table, for some reason not filtering by product
I’ve tried the select directly in the Suggested Values, but that does the same thing. SELECT(Product Serial Numbers In Stock.csv[_RowNumber],[_THISROW].[Product]=[ProductCode])
You haven’t mentioned why you’re picking the extra [Serial & Warehouse] field. Since the [Serial & Warehouse] is what you want your users to actually pick then (I’m guessing) you probably what to return those and SORT() rather than ORDERBY(), e.g.
SORT(SELECT (Product Serial Numbers In Stock.csv[Serial & Warehouse],[_THISROW].[Product]=[ProductCode]))
That should give you a sorted list (sorted by [Serial & Warehouse]) to choose from without the need for 2 expressions. This may be not what you want but there’s no way for us to know as you didn’t say.
The issue will rise because the [Order By Serial Number] virtual column will require an app sync before its filtered list of values changes: if you need to see the filtering happen when you change the [Product] then you won’t as the virtual column will only change after an app sync; you need it in all one expression in the Suggested_Values and not via a virtual column.
My main problem initially was that the suggested values was showing all serial numbers, not just the ones for the product I was on.
That sort expression is helpful. As you say I don’t then need two expressions.
But it’s still not filtering properly by Product=Product Code.
It shows all serial numbers for the product, but also all serial numbers selected on another product.
So I am even more confused now.
Without seeing the rest of the app I’m not sure what to suggest. Is the [_THISROW].[Product] coing from a form you’re completing, or a record you’re looking at? I know it shouldn’t matter but I have a nagging voice in my head saying have you tried reversing the equals statement, i.e.
SORT(
SELECT(
Product Serial Numbers In Stock.csv[Serial & Warehouse],
[ProductCode] = [_THISROW].[Product]
)
)
[_THISROW].[Product] is a detail view I am editing, so the row is already created.
I’ve changed it round to [ProductCode] = [_THISROW].[Product], but same result.
Serial numbers for [_THISROW].[Product] plus serial numbers selected on other products.
Can I give any more information to help.
it just seems really weird to me. maybe a bug?
In the recording I go into each product to show the serial numbers, then go back in a select serial numbers.
Then back in again to show that both products show product serial numbers and selected other product serial numbers
Thanks for the screen recording, but you haven’t explained what selecting the serial numbers should do. You haven’t explained what the “Product Serial Numbers In Stock.csv” table is recording, so I don’t know what changes you’re expecting in there when you add the serial numbers to a product.
Since you are looking at a detail view I can see you have not let the app sync in between you altering one product and then editing another, so that may cause confusion too.
One other thing to think about is all you’re doing in this configuration is adding some text to an EnumList: you’re not relating the serial number record to the product. Are you supposed to be recording that this serial number is attached to the product? If so then this isn’t happening as you’re only adding the [Serial & Warehouse] text into the [Serial Numbers] EnumList@ you haven’t made any kind of a relationship between the two; if you need to do that then you need to record the key column.
From the original formula you propvided you were using [_RowNumber] as the key: this will cause various issues; have you not made your own key/unique id column in the “Product Serial Numbers In Stock.csv” table?
That makes sense: since they are simply text values you’re adding, not REF values, they will get cached as other values added and then appear wherever you used that field.
