Dropdown not showing correct options -

[tom_smith]

My data model for a marking app is this. It's so tutors can create a bank of Criterias in a Rubric. Each Criteria has a Marks Band particular to that Rubric. Essentially this...

When I view a Rubric I display it's related Marksbands with a  REF_ROWS("Marks Bands", "Rubric") like this (and it gets the relevant Marks Bands)

But when I edit a Criteria , which is in the data model like this...

When I edit a Criteria I get ...

So the Edit contentsd seems to behaving differently from the View contents.

I tried adding a slice to Rubrics like this...

[Rubric] = [_THISROW].[Rubric]

... and then using that in Marks Band View... 

I tried adding a mixture of any of these all over the place...

FILTER("Marks Bands of this Rubric", ([Rubric] = [_THISROW].[Rubric]))

SELECT(Marks Bands[Rubric], [_THISROW].[Rubric] = [Rubric])

FILTER(
"Marks Bands",
AND(
([Id] = [_THISROW].[Rubric]),

)
)

ANY(
SELECT(
Marks Bands[Rubric],
([Id] = ([_THISROW].[Rubric]))
)
)

[Rubric] = [_THISROW].[Rubric]

[Rubric].[Section].[Rubric]= [_THISROW].[Rubric]

... such as the System Generated Criteria_Form .... for Marks Bands col within that.... I tried a variety of VALID_IF bits (but it seems that those are for AFTER one has selected a value, not the list from which one selects, right?)

And I can't get the drop down to offer only Marks Bands items that have the same Rubric as the Criteria that is being edited.

Any help understanding what goes where gratefully received.

Tom

[WillowMobileSys]

In a Form view used for entering OR editing a row, the list of possible values that can be chosen for a column are controlled by the Valid If property within THAT column.  An expression is inserted to tailor the dropdown list based on criteria.  In your case,  The Marks Band column needs an expression in the Valid IF property to control what values are displayed.

UPDATED:  Reviewing your data structure and if I understand the structure, it seems that Rubric is the Parent table. So, I think the expression you need in the Marks Band - Valid if property is simply :  [THISROW].[Rubric].[Related Marks Bands]

In the example below, a list of Tasks specific to each Client are shown.  But there a some Tasks that apply to ALL Clients and are identified by the Client column being blank.  The 2 Lists are combined using the "+" operator.  The combined list is then Ordered by Priority so a user always sees in the dropdown list the Tasks being worked on at the top of the list.

I hope this helps!

[tom_smith · Accepted solution]

Thank you @WillowMobileSys ... that's fixed it.  Brilliant...

I think I'm struggling with the expression language, and how similar it is/different from sql, spreadsheet formulas/code.  For example, to me, it'd seem "logical" for REF_ROWS() to have extra params for ordering and maybe limits etc. 

It'd be handy to have a simple data collection that contained related tables, many-to-many, actions etc and show how they would be connected in sql, and then how that maps onto AppSheet expressions. For example, ORDERBY comes first not last 🙂 

Another thing that is challenging AppSheet wise is that functional stuff can be all over the place. The logical place for this (to me) was to create a Data Slice, then connect that to the VIew (I couldn't get that to work), I thought it could be an INITIAL_VALUE but instead the solution was a VALID_IF.

I found this tutorial REALLY useful because it has a simple sheet to start from (with a bit of data in) and instructions written down that I can follow ("Now do this etc") - which is how I learned Apps Script. If anyone has similar resources like that I can work through I'd appreciate them (more than video tutorials, which often spend 4 mins introducing the topic, and the guests webinar style )

Anyway... fabulous stuff, thanks again.

Tom