Suggested Values on two connected colums

Stefan_E · 07-13-2021 03:03 PM

Hey everyone,

I’m currentyl trying to convince my boss to switch over from a google sheet to appsheet. But to do that I would like to show him how it would make data entry easier and I keep struggeling with the Suggested Value.

We have hundreads of Material Numbers and around 20 Product types

I want to be able to either enter a Material number and autofill the Product or enter a Product and get a filterd List of matching Material numbers.

I got the filtered View for the working by using Suggested Values

for Material:

=IF(ISBLANK([Product]);Material[Number];Filter(Material, [Product]=[_ThisRow].[Product]))

for Product:

=IF(ISBLANK([Material]),Product[Name];FILTER(Material;[_ThisRow].[Material]=Material[Number]))

The Material one works fine but the Product one keeps screaming at me that it >> Cannot compare Ref with List in ([_ThisRow].[Material] = Material[Number]) and I really have no Idea what it wants from me…

I’m pretty new to using databases and a couple of days in to AppSheet so I would really apriciate any help.

If I did some formulas in clunky way please also correct me with those as I would love to improve myself

Greetings

Stefan

Steve

This:

IF(ISBLANK([Material]),Product[Name];FILTER(Material;[_ThisRow].[Material]=Material[Number]))

Should be this:

IF(ISBLANK([Material]),Product[Name];FILTER(Material;[_ThisRow].[Material]=[Number]))

Stefan_E

Hey @Steve,

I actually figured it out that far at night but it still isn’t quite correct
If I enter a Material Number it forces me to enter the same Material Number into Product

Picture 1
Picture2

I want it to give me the corresponding Product out of the table