Checking on SharePoint List to see if text field matches a Choice

[TCMcNeill]

Hi,

I am trying to setup a Power App that checks a SharePoint list to see if a stock is available to be traded.

A user in the Power App selects a dropdown of Stock Exchanges, and enters the ticker of the stock that they want to see if it is OK to trade.

If the ticker is not found, its ok to trade.

If the ticker is found, the exchange matches, and its Stock_Prohibited is false, its OK to trade.

If the ticker is found, the exchange matches, and its Stock_Prohibited is true, its NOT OK to trade.

When the user submits, it navigates to a second page. On that page if its OK to trade then display one message, and if its NOT OK to trade, display a different message.

The SharePoint list is called "Stock Check"

It has fields:

Stock_Exchange which is a Choice

Stock_Ticker which is a Single line of text

Stock_Prohibited which is a Yes/No

I have this SPL connected to my Power App.

A user to makes a selection from of a Dropdown of the exchange their stock resides:

And this is being populated by the SharePoint List

The user enters the stock ticket in the "TickerInput"

I want to be able to click on the "Verify your security" button, and it navigates to a second page.

I am unsure how to setup the formula on the button OnSelect, to show the relevant text label "ProhibitedSecurity" or "ApprovedSecurity".

I've seen references to using the Lookup function, but unsure how to implement it here.

This is what I have so far

Any help on making this work is appreciated.

Thanks,

TC

[WarrenBelz]

Hi @TCMcNeill ,

This will return either true or false depending on whether the stock is prohibited

UpdateContext{
   {
      varProhibited:  
      LookUp(
         'Stock Check’,
         Stock_Ticker = TickerInput.Text &&
         Stock_Exchange.Value = Stock_ExchangeDropdown.Selected.Value 
      ).Stock_Prohibited
   }
)

so your Visible on the buttons can be varProhibited and !varProhibited. Can you please also remember to post your code as Text.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[TCMcNeill]

Hi @WarrenBelz!

Thank you for the help!

This performed the request that I asked. But I don't think i was completely clear on the outcome that I was looking for.

I changed the code from UpdateContext to Set, so that I would be able to access the global variable from the second Screen.

Set(varProhibited, LookUp(
            'Stock Check',
            Stock_Ticker = TickerInput.Text && Stock_Exchange.Value = Stock_ExchangeDropdown.Selected.Value
        ).Stock_Prohibited
)

In testing this, it will correctly respond if the ticker matches, the exchange matches, and the Stock_Prohibited is true.

So I have a Company called ABC Inc. with a ticker ABC on the NASDAQ exchange.

I submit this information

And it correctly, responds as a prohibited stock

But if I submit the stock ABC for a different exchange like NYSE, which there isn't a record for.

 It will tell me it is not a prohibited security

Which is true, but isn't the exact functionality that I am looking for.

The conditions that I want to check are: if the stock is in the SharePoint List, and that stock has the matching exchange value, then we would check if it is approved:

So Power Apps would submit:

TickerInput: ABC

Stock_ExchangeDropdown: NYSE

SharePoint List:

Stock_Ticker: ABC

Stock_Exchange: NASDAQ

Stock_Prohibited: Yes

Power Apps Value	SharePoint Value	Evaluation
TickerInput: ABC	Stock_Ticker: ABC	match (true)
Stock_ExchangeDropdown: NYSE	Stock_Exchange: NASDAQ	no match (false)

So if the ticker is found, but the exchange isn't found in that record, then the request should be prohibited.

Any idea on how I could accomplish this?

Thanks,

TC

[WarrenBelz · Accepted solution]

Hi @TCMcNeill ,

There are a couple of ways of doing this - I cannot test this, but it should work

With(
   {
      wStock:
      LookUp(
         'Stock Check’,
         Stock_Ticker = TickerInput.Text &&
         Stock_Exchange.Value = Stock_ExchangeDropdown.Selected.Value 
      ).Stock_Prohibited
   },
   Set(
      varProhibited,
      IsBlank(wStock) || wStock
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[TCMcNeill]

Thank you @WarrenBelz !

This worked for me.