Link to sheet
Context: with an API call I am getting pricelist data from Airtable into Sheets. In Airtable tables are linked, hence I am seeing the primary key generated by Airtable as opposed to the actual field data. The tables where I can find that data can be found in separate tabs created with the API call.
Challenge: I am trying to create a price list with a dropdown field that generates relevant data once a model is selected. This query is used for that:
=UNIQUE(QUERY(models,"SELECT F, E, R, R*(1-0.15), R*0.15, R*1.07 WHERE J = '"&$A$1&"' AND (F = 'rec99pY85FrcpKWmh' OR F ='reccliFdf3cbYeADx') AND F <> '' LABEL R 'Retail excl. VAT', R*(1-0.15) 'Store Price', R*0.15 'Margin', R*1.07 'Retail incl. VAT' FORMAT R '฿ ###,##0', R*(1-0.15) '฿ ###,##0', R*0.15 '฿ ###,##0', R*1.07 '฿ ###,##0' ",1))
I need to replace the strings under material and size with the corresponding labels. I can do that with this formula:
=SUBSTITUTE(B3,B3,index(indexBodySizes,match(B3:B13,idSizes,0),6))
for sizes and
=SUBSTITUTE(A3,A3,index(indexBodyMaterials,match(A3:A13,idMaterials,0),1))
for materials.
Question: How can I combine the functionality of these individual sub-solutions into on query?
=ARRAYFORMULA({UNIQUE(QUERY(models,
"select F,E,R,R*(1-0.15),R*0.15,R*1.07
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''
label R 'Retail excl. VAT',R*(1-0.15)'Store Price',R*0.15'Margin',R*1.07'Retail incl. VAT'
format R '฿ ###,##0',R*(1-0.15) '฿ ###,##0',R*0.15 '฿ ###,##0',R*1.07 '฿ ###,##0'", 1)),
{"Sizes"; IFERROR(VLOOKUP(INDEX(UNIQUE(QUERY(models,
"select F,E,R,R*(1-0.15),R*0.15,R*1.07
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''
label R'',R*(1-0.15)'',R*0.15'',R*1.07''", 0)),,2), Sizes!A:G, 7, 0))},
{"Materials"; IFERROR(VLOOKUP(INDEX(UNIQUE(QUERY(models,
"select F,E,R,R*(1-0.15),R*0.15,R*1.07
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''
label R'',R*(1-0.15)'',R*0.15'',R*1.07''", 0)),,1), Materials!A:B, 2, 0))}})
=ARRAYFORMULA({{"Material"; IFERROR(VLOOKUP(INDEX(UNIQUE(QUERY(models,
"select F,E
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''", 0)),,1), Materials!A:B, 2, 0))},
{"Size"; IFERROR(VLOOKUP(INDEX(UNIQUE(QUERY(models,
"select F,E
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''", 0)),,2), Sizes!A:G, 7, 0))},
UNIQUE(QUERY(models,
"select R,R*(1-0.15),R*0.15,R*1.07
where J = '"&$A$1&"'
and (F = 'rec99pY85FrcpKWmh'
or F = 'reccliFdf3cbYeADx')
and F <> ''
label R 'Retail excl. VAT',R*(1-0.15)'Store Price',R*0.15'Margin',R*1.07'Retail incl. VAT'
format R '฿ ###,##0',R*(1-0.15) '฿ ###,##0',R*0.15 '฿ ###,##0',R*1.07 '฿ ###,##0'", 1))})