How to get a column value from another table based on enum inputs from a different table

[haris6g]

A newbies to Appsheet here. I've been stuck in a seemingly simple task that I haven't been able to solve yet. My Sheet has two tables:

1. Quotations 

2. INVT

Quotations table has some user inputs from DROPDOWNs. See below:

I've made another table with prices of inverters  like this:

What I want to do is to get The related price from the INVT table in the quotations table based on Dropdown inputs of "Inverter Company" and "Inverter Rating". Lets say user selects "Huawei" and "5kW". I've already added a virtual column to concatenate the required string for column search like this: CONCATENATE(TEXT([Inverter Company]),"_",TEXT([Inverter Rating])). Now I need to get the value of this column from INVT table. A help would be much appreciated. Any help would be much appreciated!

Edit: I've already added the INVT table in my data. Still couldn't figure out how to perform this task from documentation.

[RainKun]

First, you should have a unique id column from INVT for identifiers with the same value on you concatenated VT column.

Second, using the result of your concatenated virtual column CONCATENATE(TEXT([Inverter Company]),"_",TEXT([Inverter Rating]))

Then lastly, use the Lookup or Select function to relate it from the INVT table and get the desired data.

[haris6g]

The Lookup or Select function has been the real problem for me. Before this Column structure I was using this structure: 

But was stuck on how to query the inputs to get those values.

(LOOKUP([Inverter Company],"INVT","Company",[Inverter Rating]))

Tries this with error saying that [Inverter Rating] should be a string.

or VIA select:

ANY(SELECT(INVT[[Inverter Rating]], ([Company] = [Inverter Company])))

it had other errors.

[RainKun · Accepted solution]

I prefer you should create a proper table like this. So that you can use the select or lookup function properly. Make sure that the Column ID is unique and no duplicates.

ID	Company	Watts?	Price
concat(company,watts?)	Sungrow	5kW	9800
Sungrow10kW	Sungrow	10kW	780000
Sungrow15kW	Sungrow	15kW	12000
Sungrow20kW	Sungrow	20kW	22000
Huawei5kW	Huawei	5kW	77881
Huawei10kW	Huawei	10kW	…
Huawei15kW	Huawei	15kW	…
Huawei20kW	Huawei	20kW	…
……	…	…	…

[Joseph_Seddik]

Welcome to the community!

Your data modelling is not correct. You should have your price list in rows not columns. Please read this guide carefully and follow the links inside:

Data: The Essentials | AppSheet Help Center 

You should be able then to setup your data structure correctly, otherwise you'll keep running into complications. Also, feel free to ask in case you face any difficulty with the documentation.

[haris6g]

Thanks for your quick reply. I had first added my price list in rows:

but when I added the table in my data it looks like this:

This seemingly creates a problem for me which means I cannot make a form from this as I can edit my prices from the app because it just has two rows to work with.

[RainKun]

You should put column header and dont make it number should be text in your 1st column.

[Joseph_Seddik]

You should have:

1. Companies table

2. Ratings table 

3. Pricing table, with two ref columns to Companies and Ratings

In the Pricing form you can select a company and a rating and set the price accordingly. You can also have a table view for your Pricing table with Quick Edit enabled so that you update prices directly on the table view without the need to open a form.