Help: Highlighting if incorrect value pair in two columns is entered

[Ma_Heu]

Hello,

I am very new to power automate, and having some issues to get my head around a problem with conditional formatting or some sort of highlighting a certain condition in my SharePoint list. Maybe someone can help me with the following Szenario or give me some pointers in the right direction.

I have one SharePoint list which is used to track various activities of processes that run on different products.

• Column A is of the 'text' type and contains a process ID (e.g., ID-111). The column can contain multiple entries of the same process ID
• Column B is very similar. It is also of the 'text' type and contains the product ID (e.g., P-999). As above the column can contain multiple entries of the same product ID
• Column C-... contain various other information, but are irrelevant to the question

What I want is to alert the user via conditional formatting, other forms of highlighting or a dedicated 'alert' column if an incorrect 'process ID <-> product ID' pair was inserted:

Each process ID is only allowed to have a single product ID associated with it. It is OK to have multiple row entries with the same process ID, but it always has to have the same product ID.

It is also OK to have another process ID associated with the same product ID, but then again the rule above has to apply.

Example:

Process ID // Product ID // Column C // ALERT

ID-111     // P-999          // ...              // OK

ID-111     // P-999          // ...              // OK

ID-222     // P-999          // ...              // OK

ID-222     // P-999          // ...              // OK

ID-111     // P-888         // ...              // ERROR

I am quite flexible how the error can be highlighted. It can highlight all rows of the respective process ID or only the newest with the mismatch. Also it does not matter if the product ID field or the process ID field are highlighted or if a dedicated alert column is used.

Previously I have used XLookUp in Excel in combination with help columns to do the trick. But I am somewhat lost at the moment in SharePoint. Some help would be much appreciated 🙂

Please let me know if my explanation was to confusing or if there any open questions:)

Best,

Max

[#2]

Hi @Ma_Heu , have you explored using conditional formatting in the list itself, rather than via Power Automate? A good article to familiarise yourself with conditional formatting in lists is SharePoint list conditional formatting based on a date - Enjoy SharePoint. You could use an expression to compare the suffix in each field, then evaluate.

[#3]

Hi @Ma_Heu, As I understand, you would like to have unique pairs between process ID and product ID. I’m not sure how far you are with the development of the list, but maybe you can create a new list with just the process ID and product ID pairs and enforce unique values on the process ID column. Then, you can use values from the newly created list in your list as a lookup column. This way, you can avoid problems with inconsistencies. 

If that solution does not help then i will check the possibilities with Power Automate, but I can do on Sunday.