Automatically select a dropdown value

[Jerry_Hill]

I’m stumped, I cannot find a way of accomplishing this: In a dropdown (ref) to select a value automatically, but still give the user the ability to select from a list. I’ll elaborate:

Table JLedger has a DD column titled Category which is populated from Categorys table. In my Entitys table I have a column T:Category (the T: indicates that it is a template). So, for the Category DD in JLedger I want the T:Category value selected automatically, but give the user the ability to select any of the other category values.

[LeventK]

You can form the DD content with Valid_If and then use a LOOKUP expression in the initial value property.

[Jerry_Hill]

I set Valid If to: Categorys[Name]

I set Initial Value to:

LOOKUP(

[_THISROW].[Entity].[T:Category],

“Categorys”,

“Name”,

“Name”

)

AppSheet accepts this formula, although it does give the warning that it could significantly impact sync time.

The problem is that It simply lists everything in the Categorys data set. If I replace the first arg (find-value) with “Personal Expenses” it works, but that won’t do. Any suggestions?

[Steve]

You cannot chain dereferences. This is valid:

[_THISROW].[Entity]

but this is not:

[_THISROW].[Entity].[T:Category]

[Jerry_Hill]

I changed it to:

LOOKUP(
[_THISROW].[Entity],
“Categorys”,
“Name”,
“Name”
)

It didn’t change its behavior, it still lists everything without selecting the (find-value).

[LeventK]

@Jerry_Hill
That’s because you’re calling an entire table column’s values to the dropdown via TableName[ColumnName]
If you want to filter the dropdown content values, you need to use a SELECT() expression explicitly

[Jerry_Hill]

With a select you get all or filtered, that is not what I want. I want all, but with one selected based on a value set in another table. Is that possible

[LeventK]

@Jerry_Hill
Have you tested the LOOKUP expression you have set? Is it returning the correct value? Also have you verified that the value returned by LOOKUP does exist in the list created by the Valid_If expression?

[Jerry_Hill]

Yes, for several hours now I have tested every combination I can think of and have made absolutely certain that the values are as expected.

If this expression in Initial Value works perfectly, i.e. it lists all in the DD with the “Refund” value selected.

LOOKUP(

“Refund”,

“Categorys”,

“Name”,

“Name”

)

But the aim is to have a value selected based on the Entity selected.

Using this expression:

LOOKUP(

[Entity].[T:Category],

“Categorys”,

“Name”,

“Name”

)

It gives me this error:

Error in expression ‘[_THISROW-1].[Entity].[T:Category]’ : Unable to find column ‘T:Category’

Using this expression:

LOOKUP(

[_THISROW].[Entity].[T:Category],

“Categorys”,

“Name”,

“Name”

)

It gives me no errors, only a sync time warning. It lists all in the DD but does not select the value in the Entitys table.

[LeventK]

Jerry_Hill:

It gives me this error:

Error in expression ‘[_THISROW-1].[Entity].[T:Category]’ : Unable to find column ‘T:Category’

Using this expression:

LOOKUP(

[_THISROW].[Entity].[T:Category],

“Categorys”,

“Name”,

“Name”

)

@Jerry_Hill

As @Steve had already mentioned above, you can’t chain de-refs. Therefore, the lookup parameter inside the LOOKUP expression shall either be [_THISROW].[Entity] or [_THISROW].[T:Category]. Have you tested with both of these lookup values? If so, what was the returned value? Was it blank or was it not blank but not the expected value?

If you are very much sure that your DD list content is correct and verified (including the list item that you want to pre-select as an initial value) but in the Form UX the initial value is not pre-selected, then the problem totally lies with the LOOKUP expression. May be you should try with ANY(SELECT()) or ANY(FILTER()) expressions instead where you can define more filtering criteria as opposed to LOOKUP().

[Jerry_Hill]

What I don’t understand is that when I chain derefs as illustrated it does not give me an error, but also doesn’t work as desired. When I use [Entity].[T:Category] it says it can’t find the T:Category column, as though it can’t use the Entity ref.

Please watch my video, it’s short but clearly illustrates my point.

https://www.awesomescreenshot.com/video/2073509?key=262d74606ed426788cc5530784f58ed8

[Jerry_Hill]

I’ve read through docs on ANY(SELECT()), ANY(FILTER()) they filter the list and do not select a value like Lookup() does. The only problem with Lookup() is figuring out how to supply it with a value from another table for the first arg (find-value).

[LeventK]

@Jerry_Hill
ANY(SELECT()) or ANY(FILTER)) returns a single item from a list and if used in the initial value and if the returned value is not blank and in the DD list, it will pre-select it no matter what.

[Jerry_Hill]

The problem is not whether ANY(…) works. ALL variations I’ve seen require supplying a known value. If a known value is supplied it will pre-select it no matter what. Agreed.

In my case, the value is not known in the current row, it has to come from another table. In every case I’ve seen or tried, AS won’t recognize the other table and/or column. If you like I can prepare a video that demonstrates this point.

[LeventK]

Jerry_Hill:

In my case, the value is not known in the current row, it has to come from another table. In every case I’ve seen or tried, AS won’t recognize the other table and/or column. If you like I can prepare a video that demonstrates this point.

@Jerry_Hill

Provided it won’t be so messy for you, appreciate a video so that I can try to understand the issue better. Also if you can share the app with levent@able3ventures.com as a co-author (Can edit app definition) and share the back-end gSheet as well with the same email with edit access, I can also look to your app build.

The [Category] column is a REF type column and you expect the REF list to be expanded with the value of the initial value, if that value is not existing in the REF list. Frankly this is out of reference logic and if a REF value is missing in the DD list, then you need to create that reference record actually with selecting New from the DD list. Even-if your LOOKUP, ANY(SELECT()) or ANY(FILTER()) return a value, when you save that record in the back-end, you will notice a yellow triangle in the detail view for that record denoting a mismatching ref as the record for that value in the reference table is basically non-existent.

[Jerry_Hill]

Ok, I’ll work on it and get back to you.

[Jerry_Hill]

Yahoooo!

While preparing the video I solved the issue and of course now that I’ve solved it, it seems so simple. This is the expression that works;

ANY(

SELECT(

Entitys[T:Category],

AND(

[T:TranType] = [_THISROW].[TranType],

[Key] = [_THISROW].[Entity]

)

)

)

Thanks again for your patience and willingness to go above and beyond.

[LeventK]

Thanks for the feedback @Jerry_Hill, glad to hear that you’ve solved the issue.
Post#10

LeventK:

If you are very much sure that your DD list content is correct and verified ( including the list item that you want to pre-select as an initial value ) but in the Form UX the initial value is not pre-selected, then the problem totally lies with the LOOKUP expression. May be you should try with ANY(SELECT()) or ANY(FILTER()) expressions instead where you can define more filtering criteria as opposed to LOOKUP().

[Jerry_Hill]

Here is a video that demonstrates my point:

https://www.awesomescreenshot.com/video/2073509?key=262d74606ed426788cc5530784f58ed8

[Jerry_Hill]

PS: I forgot to show the setting in Valif If: Categorys[Name]

[LeventK]

@Jerry_Hill
When you select an item from the [Entity] column is a REF column, referenced to Categories table. Basically you want to pre-select the value from Categories DD where thisrow [Entity] column matching the value of [T:Category] column from Entities table. [T:Category] column seems to be a ref as well, but I can’t see to which table it’s referenced, so I will improvise a bit

Change the LOOKUP with this and try (make you own adjustments if the table and column names are not properly matching)

ANY(
	SELECT(
		Entities[T:Category],
		AND(
			[T:TranType] = [_THISROW].[TranType],
			[Name] = [Entity].[Name]
		)
	)
)

[Jerry_Hill]

I tried several variations of ANY(SELECT()) including those at the bottom. The logic of this did not seem as though it would because in each variation it assumes that the value you want to select already exists in the current row. It doesn’t, it exists in another table, but I can’t find the way to work the expression to include the value from that other table.

ANY(

SELECT(

Entitys[T:Category],

AND(

[T:TranType] = [_THISROW].[TranType],

[T:Category] = [_THISROW].[Category]

)

)

)

ANY(

SELECT(

Entitys[T:Category],

[T:Category] = “Refund”

)

)

[LeventK]

@Jerry_Hill
Provided the initial value may not always exist in the DD list, then you need to change your Valid_if expression slightly and convert it to a list aggregation. Basically (I belive you can fill in the spaces as per your schema)

IF(
	ISBLANK(
		ANY(
			SELECT(....)
		)
	),
	Categorys[Name],
	Categorys[Name] + LIST(ANY(SELECT(...)))
)

You also need to use the same ANY(SELECT(…)) with the Initial Value as well, so that when it’s not blank and therefore aggregated to the DD list, it will be selected.

[Giuseppe_Nugara]

To solve it I used the formula ANY(FILTER(…)) in the initial value of the ref column.