cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Cross Reference tool in PowerApps

Hello everyone,

 

I have a list in SharePoint  with around 300 learning objectives (LO). The columns in the list are LO, LO Description, Event, Lesson, PPT Title, PPT Page#, Source Document, and Section

 

LOs are covered in multiple lessons and events, and events can cover multiple LOs, so there are over 1000 items in the list.

 

Here is a sample of the list:

LOLO DescriptionEventLessonPPT TitlePPT Page #Source DocumentSection
40ClearanceNH4COM11INDOC NH415EHB500
41CommunicationsNH4COM12INDOC NH416EHB550
42PerformanceNH4COM13INDOC NH417EHB550
43FlowNH4COM14INDOC NH45EHB500
50OrganizationNH1HR11INDOC NH16EHB400
50OrganizationNH2HR25INDOC NH27EHB100
50OrganizationNH1HR13INDOC NH18EHB100
50OrganizationNH1HR14INDOC NH19EHB200
50OrganizationNH3HR35INDOC NH32EHB150
50OrganizationNH1HR16INDOC NH111EHB300
50OrganizationNH4HR42INDOC NH42EHB350
59FinanceNH3FTO11INDOC NH33HR GuidePay
59FinanceNH3FTO12INDOC NH34HR GuideBenefits
65Emergency ResponseNH2ECT11INDOC NH28S&S3A
66Safety Management Systems 101NH2ECT12INDOC NH29S&S1A
67Security and Privacy Awareness 101NH2ECT13INDOC NH210S&S1B
99Drug & Alcohol PoliciesNH2HR21INDOC NH22Guide to Drug & Alcohol Policies2
99Drug & Alcohol PoliciesNH2HR22INDOC NH23Guide to Drug & Alcohol Policies3

 

I want to build a cross reference tool in PowerApps which will enable me to enter the training event, for instance "NH4", and have the tool list all of the LOs, lessons, and source documents associated with that event.  Or I want to enter the LO and find out which events, lessons, etc. cover that LO. See example below:

Below is a diagram of what I want to get to. Where, if the either the LO #, the Event, or the Lesson code is entered, the fields below would populate

 

 

______ Learning Obj  (LO)   _NH4______  Event      ______ Lesson 

 

40 LO /     Clearance      LO Description / NH4  Event / COM11 Lesson / INDOC NH4  PPT Title / 15  PPT Page # / EHB Source Document / 500  Section

41 LO / Communications LO Description / NH4  Event / COM12 Lesson / INDOC NH4  PPT Title / 16 PPT Page # / EHB Source Document / 550  Section

42 LO / Performance       LO Description / NH4  Event / COM13 Lesson / INDOC NH4  PPT Title / 17 PPT Page # / EHB Source Document / 500  Section

43 LO /       Flow              LO Description / NH4  Event / COM14 Lesson / INDOC NH4  PPT Title /  PPT Page # / EHB Source Document / 500  Section

50 LO / Organization        LO Description / NH4  Event / HR42    Lesson / INDOC NH4  PPT Title / 2   PPT Page # / EHB Source Document / 35 Section

 

I appreciate any assistance.  thank you.

3 REPLIES 3
CarlosFigueira
Power Apps
Power Apps

You can use a filter expression that will use the values that were entered in the text inputs, like the one below.

Filter(
    coll,
    txtLO.Text = "" Or LO = Value(txtLO.Text) Or LODescription = txtLO.Text,
    txtEvent.Text = "" Or Event = txtEvent.Text,
    txtLesson.Text = "" Or Lesson = txtLesson.Text)

You can use the attached app to see this formula being used.

v-xida-msft
Community Support
Community Support

Hi @ewchris_alaska,

 

How do you display your learning objectives within your app? Using Data table?

 

I agree with @ewchris_alaska's thought almost, I think the Filter function within PowerApps could achieve your needs.

 

I assume that you use a Data table control to display the learning objectives within your app, please set the Items property of the Data table control to following formula:

Filter(
       YourSPList,
       TextInput1.Text=Blank()||Lo=Value(TextInput1.Text),
       TextInput2.Text=Blank()||Event=TextInput2.Text,
       TextInput3.Text=Blank()||Lesson=TextInput3.Text
)

Note: The TextInput1, TextInput2 and TextInput3 represents the TextInput control, you could separately type your LO value, Event value and Lesson value within them.

 

In addition, you could also take a try with the following formula:

If(
IsBlank(TextInput1.Text)&&IsBlank(TextInput2.Text)&&IsBlank(TextInput3.Text),
'YourSPLIst',
!IsBlank(TextInput1.Text)&&IsBlank(TextInput2.Text)&&IsBlank(TextInput3.Text),
Filter('YourSPLIst',LO=Value(TextInput1.Text)),
IsBlank(TextInput1.Text)&&!IsBlank(TextInput2.Text)&&IsBlank(TextInput3.Text),
Filter('YourSPLIst',Event=TextInput2.Text),
IsBlank(TextInput1.Text)&&IsBlank(TextInput2.Text)&&!IsBlank(TextInput3.Text),
Filter('YourSPLIst',Lesson=TextInput3.Text),
!IsBlank(TextInput1.Text)&&!IsBlank(TextInput2.Text)&&IsBlank(TextInput3.Text),
Filter('YourSPLIst',LO=Value(TextInput1.Text),Event=TextInput2.Text),
!IsBlank(TextInput1.Text)&&IsBlank(TextInput2.Text)&&!IsBlank(TextInput3.Text),
Filter('YourSPLIst',LO=Value(TextInput1.Text),Lesson=TextInput3.Text),
IsBlank(TextInput1.Text)&&!IsBlank(TextInput2.Text)&&!IsBlank(TextInput3.Text),
Filter('YourSPLIst',Event=TextInput2.Text,Lesson=TextInput3.Text),
!IsBlank(TextInput1.Text)&&!IsBlank(TextInput2.Text)&&!IsBlank(TextInput3.Text),
Filter('YourSPLIst',LO=Value(TextInput1.Text),Event=TextInput2.Text,Lesson=TextInput3.Text)
)

Best regards,

Kris 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the help with this.  It is definitley a great place to start.  I really appreciate it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,430)