Solved: Re: Problem getting value from another table

Johny_Freitas · 10-18-2019 05:56 PM

Hi
i have to tables: visao and lojasemail

on table visao i have a virtual column “problemas lojas” type list with app formula(SELECT(visao[concatenar lojas], (AND([solucao] = “Avisar Loja por email”,[status]=“aberto”))))

it works fine.

but on table lojas email
i have a column “lojas” type longtext app formula( visao[problemas lojas])
to get the value from table visao - virtual column problemas lojas.

that way it’s getting the value but are duplicating the values, there are only 6 values (its in looping):

how can i fix it?

on the table visao - apear only 6 values.

Johny_Freitas

hey guys, i do it to work.

on table visao - left virtual column:

concatenar lojas - appformula =concatenate(“RCC”,[protocolo]," - “,[estacao],” - “,[maquina],” - “,[problema],”
"
,)

and on table lojasemail
create another virtual column:

teste - appformula SELECT(visao[concatenar lojas], ([solucao] = “Avisar Loja por email”))

and set the column lojas to get and save on database the info from virtual column test

now it work

View solution in original post

Kirk_Masden

Is it the case that visao and lojasemail both have the same number of rows? And, for example, would you like the 3rd row on lojas email to show the value of problemas lojas that is on the 3rd row of visao?

If so, I think you will want to use a LOOKUP() expression:

pravse

If the virtual column in the first tableis of type list of X, then when you do visao[problemas lojas] you are asking for a list of list of X. That is why yo uare seeing the duplicates. That is probably not what you want.

First of all, in the first table, that formula is going to produce the exact same value for each row (a list of 6 rows). That is usually a bad idea (inefficient and not really appropriate for a virtual column). Usually one of the conditions of the SELECT filter will be [_THISROW].[Column] = [some other column]

Kirk_Masden

Thanks @praveen!

I can see now that this is the correct approach. @Johny_Freitas, please disregard what I wrote about Lookup(). The expression may be useful to you in other contexts but Select() with _THISROW is the better approach here.

Johny_Freitas

but in my case how could i use this select, i don’t understand.

how is my case:
table visao there is a lot of rows more than 20000 on database

in table “lojas” i want only the rows from table “visao” with the column [solucao] = “Avisar Loja por email” and column [status]=“aberto”

Steve

You have at least two problems. Let’s start here:

This expression will produce the same result for very row in the visao table. I suspect you don’t want the concatenar lojas column of each row to have exactly the same value. Can you explain in your own words (not as an AppSheet expression) what you want this column value to be for each row?

Johny_Freitas

my app is a field service, every day more than 50 lines are saved
this is an example line:

ticket trecho abertura encerramento aberto_por estacao maquina problema solucao status acao observacao foto tecnico add_prev_1 add_prev_2 abastecimento reabastecimento comprovante os_time sla_perto acessos gps Disponibilidade prioridade enviar_para_perto envio total protocolo

EjwUcNgK BARCAS 22/07/2019 17:30:34 Hugo Alves PRAÇA XV BRC-PXV-04 sem papel teste aberto -07/22/2019 17:30:34 johny -22.903898, -43.175440 Disponivel 1 RCC1

then i need get only 4 columns to send an email, these columns are:
([ticket]," - “,[estacao],” - “,[maquina],” - ",[problema])

so i did an virtual column (concatenar lojas) to concatenate then.

and another virtual column to get then if status is “aberto”.

but i need all rows that status is “aberto”

in one day sometimes i have 10 rows with this status. so with this formulas i will get all rows to store in another table (lojas email) and then i will use workflow to send emails automatic

Lynn

HI @Johny_Freitas have you considered using “Slices”?

Johny_Freitas

with slices i do it in the same table, but i need to put them in another table.

Kirk_Masden

A slice that is made from a table in your app will function like a separate table. In other words, you can build a new UX (view) from that slice.

Johny_Freitas

hey guys, i do it to work.

on table visao - left virtual column:

concatenar lojas - appformula =concatenate(“RCC”,[protocolo]," - “,[estacao],” - “,[maquina],” - “,[problema],”
"
,)

and on table lojasemail
create another virtual column:

teste - appformula SELECT(visao[concatenar lojas], ([solucao] = “Avisar Loja por email”))

and set the column lojas to get and save on database the info from virtual column test

now it work

Johny_Freitas

now, i’m thinking if is possible to do it like an enumlist to select what i want instead all

Kirk_Masden

That may work. I’m working on something similar (a slice based on an enum column) in my own app. Good luck!

Johny_Freitas

i can put it to work,
i use mysql database to store, so i create a new view direct in database concatenating what i want to use and works fine.

so i use the column as enumlist

but to send by email
i have to do a workaround to do a linebreak, becouse the normal use virgula(,) and stay side by side
so i created a virtual column to do this:

SUBSTITUTE([local], " , ", "
")

and send this virtual column by email.