Working with the editor

Rui · 05-23-2020 09:19 AM

Hi, i´d appreciate some help.

my main worksheet (Dados) has, among others, these 2 columns:

Fabricante	Image
MSC	appsheet/msc2.jpg
MAERSK	appsheet/Maersk.jpg

I wanted to automatically fill in the image when we write a value in column Fabricante, as long as that value exists in the following worksheet (Fabricantes):

Nome	Imagem
MSC	appsheet/msc2.jpg
MAERSK	appsheet/Maersk.jpg
CMA	appsheet/CMA.jpg
HAMBURG SUD	appsheet/HAMBURG.jpg
EVERGREEN	appsheet/EVERGREEN.jpg

So, in [Image] i´ve set this array formula:
={“Image”;ARRAYFORMULA(IFERROR(VLOOKUP(B2:B;Fabricantes!B2:C;2;0)))},

Things work ok apparently and, in the app´s deck view, we can see the images as supposed to.

The problem occurs everytime we update a row because it is going to interfere with that formula which results in a #REF! error in the worksheet´s [Image] column and those images disappear.

I´m sure that formula is not the way to go and there must be a simple solution for experienced users like you.

Thanks

|

Suvrutt_Gurjar

I believe you may wish to mention if the two tables have any kind of referencing between them. Referencing in general makes getting the field values easier from one table to another , especially from parent to the child table. Of course there should be business and technical need to apply referencing between tables. You may wish to take a look at the below article,just in case you have not

You seem to be also using VLOOKUP() from spreadsheet.

In the absence of referencing, you may possibly have AppSheet 's LOOKUP() or SELECT() statements to extract values from one table to the other. However, please be aware of possible performance issues in case the inefficient SELECT() or any such multirow statements.

Based on information given by you , please explore if following expression helps you in app formula setting of Image column in Dados worksheet.

ANY(SELECT(Fabricantes[Imagem], [Nome]=[_THISROW].[Fabricante]))

Rui

Hi Gurjar, thanks a lot for the fast response! This community is as great as appsheet´s platform itself!

For better understanding, suppose you are just using a google sheet (forget appsheet for now) and every day you insert data in that sheet. For faster insertion (just for that purpose), as soon as you insert MSC in column [Fabricante], a value is inserted in column [Image]. In this case, that value is appsheet/msc2.jpg because it is the constant value defined in worksheet Fabricantes. Basically, the values in column [Images] are dependent from the values you insert in the previous column.

Without a script, I don´t know a way to use dependent dropdowns within a google sheet, so that array formula solves the problem and it works fine.

Now suppose you are distracted and by mistake insert a value (for example, MSC) directly in that dependent column [Image]… obviously you will a get a #REF error and all the values that you have inserted previously in that column, disappear. Ok no problem, you just cut MSC and put it in the right column and everything´s ok.

Now let´s add Apsheet to this scene.

There´s a manager everyday inserting data in the googlesheet and there are drivers everyday using appsheet to read that data and get their jobs done. So far so good, no problems as long as the drivers just read the data but, at some point, they must update a [Status] column.

Now i have problems because when a driver updates that [Status] column there is a “refresh” of the entire row in the googlesheet, so, not only that column is updated with the new value, as we wanted, but also the other columns are refreshed with their own values and at this exactly point i get that #REF error in that [Image] column because we can not insert vales directly there.

At this moment, in the app, the drivers can´t see those images anymore

As long as i use that array formula i´ll have problems, that is why i´ve asked if there was another way to set this.

Thanks