Appshet Formula

AppSheet AppSheet Q&A
1

Hello expertise

Anoyone knows how to convert this into appsheet formula below :

=QUERY(ARRAYFORMULA(IF(D:D<>“”,(((MATCH(G:G,G:G,0)))&“-”&(((MID(E:E,1,1))&(REGEXREPLACE(E:E,“[^[:digit:]]”, “”))))&“-”&F:F&“-”&G:G&“-”&H:H),“”)),“SELECT Col1 LABEL Col1 ‘LOCATION ID’”,1)

2

Hi @desmond_lee

You may want to give us the column names instead of columns letters.

As I understand your questino, your query expression would need to first a substitute/concatenate, then use a select expression.

  1. First step, you may want to add a virtual column with these pieces of expression:
IF(D:D<>"" 
==> IF(ISNOTBLANK([ColumnD])

(((MATCH(G:G,G:G,0)))&"-"&(((MID(E:E,1,1))&(REGEXREPLACE(E:E,"[^[:digit:]]", ""))) 
==> 
CONCATENATE(
  [ColumnG],"-",MID([ColumnE],1,1),
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE([ColumnE],"1",""),"2",""),"3",""),and so on...)
        )      )    )   )
)

&"-"&F:F&"-"&G:G&"-"&H:H 
==> CONCATENATE("-",[ColumnF],"-",[ColumnG],"-",[ColumnH])

a) Please note REGEX are currently not supported, hence the nested SUBSTITUTE to get rid of any number. The expression I provide assume you have only ONE figure in your column E. You may want to add your like here:

https://www.googlecloudcommunity.com/gc/Feature-Ideas/REGEX-expressions-and-an-optional-occurrence-parameter-for/idi-p/366052

b) Since you did not provide any context for your expression, I didn’t provide relevant information about the “MATCH” part, which I assume is an important component of your query.

  1. your Query expression:
FILTER("yourTableName",
  ISNOTBLANK([ColumnD])
)

You may want to detail further, in plain text, the result you are expecting if you don’t have enough information here.

I encourage you to read these articles:

structure oriented articles:

https://help.appsheet.com/en/articles/919891-virtual-columns

expression-oriented articles:

https://help.appsheet.com/en/articles/2357341-substitute

https://help.appsheet.com/en/articles/2435440-mid

https://help.appsheet.com/en/articles/2347638-concatenate

https://help.appsheet.com/en/articles/2357308-filter

https://help.appsheet.com/en/articles/2357314-select

https://help.appsheet.com/en/articles/2357267-isnotblank

4 Likes
3

Hello Expert

Here is the screenshot of the database. I cannot use vitual column because this is and ID and I need this to be appeare in my E-Maintainance App. This is a Location ID and this is unique to a location which means all the entrance whether is office, washroom will stick with a QR Code location. When a complainant complaints there is a leak all she need is to scan the Loc ID it will pops up the register substring WHICH shows only the items like pipe, water-tab and etc for her to select.

desmond_lee_0-1651806352506.png
desmond_lee_0-1651806352506.png1920×1080 272 KB

4

Hi @desmond_lee

I cannot use virtual column because this is an ID and I need this to be appeare in my E-Maintainance App.

I don’t get why you can’t use virtual column, though.

Which column is the key-column of this table ?

Can you also share a screenshot of your tickets table ?

1 Like
5

Hi Aurelien

I am really very new in this Appsheet and this is my first project in use Appsheet to develop an E-Maintenance system. To make thing easy below is the mockup up table. Just request permission I will give you full wrtie permission.

https://docs.google.com/spreadsheets/d/1-O0OqVnrQ-EN__glShLB8Mb_O2x3joRkrx7q-uRh7K4/edit#gid=229767795

6

From what I see, you should have:

-table for tickets, with as many columns as you need

  • one table per entity, such as location, items, tickets (you use 2 for your tickets: I think one is enough), category floor

  • your Ticket table should have columns that refers to all other tables: one for the Floor, one for the Location, and so on

The Ticket Table will be the one on which the users will add new items, from my point of view.

And you can restrict each column enum choice, using dependent dropdown with a FILTER() expression.

More information here: the first 3 articles:

https://help.appsheet.com/en/articles/961554-dependent-dropdown

https://help.appsheet.com/en/articles/961211-valid_if-column-constraint

https://help.appsheet.com/en/articles/961544-dropdown-from-valid_if

2 Likes
7

Hi Aurelien

Actually I am very new and down understand this formula below you provied. Is there a possible way to convert this formula directly to appsheet formula at the formula column wihtout extra step in creating vitual column?

=QUERY(ARRAYFORMULA(IF(D:D<>“”,(((MATCH(G:G,G:G,0)))&“-”&(((MID(E:E,1,1))&(REGEXREPLACE(E:E,“[^[:digit:]]”, “”))))&“-”&F:F&“-”&G:G&“-”&H:H),“”)),“SELECT Col1 LABEL Col1 ‘LOCATION ID’”,1)

8

I am referring to this column

Appsheet.png
Appsheet.png988×523 31.3 KB

9

Hi @desmond_lee

“Is there a possible way to convert this formula directly to appsheet formula at the formula column wihtout extra step in creating vitual column?”

Short answer is no.

Long answer is: you may want to have a look to data structure introduction here:

https://help.appsheet.com/en/articles/895267-data-the-essentials

What you have in Google Sheets with ARRAYFORMULA is implicit with AppSheet, because the field “Formula” you highlighted is calculated for each row in the table.

So, if in Google Sheets you have this expression:

=ARRAYFORMULA(A:A+B:B)

this is equivalent in Google AppSheet to creating a new virtual column and setting its expression as:

[ColumnA_header]+[ColumnB_header]

This is why you would need to use a virtual column. You don’t have to display it, but this is a necessary step: you need to break your expression down.

I encourage you to test this simple case to better understand.

Similarly, QUERY() is equivalent in AppSheet to FILTER(). You can also use a Slice that is a part of your table, filtered with a condition expression.

More information here:

https://help.appsheet.com/en/articles/2357308-filter

https://help.appsheet.com/en/articles/901432-slice-row-filter-conditions

1 Like