Conditional Lookup

[Edgar_Hernandez]

Hey guys, I’m having some real trouble trying to figure out how to do a conditional lookup.

As you can see in the image, I have two tables and one select list. What I want to achieve is an automatic fill of the item store and price, based on the ‘preferred store’ that was selected. That part I have managed to do,
However, what I am trying to achieve is…
1- Have the user select the preferred store where they want to purchase items from.
2- Items inside of Shopping List will then be looked-up in Item List.
3- Inside Shopping List, the Store column will be filled in automatically based on the preferred store.
4- Inside Shopping List, the Price column will be filled in automatically based on the preferred store.
5- CRITICAL: If the item inside [Shopping List].Item does NOT have a corresponding item inside [Shopping List] that matches the preferred store, it should automatically DEFAULT to ‘store A’, as if that was the original store selected from the start. In other words, if the preferred store doesn’t have the item, display the data from ‘store A’ (because that store will always have it).

Hope my explanation is clear, if not do let me know. Any help would be greatly appreciated since I have tried multiple things, like IF, IFBLANK, etc, to no avail

[Ander]

Not entirely clear on what you want, but maybe something like this?

Control name: storeName
Store column: =storeName
Price column: =[item list].Filter(store=storeName and item=thisRow.item).price

[Edgar_Hernandez]

Edited for clarity, hope it makes more sense now. Store & Price columns have not been filled inside Shopping List because they are meant to be filled in automatically based on a set of conditions, if-else.

[Ander]

This is hacky, using join() to “de-list” the list of rows returned. I’ve had to do this in my own project. It seems like there should be a better way to recognize an empty list as some value on which conditional logic can be set, but I don’t know how to do it.

Maybe @mallika an help us out here?

Control: Default Store = A, B, C
Control: Preferred Store = A, B, C

Table: Shopping List
Column: item:
Column: de-list: =join("",filter([item list],item=thisRow.item AND store=[Preferred Store]))
Column: store: =IfBlank([de-list],[Default Store] )
Column: price: =[item list].Filter(store=thisRow.store and item=thisRow.item).price

[Edgar_Hernandez]

So awesome, thank you very much! Totally agree that it seems like something that should be really simple to do, but ends up being extremely complicated (more than it should at the very least).