Cross Reference tool in PowerApps

[ewchris_alaska]

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:

LO	LO Description	Event	Lesson	PPT Title	PPT Page #	Source Document	Section
40	Clearance	NH4	COM11	INDOC NH4	15	EHB	500
41	Communications	NH4	COM12	INDOC NH4	16	EHB	550
42	Performance	NH4	COM13	INDOC NH4	17	EHB	550
43	Flow	NH4	COM14	INDOC NH4	5	EHB	500
50	Organization	NH1	HR11	INDOC NH1	6	EHB	400
50	Organization	NH2	HR25	INDOC NH2	7	EHB	100
50	Organization	NH1	HR13	INDOC NH1	8	EHB	100
50	Organization	NH1	HR14	INDOC NH1	9	EHB	200
50	Organization	NH3	HR35	INDOC NH3	2	EHB	150
50	Organization	NH1	HR16	INDOC NH1	11	EHB	300
50	Organization	NH4	HR42	INDOC NH4	2	EHB	350
59	Finance	NH3	FTO11	INDOC NH3	3	HR Guide	Pay
59	Finance	NH3	FTO12	INDOC NH3	4	HR Guide	Benefits
65	Emergency Response	NH2	ECT11	INDOC NH2	8	S&S	3A
66	Safety Management Systems 101	NH2	ECT12	INDOC NH2	9	S&S	1A
67	Security and Privacy Awareness 101	NH2	ECT13	INDOC NH2	10	S&S	1B
99	Drug & Alcohol Policies	NH2	HR21	INDOC NH2	2	Guide to Drug & Alcohol Policies	2
99	Drug & Alcohol Policies	NH2	HR22	INDOC NH2	3	Guide to Drug & Alcohol Policies	3

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 / 5   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 / 350  Section

I appreciate any assistance.  thank you.

[CarlosFigueira]

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]

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 

[ewchris_alaska]

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