App Ref dropdown selection auto fills single value from same table; different column; same row?

[Dew]

Have a table dropdown_lists ; with a bunch of different drop down lists columns inside it.

Have a Column H which basically converts a nested list dropdown list into a single word.

dowpdown_lists Column B value cells make up a "dropdown list app user selects in form, from ref_dropdown  in main table.

Wish to pull the matching row value out of Column H and.....

how to add the H cell value from same row from dropdown selected into another user form field automatically???

_____________________________________________________________

I'm sure the answer is out there, however have spent days looking and confusion because so many different types of drop downs and many ways to do it. So many different examples in different orders and combinations. Looked at the continent country example but this doesn't really have to do with pulling a single value from another column. Looked at the State City School example but this is narrowing down to multiple value results.

Just want to pull a single value from a row based on a selected ref dropdown in the same table, same row.

To my understanding Ref is the best way to do a drop down and the most likely way it will transfer if moved to a database format in the future from a sheet?

At first we tried this with Formulas in the sheet but that info does not transfer into appsheet.

Really grateful if someone can help answer; did try to figure out on own, but the more I look the more confused I get.

Have rearranged ref parent child as this is confusing.

Have tried using valid_if , select, any select thisrow. 

The drop down in the form works, but not yet just the single value automatically.

An example would be

COLUMN B: Company_Department_Section_SubSection_Result

COLUMN H: And pulling our just the _Result which has already been put as Result in a matching row in the same table; but instead of using the word _result wish to another word which has already been defined in the H column.

[dbaum]

I don't follow clearly what you're trying to accomplish. It sounds like maybe you could use either the LOOKUP function or maybe just a dereference expression in the App formula property of a column.

[Dew]

Sorry was trying to put it in general terms everyone could use. I find the names of real tables and real columns make the references and expressions more confusing to follow.

We have the first part BOLD you select in the form in the drop down from

CATEGORY TABLE

A                                                                                          B

google_product_code                                 google_product_category
166              Apparel & Accessories
1604            Apparel & Accessories >Clothing
5322             Apparel & Accessories > Clothing >Activewear
182                Apparel & Accessories > Clothing > Baby & Toddler Clothing
5408            Apparel & Accessories > Clothing > Baby & Toddler Clothing > Baby & Toddler Bottoms
5424              Apparel & Accessories > Clothing > Baby & Toddler Clothing > Baby & Toddler Dresses

NEXT we have.............

CATEGORY TABLE

G                                                                                                        H

collection                                                                                  product_type
Apparel & Accessories
Clothing
Activewear                                                                               Activewear
Baby & Toddler Clothing                                                      Kids

Baby & Toddler Bottoms                                                      Kids Pants
Baby & Toddler Dresses                                                        Kids Dress

__________________________________________________________________

TO start with:

PRODUCT Table. Form google_product_category  which is a Ref

CATEGORY Table.  Using a google_product_category  Text Label and google_product_code Number for key

These two things give us a dropdown list in the APP USER FORM VIEW.

and AppSheet Automatically add a virtual column [Related PRODUCTs]  List in the CATEGORY TABLE 

REF_ROWS("PRODUCT", "google_product_category")  expression

When someone selects "Apparel & Accessories > Clothing > Baby & Toddler Clothing > Baby & Toddler Dresses"

From the Form View "CATEGORY" Drop Down App List Selection.

We wish to auto populate: "Kids Dress".  THE FORM VIEW aka spreedsheet PRODUCT TYPE aka [PRODUCT_TYPE] which is a column heading in our PRODUCT TABLE from a same row cell from a prefilled column H [product_type] in our CATEGORY TABLE.

Example: Kids Dress

We wish to repeat this for a column G [collection] and have it autofill from cell "Baby & Toddler Dresses"

All the rows match across our Table for what we wish to pull out, we were hoping we could set it up in this manner and automatically pull out the collection and type into the form automatically, once a user selects the long google_product_category

[WillowMobileSys · Accepted solution]

Didn't quite understand what it is you are trying to do, so let me explain how to use a Ref column, its dropdown and get access to other row columns.

A Ref column is simply a "pointer" to a ROW in a table.  Could be same table but usually is in another table.  The "pointer" value MUST MUST MUST always be the row key of the referenced table.

When creating the dropdown for a Ref column, the list values MUST be a list of row keys from the referenced table.  So any expression you supply to generate the dropdown list MUST result in a set of row keys.

However, the row keys may not be the desired visible value - especially if they are the preferred random generated values.  AppSheet has a feature that allows you to specify a "Label" column in each Table.  A single value column (or an image column + a value column) can be marked as the "Label".   NOTE: at times it may be that the dropdown label value needs to be several value columns together.  In these cases, you would create a dedicated column and set it as the "Label" column in the table.

AppSheet will automatically display the "Label" column value to the user in the dropdown.  When a dropdown choice is made, the row key value will be stored in the column when the row is Saved.

Once a dropdown Ref value has been selected, you can use the Ref column to access other row column values through "dereference" or what I like to call "dot" notation. 

For example, let's say I have an Order row where I reference the Customers table.  I choose a Customer and want to set the Phone number and the shipping address on the Order row.  I can use the following dereference expressions:

[Customer].[Phone]

[Customer].[Address]

I hope this helps! 

[Dew]

I put set the initial value to 

COLLECTION=[google_product_category].[collection]

but I get the value "false" after selecting the category

how do I get the value from the H column?

How do you reference a reference?

Products have a category, from the category I wish to autofill the collection and type.

Dropdown choice has already been made.

How do I connect the dropdown choice to the [Product].[Category].[Type] cell value or [Product].[Category].[Collection] value so it auto populates.

[Dew]

YEA!!! Thank you so much!

For defining how to drill the well and not give me the water.

I had the key as the code and the label as the category list. I though I wanted to use the category code number as you can clearly understand it is unique but wanted a label to show a real word readable value.

Turns out, I think your 100% right! The CATEGORY KEY had to be the category list and label, NOT THE CODE NUMBER and name label.

Secondly, Turns out I was making it WAY more complicated than need be.

Since the formula is defining what " initial value is = " , I don't need that part.

just in the PRODUCT TABLE, set the INITIAL VALUE to

[google_product_category].[product_type]

and

[google_product_category].[collection]

for the other auto fill. What is great about this method is it fills in the words before you even refresh!

I easily confused myself and though it needed to be setup in the CATEGORY TABLE.

I guess you make the REF once for the TABLE you do not need to do it multiple times?

Anyway, guessed my way through this with trial and error.

The biggest problems was thinking I needed a "valid if" or "select _Thisrow" , all the other examples I saw were way more complicated. 

Even in your examples I got confused. Basically remove the Phone= and Shipping Address= in your examples.

I still am not comprehending this very well, but at least it is working the way I was hoping. Thank you for your responses!! giving me hope and guessing to an answer!

[Dew]

We want to use "TYPE" as part of our "TITLE" using concatenate.

So not sure if this matters how you setup the former in a way that it works with this later function.

We also wish to move the sheet to the database eventually, so would like setup correctly to eventually transfer without additional work.

I know that moving formulas to appsheet do not work so wish to setup in a way that hopfully will move over correctly to appsheet database without having to re setup the Enum and Ref all over again. 

Tried to work from the database backwards but it was even more difficult for me to understand and seems less flexible.