Dropdown selection with QUERY function

[AlexShevyakov]

Hi,
One of my spreadsheets contains the following structure:
Two columns, A and B (see pic below)

Column A contain the parameter names
Column B contain the parameter values, which are populated when value B1 (dropdown) is selected by user.

When B1 is selected, cell B2 uses QUERY function to pull the data into the rest of the rows below in colum B.
This all work fine in Google sheet but I seem to have problem of bringing this functionality into AppSheet.
Any help would be apprecited.

[Lynn]

HI @AlexShevyakov Check out this link on basic App design.

help.appsheet.com

App Design 101

How to think about building your app

[AlexShevyakov]

Hi @Lynn!
Well, I did. Do you imply that my data structure is initially incorrect?

[Lynn]

Hi @AlexShevyakov Just looking at what you have there I would make the parameter names in col A column headers and have the data in column B in rows under the columns. Is there any reason that would not work for you?

[AlexShevyakov]

@Lynn You mean - make it horizontal, rather than vertical? The only parameter that a user changes (selects) is Vessel - where would you place this one then?

[LeventK]

@AlexShevyakov
As you are using an ARRAYFORMULA, it will populate the sheet content after the user selects B1 value in the dropdown and save the form. Without the B1 value recorded in the gSheet, your QUERY will be empty.

[AlexShevyakov]

@LeventK. No, actually

=TRANSPOSE(QUERY('$SelectData'!$A$2:$AN, "SELECT B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z WHERE A = '"&$B$1&"'", 0))

[LeventK]

@AlexShevyakov
I couldn’t understand the difference??

[AlexShevyakov]

@LeventK What I am asking is: do I need to replace QUERY with ARRAYFORMULA for the thing to work?

[LeventK]

@AlexShevyakov

AlexShevyakov:

When B1 is selected, cell B2 uses QUERY function to pull the data into the rest of the rows below in colum B.

How B1 is selected? From where? From the app? If that’s the latter, unless you don’t record the B1 value to the sheet with the app, your ARRAYFORMULA or QUERY won’t populate anything. Besides, you need to sync twice for letting the app to read the populated values on this sheet.

[AlexShevyakov]

@LeventK Ah I see - sorry.
No, at the moment B1 is selected in the google sheet by the user. The QUERY formula pulls data from another spreadheet and by use of TRANSPOSE populate information into the B column (B2:B).
When Building Appsheet app, I cannot create a dropdown to replicate the same functionality.

[LeventK]

@AlexShevyakov
Have you checked this page?

help.appsheet.com

Dependent Dropdown

How to make a dropdown with choices that depend on the value selected in another dropdown.