Search code examples
sqlpowerapps

PowerApps retrieving data from SQL using compound criteria

I feel as if I must be misunderstanding something basic, here. I admit I am a little baffled.

In PowerApps, I can generally get LOOKUP to work if I have a single item as part of my criteria.

But I am having the darndest time getting multiple criteria to work.

The TLDR version: How do I format a PowerApps command (I presume LOOKUP) using multiple items as criteria to give me a proper output.

My database table design: enter image description here

My database sample data: enter image description here

And the two different ways I've tried building my LOOKUP query (both of which produce blank results): enter image description here

My expectation was that the query would produce the answer "LS57CG954" (which is the only serial number sharing those two criteria).

How do I get this?

minor note 1: Although my sample table is small, the formal table will contain something on the order of 60,000 rows. There are about 13,000 unique Store_Number values, and for each Store_Number, there are from 0 to six different entries, based on Install_Position. There is never the case where two devices occupy the same position in the same store.

Solution

  • Okay, that took me way longer than expected, but this is what solved it without delegation warnings...

    Basically, I used the IN operator. I reasoned that maybe there were invisible characters in my database strings, or maybe there were weird tweaky ways in which strings were being handled between these two. Using IN bypassed a lot of those issues.

    Whatever the case, this is the construct that gives me exactly the data I'm looking for:

    Set(TextValue,LookUp(DTT_Monitors,"S00666" in Store_Number && "OSB" in Install_Position,Serial_Number));

    May it help you in the future!