cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TCMcNeill
Frequent Visitor

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

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"

TCMcNeill_7-1656019442718.png

 

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:

TCMcNeill_0-1656017999563.png

And this is being populated by the SharePoint List

 

TCMcNeill_1-1656018056849.png

 

TCMcNeill_2-1656018081545.png

 

The user enters the stock ticket in the "TickerInput"

TCMcNeill_3-1656018123557.png

 

TCMcNeill_4-1656018164318.png

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

 

TCMcNeill_5-1656019184015.png

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

TCMcNeill_8-1656019589856.png

TCMcNeill_9-1656019626169.png

 

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

 

This is what I have so far

TCMcNeill_6-1656019389047.png

 

Any help on making this work is appreciated.

 

Thanks,

TC

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

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

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.

TCMcNeill_1-1656080910295.png

I submit this information

 

TCMcNeill_0-1656080779982.png

And it correctly, responds as a prohibited stock

 

TCMcNeill_2-1656081072031.png

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

TCMcNeill_3-1656081163686.png

 

 It will tell me it is not a prohibited security

TCMcNeill_4-1656081224100.png

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:

TCMcNeill_0-1656087062692.png

So Power Apps would submit:

 

TickerInput: ABC

Stock_ExchangeDropdown: NYSE

 

SharePoint List:

Stock_Ticker: ABC

Stock_Exchange: NASDAQ

Stock_Prohibited: Yes

 

Power Apps ValueSharePoint ValueEvaluation
TickerInput: ABCStock_Ticker: ABCmatch (true)
Stock_ExchangeDropdown: NYSEStock_Exchange: NASDAQno 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

 

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

Thank you @WarrenBelz !

 

This worked for me.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,665)