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

two Sharepoint List - LookUp & ForAll - conditional formating

Hi, 
There are two SharePoint tables: Tabel 1 & Table 2 (structured like on the photo below)
2tables.jpg

 

 

 

 

 

 

For Tabel_1 there is PO number 001. That number has two drawings attached. For that drawings are some characteristics to check and the result are presented in the separate Table2. 

 

I would like to have function to check for one PO number all drawing and corresponding check result. In case some value is missing the PO number is red. 

thank you in advance for you reply. 
 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Hi @lukmac ,

 

Could you tell me:

  • Do you want to display the data in the gallery?
  • Do you want the value of Drawing field in Table2 to be red when the value of checkresult field is blank, otherwise it is green?
  • Do you want to make the value of Drawing field red in Table1 when the following conditions are met at the same time?
    1. Exist a record that the value of Drawing field in Table2 = The value of Drawing field in Table1
    2. The value of the ‘CheckResult’ field of the record in condition1 is blank.           
  • Do you want to make the value of the PO field red when the following condition is met ?
    1. In the records with the same value of the PO field, exist a red value of Drawing field.

 

If so ,the key is the color property of the fields.

Ive made a test for your reference:

 

1\ Show my data in Table1 list and Table2 list

v-xiaochen-msft_0-1605685857984.png

 

v-xiaochen-msft_1-1605685857987.png

 

  // PO fields data type: Single line of text

 

2\ Add a gallery control gallery1 to screen and set its Items property to:

Table2

 

3\ Insert label control label1 into gallery1 and set its Text property to:

ThisItem.Drawing

 

4\ Set label1’ ’s color property to:

If(ThisItem.CheckResult=Blank(),Red,Green)

 

5\ Add a gallery control gallery2 to screen and set its Items property to:

Table1

 

6\ Insert label control label2 into gallery2 and set its Text property to:

ThisItem.Drawing

 

7\ Set label2’ ’s color property to:

If(

    CountRows(

        Filter(

            Filter(

                Table2,

                Drawing = ThisItem.Drawing

            ),

            CheckResult = Blank()

        )

    ) <> 0,

    Red,

    Green

)

 

8\ Insert label control label3 into gallery2 and set its Text property to:

ThisItem.PO

 

9\ Set label3’ ’s color property to:

If(

    Sum(

        ForAll(

            Distinct(

                Filter(

                    Table1,

                    PO = ThisItem.PO

                ),

                Drawing

            ),

            CountRows(

                Filter(

                    Table2,

                    Drawing = Result,

                    CheckResult = Blank()

                )

            )

        ),

        Value

    ) <> 0,

    Red,

    Green

)

 

10\ The result is as follows:

v-xiaochen-msft_2-1605685857989.jpeg

 

 

Best Regards,

Wearsky

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Hi @lukmac ,

 

Could you tell me:

  • Do you want to display the data in the gallery?
  • Do you want the value of Drawing field in Table2 to be red when the value of checkresult field is blank, otherwise it is green?
  • Do you want to make the value of Drawing field red in Table1 when the following conditions are met at the same time?
    1. Exist a record that the value of Drawing field in Table2 = The value of Drawing field in Table1
    2. The value of the ‘CheckResult’ field of the record in condition1 is blank.           
  • Do you want to make the value of the PO field red when the following condition is met ?
    1. In the records with the same value of the PO field, exist a red value of Drawing field.

 

If so ,the key is the color property of the fields.

Ive made a test for your reference:

 

1\ Show my data in Table1 list and Table2 list

v-xiaochen-msft_0-1605685857984.png

 

v-xiaochen-msft_1-1605685857987.png

 

  // PO fields data type: Single line of text

 

2\ Add a gallery control gallery1 to screen and set its Items property to:

Table2

 

3\ Insert label control label1 into gallery1 and set its Text property to:

ThisItem.Drawing

 

4\ Set label1’ ’s color property to:

If(ThisItem.CheckResult=Blank(),Red,Green)

 

5\ Add a gallery control gallery2 to screen and set its Items property to:

Table1

 

6\ Insert label control label2 into gallery2 and set its Text property to:

ThisItem.Drawing

 

7\ Set label2’ ’s color property to:

If(

    CountRows(

        Filter(

            Filter(

                Table2,

                Drawing = ThisItem.Drawing

            ),

            CheckResult = Blank()

        )

    ) <> 0,

    Red,

    Green

)

 

8\ Insert label control label3 into gallery2 and set its Text property to:

ThisItem.PO

 

9\ Set label3’ ’s color property to:

If(

    Sum(

        ForAll(

            Distinct(

                Filter(

                    Table1,

                    PO = ThisItem.PO

                ),

                Drawing

            ),

            CountRows(

                Filter(

                    Table2,

                    Drawing = Result,

                    CheckResult = Blank()

                )

            )

        ),

        Value

    ) <> 0,

    Red,

    Green

)

 

10\ The result is as follows:

v-xiaochen-msft_2-1605685857989.jpeg

 

 

Best Regards,

Wearsky

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (47,288)