Solved: Ordering a dropdown that has valid_if

Pratyay_Rakshit · 04-19-2020 06:50 AM

I have a valid_if expression using select() that drops down a list from another spreadsheet.

What I want to do is sort the dropdown based on a column value, preferably the ‘date’.

I tried wrapping up the select expression with orderby() using the [Date] column, but I get shown a message that orderby doesn’t have the valid inputs.

I saw that an exact same problem was posted by some other person, the solution to which was suggested as:

Try generating a sorted list using the Suggested values property for the column.

which seemed to have worked for the seeker.

But I don’t know how to properly write the expression in the suggested values field.

The select expression in valid_if :

select(RMC Bill entry[Computed], and(([Which Company?]=[_THISROW].[RMC Supplier]), [Paid?]=“Unpaid”))

I tried using orderby(select(RMC Bill entry[Computed], and(([Which Company?]=[_THISROW].[RMC Supplier]), [Paid?]=“Unpaid”)), [Date]) to no avail.

The [Date] column is within the table RMC Bill entry.

Steve

If you have a virtual column (named Rows by Date, e.g.) with the needed rows ordered in the desired way:

ORDERBY(
  FILTER(
    "RMC BILL entry",
    AND(
      ([Which Company?] = [_THISROW].[RMC Supplier]), 
      ([Paid?] = “Unpaid”)
    )
  ),
    [Date]
)

Theoretically, you could get the desired-order list of column values using a bulk dereference:

[Rows by Date][Computed]

(Note there is no period between the two column references.)

I say “theoretically” because I don’t have enough experience with bulk dereferences to say with confidence how they behave, but I would expect this to work.

View solution in original post

WillowMobileSys

Is [Computed] the key column for the table? If so, this seems to be ok. What was wrong with it?

If [Computed] is not the row key, then replace it with the column that is.

Pratyay_Rakshit

[Computed] is not the key, its a virtual column which is the concatenation of a number of columns from the concerned table; just something that I NEED in the drop-down.

Replacing it with the key column will show the values from the key column in the dropdown, because effectively we are ‘Selecting’ the key column, isn’t it?

Pratyay_Rakshit

Oh this is in the suggested values field, let me try. Sorry.

WillowMobileSys

For the ORDERBY() to work, it MUST have the key columns in order to reference the row, retrieve the [Date] value an then apply the sorting.

If you are using the dropdown for column of “Ref” type, then in most cases you want the key value returned. Then you should be setting the Label column of that referenced table to be used as the display value.

Pratyay_Rakshit

The column is of ‘enumlist’ type, with a valid_if expression as the select expression that I mentioned.

WillowMobileSys

You can set an EnumList column as a “Ref” type and have all the normal Ref column functions applied.

Steve

You cannot sort a list of non-Ref values by anything but the values themselves.

Pratyay_Rakshit

In some other thread you suggested a similar solution seeker to try the ‘suggested values’ field. Is there any means of doing that?

Steve

I don’t recall suggesting such a thing. What thread are you referring to?

Pratyay_Rakshit

Wish I had saved the link. I cannot happen to find it now.

But nevertheless, is there a way to do it?

Steve

If you have a virtual column (named Rows by Date, e.g.) with the needed rows ordered in the desired way:

ORDERBY(
  FILTER(
    "RMC BILL entry",
    AND(
      ([Which Company?] = [_THISROW].[RMC Supplier]), 
      ([Paid?] = “Unpaid”)
    )
  ),
    [Date]
)

Theoretically, you could get the desired-order list of column values using a bulk dereference:

[Rows by Date][Computed]

(Note there is no period between the two column references.)

I say “theoretically” because I don’t have enough experience with bulk dereferences to say with confidence how they behave, but I would expect this to work.

Pratyay_Rakshit

This worked. You can count on your knowledge.

candicepelser

Hi Steve, All,

I have a similar problem to @Pratyay_Rakshit’s original question…

An existing ValidIf condition for a Ref field, that I would now like to change - so that the drop-down is sorted descending.

Anyone have a way to combine the ValidIf and the Orderby expressions?
Or do I also need a virtual column somewhere?

Here are the two statements in isolation (both of them work on their own).
Would be great if I could merge them into one:

Expression A - ValidIf condition that prevents multiple child records:
NOT(
IN([_THIS],
SELECT(dbo.nest_relocation[nest_id],
NOT(IN([nest_relocation_id],
LIST([_THISROW].[nest_relocation_id])
)
)
)
)
)

Expression B - The Orderby expression:
ORDERBY(dbo.turtle_nest[nest_id],[nest_id],TRUE)

Many thanks!

Steve

Try:

ORDERBY(
  SELECT(
    dbo.nest_relocation[nest_id],
    ([_THISROW].[nest_relocation_id] <> [nest_relocation_id])
  ),
  [nest_id],
    TRUE
)

candicepelser

Thanks a million @Steve - I had to adjust it slightly to work for all cases (namely: DO NOT allow more than one child entry per parent, but DO allow editing of existing child entry).

Here’s what finally worked:

ORDERBY(
SELECT(
dbo.turtle_nest[nest_id],
NOT(
IN([_THIS],
SELECT(dbo.nest_relocation[nest_id],
NOT(IN([nest_relocation_id],
LIST([_THISROW].[nest_relocation_id])
)
)
)
)
),
TRUE),
[nest_id],
TRUE
)

Steve

Well done!