Skip to main content
Solved

Conditional Formatting works but not the custom field


Forum|alt.badge.img

We have a generic inquiry that flags when there is a mismatch or a discrepancy between Customer Billing Information and Sales Order billing information. 

 

We added a conditional formatting whenever SO Billing Email and Customer Billing do not match and similarly for SO Bill To Attention and Customer Bill To Attention do not match as shown below: (this works as expected)

Conditional formatting

Requirement: Now, we want to add a custom field/ column to this GI called Update/ Action which tells you to update when there is a discrepancy between Customer Billing Information and Sales Order billing information. (i.e. SO Billing Contact, Customer Billing Contact, SO Bill to attention, Customer bill to attention)

  1. First implementation: We created a switch statement, which partially works (only when the first condition is not met)
Switch statement

There are cases when customer and sales order billing email matches but not the attention, in that case - it returns blank (when it should say update)

 

  1. 2nd implementation: we then created a column called test which has the exact iif statement as the conditional formatting, but it works the opposite - it displays 0 always even though there is no match. 
    Should display 1 when the two values match and 0 when there is no match.

Below is how the generic inquiry looks. Data values are hidden on purpose. Here the Sales order Bill to attention is blank and customer bill to attention is filled in. Also the SO Bill to Email and Customer Bill to Email matches - the column Action should say ‘update’ and Test should say 1. But they do not. 

Any recommendations/ suggestions would be appreciated! Thank you. 

Best answer by darylbowman

 

Try this

=IIf(Trim(IsNull([Contact.Attention],''))<>Trim(IsNull([SOContact.Attention],'')),'update','no action')

 

View original
Did this topic help you find an answer to your question?

6 replies

darylbowman
Captain II
Forum|alt.badge.img+13

When comparing strings, it is a good idea to trim them.

Try replacing your first switch with: 

=Switch(Trim([Contact.Email])<>Trim([SOContact.Email]) Or Trim([Contact.Attention])<>Trim([SOContact.Attention]),'update')

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 63 replies
  • June 7, 2023

Hi @darylbowman, Thank you for your recommendation. I tried what you suggested, unfortunately that did not work. Should ‘update’ come before the Or statement? 


darylbowman
Captain II
Forum|alt.badge.img+13

Nope. You can chain together conditionals with And / Or. My statement says exactly the same thing as your original statement, just with additional trims.

Try making two more test column with just

=IIf(Trim([Contact.Email])<>Trim([SOContact.Email]),'not match','match')

and

=IIf(Trim([Contact.Attention])<>Trim([SOContact.Attention]),'not match','match')

and see which values are not showing in the way that you expect.


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 63 replies
  • June 7, 2023

Hi @darylbowman, thank you again. I tried your above suggestion and looks like it is not working with NULL/ blank values. The last two lines of the snippet, the highlighted values in yellow should say “Update” but they are showing up as no action

Modified GI results

 


darylbowman
Captain II
Forum|alt.badge.img+13

 

Try this

=IIf(Trim(IsNull([Contact.Attention],''))<>Trim(IsNull([SOContact.Attention],'')),'update','no action')

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 63 replies
  • June 7, 2023

That seemed to have worked! Thank you very much! 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings