Filter function workaround in appsheet?

[Krismar]

Hello

How to convert a filter() function from google sheet to appsheet?

I have been building an inventory management app. Currently I have "Database" as main sheet where all products and parts are listed. For each product I have separate sheet and the Filter() function pulls necessary data from database to the corresponding product sheet. All sheets including database have basically same build up (columns from left->right : ID, Product type, quantity, image etc). Although i cannot change the values in my app due to the filter formula. All data that is pulled with the filter formula, are automatically set as "read only" in the app. the biggest problem is that i can't change the quantities of the parts. My filter function looks like this: 

=FILTER(Database!A2:D,(Database!B2:B="Orthopedic")+(Database!B2:B="Springplunger")) 

I am using 2 conditions, if the "Product type" matches to the condition it gets pulled to the sheet, I need 2 conditions because some parts are necessary for multiple products.
I am really beginner at this, so if my whole build up is bad then all advices are welcome.                                        
Thanks in advance!

Edit: One workaround would be to get rid of "Database" sheet, which means that on the "product sheets" all the data is actually there, not pulled from somewhere else. In that case i can get rid of the sheet formula and the data turns from "read only" to "editable". Downside is that some parts belong to several products and if i use those parts and want to change the quantity then i have to do that on every sheet which is annoying, right now i have a script that overwrites the quantity in the database after editing on the product sheet, and the other product sheets get synced according to the number in database.

[Aurelien · Accepted solution]

Hi @Krismar 

This may help you:

FILTER() - AppSheet Help

FILTER(
  "Database",
  OR(
    [Product Type] = "Orthopedic",
    [Product Type] = "Springplunger"
  )
)

You may want to try a slice instead, though:

With this row filter condition: 

  OR(
    [Product Type] = "Orthopedic",
    [Product Type] = "Springplunger"
  )

For reference:

Slices: The Essentials - AppSheet Help

[Aurelien · Accepted solution]

I think you would like to use this, for a start:

Then, create a view based on this from this panel:

[Aurelien · Accepted solution]

Hi @Krismar 

This may help you:

FILTER() - AppSheet Help

FILTER(
  "Database",
  OR(
    [Product Type] = "Orthopedic",
    [Product Type] = "Springplunger"
  )
)

You may want to try a slice instead, though:

With this row filter condition: 

  OR(
    [Product Type] = "Orthopedic",
    [Product Type] = "Springplunger"
  )

For reference:

Slices: The Essentials - AppSheet Help

[Krismar]

Thank you for your quick response. I found the expression list but I am confused about where do i put that formula in appsheet? Do i have to insert the formula for every column under data->columns->formula ?

[Aurelien · Accepted solution]

I think you would like to use this, for a start:

Then, create a view based on this from this panel:

[Krismar]

Okay this slice thing seems to work in the first place.
Tomorrow i will play around with it and then I will report back how it goes.

Thank you a lot!

[Krismar]

Ok I think I got it working as it should. But...couple of things.

How can i display images? In slice settings i have all the columns included. Image is in a cell but does not get displayed. There is no "Main image" option in slice settings and i think the app does not understand it is an image?

If we forget about the images, it works basically, the slice filters all information I need, but the data does not sync between the table and the app. I mean at first all the data is correct, copied from database, but when I use the app and change some values then table does not know anything about that. Is it possible to make them both sync with each other?

[Aurelien · Accepted solution]

Slice is treated basically as another table.

About your image question, you may want to create à New view, and set the slice as the data source of this view 🙃

About your syncing question, if there is no red circle after à few seconds when you make a change, then that means the change has been applied to your datasource. 

[Krismar]

I did something wrong about the syncing at the first place, sorry, it syncs fine, amazing.

About the images, if i create a new view and set slice as data source, i basically just copy that table. Under view settings i cannot choose main image, app does not recognize any image i think. Maybe im using images incorrectly, maybe the app requires images as URLs not actual images in cells?

[Aurelien]

About image, can you make sure that in your table settings, the column you expect to be an image is set with type Image ?

[Krismar]

Ok yeah I am pretty lost sometimes. 
I had to set the column with type image as you said, but i had to do it in my "Database" table settings, not "Slice" or "View" settings. 

I think i got all working now, the data syncs perfectly and I dont even need a script anymore, only 1 mastersheet needed not hundreds of sheets. Just amazing.

Thank you for your superb effort Aurelien.

[Aurelien]

You are very welcome!