index and match

[Jonatan]

Hello,
thanks  in advance for your advises
I'm  newbye and my background is mechanical :), I'm tryng to make  an app for my lathe and basing on my existing excel i would like to clone the same functionality on appsheet.
In order to make the story short in excel i used this  formula
_____INDEX(Table1[#all];MATCH(1;(DATA!$A:$A=$F$15)*(DATA!$B:$B=$F$17)*(DATA!$C:$C=$F$18)*(DATA!$D:$D=$F$19);0);5)
Such formula check in a table  and match three words present in a cell in order to give a result .
First problem (Table1[#all), i'm not able to refer to a complete table even if i made it in my google sheet . This is what i wrote ____INDEX((DATA!A1:M427) but an error shown.
Second problem (MATCH)  the formula match doesn't exist in google sheet.

ops ......the match words shoul be a filled field of my form view , for example a enumlist selected.

how, I can wrote the complete formulata to satisfy my needs?
Thanks a lot

Jonatan

[dbaum]

INDEX ≠ INDEX = INDEX

Consider LOOKUP.

If you provide details regarding your AppSheet tables and columns and what you're trying to accomplish, likely someone can advise.

[Jonatan]

Hi dbaum, 

sorry, look the picture if it is enough, otherwise i don't know how i can share the table

This is my main database, i made a support table where i use three fields that call back the column B C D E and a virtual field where i would like to add the formula in order to visualize the result of column F.

Regards

Jon

[dbaum]

---

@Jonatan wrote:

i don't know how i can share the table

---

Just as you did with your spreadsheet table, you can always share a screenshot of an AppSheet table's list of columns.

---

@Jonatan wrote:

i made a support table where i use three fields that call back the column B C D E and a virtual field where i would like to add the formula in order to visualize the result of column F

---

I don't know the specifics of what you mean by this description. Potentially, you could use an expression similar to one of the following.

If your "support table" references your "main database" table, use a dereference expression:

[ID].[Velocità min tagl]

 Otherwise:

INDEX(SELECT([Velocità min tagl], AND([Diameter Type] = [_THISROW].[Diameter Type], [Activities] = [_THISROW].[Activities], [Material] = [_THISROW].[Material], [Tool Material] = [_THISROW].[Tool Material])), 1)

[Jonatan]

Hi Dbaun

i tried to add a virtual column called SPEED (  row 14) in  main database  "DATA" adding the above index formula without positive result,  i added (row 7) also the same  formula  in SUPPORT table in order to make a test but i misareable failed .

attached the two screeshot

[dbaum · Accepted solution]

Your goal remains unclear to me. In case it's helpful:

DATA table

If you need to return a row's value in the [Velocità min taglio] column elsewhere in a column of the same row, you can just do so directly:

[Velocità min taglio]

SUPPORT table

If you need to return a value from the DATA table's [Velocità min taglio] column from a row for which other columns' values match the SUPPORT table's row, you should be able to use a version of the INDEX-based expression that I drafted for you. I see that my draft omitted the SELECT function's required reference to the DATA table. Following is an updated draft. When you need to troubleshoot expression syntax, the AppSheet function list - AppSheet Help is a helpful resource.

INDEX(SELECT(DATA[Velocità min taglio], AND([Diameter Type] = [_THISROW].[Diameter Type], [Activities] = [_THISROW].[Activities], [Material] = [_THISROW].[Material], [Tool Material] = [_THISROW].[Tool Material])), 1) 

As before, I suggest you review the AppSheet help articles I linked to about table references and dereference expressions. Based on the information you've shares, it's very possible that those powerful techniques are applicable to your app.

[Jonatan]

---

@dbaum wrote:

Your goal remains unclear to me. In case it's helpful:

---

see this snap, now it works and it is what i wanted,, thanks !

the only problem is that this field appears, only after enter the values on the above fields,  very strange. Is preferable to have the filed blank.

The formula has been wrote on  new  item called suggested speed onside the main table DATA and not on support, that i removed it.

Thanks for your precious support.

Jonatan 

[dbaum]

It sounds like you want a user to be able to enter known values for a few columns and then receive the corresponding row's value from the [Velocità min taglio] column. There are ways to do this that are more standard and robust than the approach you've landed on of having the user (at least begin to) create a new row in the DATA table.

Search and filter

Enable the app's "search button", enable the "Search?" property for each column that the user might need to use in searching, and create a view that includes the columns a user needs to see. Then, the user can select the search button, and within the search input box select the filter button to then see the specific columns for which they can select values to filter the view.

Interactive dashboard

Create a dashboard view that includes a view for the user to select values and a view that shows information from the DATA table. Enable interactive mode.