cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thansson
Helper I
Helper I

Conditional formatting based on several fields

HI

 

I have 2 SharePoint lists, SAP_Import and FlightList.

 

In SAP_Import each row have one checkbox (Yes,No).

 

I need to colour one field in Flightlist, if all selected checkboxes in SAP_Import is true.

 

There is serveral fields in SAP_Import connected to one field in FlightList.

 

The Column connecting selected fields in Flightlist and SAP_Import is named "Unik"

 

Can anyone help getting started?

 

Regards

 

Tony

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Thansson :

Whether the records displayed by these exceptions have no associated records in SAP_Import?

If so ,please try this formual:

 

If(
IsBlank(LookUp(SAP_Import,Unik=ThisItem.Unik And !checkbox))&& CountRows(Filter(SAP_Import,Unik=ThisItem.Unik))>=1,
Color.Green,
Color.White)

 

In addition,because many functions and operations that are not delegable are used, this formula cannot be delegated. If the number of your records is less than 2000, you can use this method to eliminate the impact (but the warning will not disappear):

1.JPG

Best Regards,

Bof

View solution in original post

4 REPLIES 4
v-bofeng-msft
Community Support
Community Support

Hi @Thansson :

Could you tell me:

  • What is the meaning of "colour one field in Flightlist"?Do you want to show the items in Flightlist in a gallery and modify the colors of the fields in the tiems according to the conditions?
  • What is  "Unik"? Is it a text column both in Filhtlist and SAP_Import?

If so ,I'v made a test for your reference:

1\My data source:

SAP_Import

1.JPG

checkbox-Yes/No

Unik-Single line of text

FlightList

2.JPG

Unik-Single line of test

2\Add a blank vertical gallery and set it's items property to 

FlightList

3\Add a label control into this gallery 

Text

ThisItem.Title

Color

If(
  IsBlank(
    LookUp(SAP_Import,Unik=ThisItem.Unik && !checkbox)
  ),/*Find out whether there is a record whose checkbox value is No in the key related records of SAP_Import*/
  Color.Black,
  Color.Red
)

3.JPG

Best Regards,

Bof

Thansson
Helper I
Helper I

Hi Bof    Thank you for your help.

 

Unik is a column in both lists, used to connect the items (Just like your Picture)

 

I have one gallery connected to Flightlist, when I select one flight, it bring me to a new galley connected to SAP_Import, shoving the selected items on the flight.

 

On this galley I have the checkbox, and I want the flight Field on the first gallery to become green, when all

checkboxes for this flight is checked (True)

 

It is working partly, if I use:

 

(If(IsBlank(LookUp(SAP_Import;Unik=ThisItem.Unik && !CheckBox));Green;White)

 

But Many off the fields are becomming green, without any checkbox is true ? Other flights working correctly?

 

Poweraps sets a warning:  LookUp is maybe not working on large dataset?   Set to 2000

Hi @Thansson :

Whether the records displayed by these exceptions have no associated records in SAP_Import?

If so ,please try this formual:

 

If(
IsBlank(LookUp(SAP_Import,Unik=ThisItem.Unik And !checkbox))&& CountRows(Filter(SAP_Import,Unik=ThisItem.Unik))>=1,
Color.Green,
Color.White)

 

In addition,because many functions and operations that are not delegable are used, this formula cannot be delegated. If the number of your records is less than 2000, you can use this method to eliminate the impact (but the warning will not disappear):

1.JPG

Best Regards,

Bof

View solution in original post

Thank you, Realy Nice of you to help.

 

Tony

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (67,322)