0

I used calculated column with "IF" & "AND" Functions,

  1. If the logic is true or fall certain comments will be appeared to users// Comments "Verification Success", "Verification Failed".

  2. When I try to filter data using particular comments, the column shows 2 with same name, refer the image. there should be only above two comments in filtering, but in my case, it shows below in the filtering point:

    • Verification Success
    • Verification Failed
    • Verification Success Verification Failed

Formula:

=IF(AND([Applicant Personal Data Verification]="Verification Pending",[Employer/Business Data Verification]="Verification Pending",[Guarantor/Referee Data Verification]="Verification Pending"),"Fresh Application",IF([CRM Returned]<>0,"CRM Returned",IF(AND([Applicant Personal Data Verification]="Verification Success",[Employer/Business Data Verification]="Verification Success",[Guarantor/Referee Data Verification]="Verification Success"),"Verification Fully Success",IF(AND([Applicant Personal Data Verification]="Verification Success",[Employer/Business Data Verification]="N/A",[Guarantor/Referee Data Verification]="Verification Success"),"Verification Fully Success",IF(AND([Applicant Personal Data Verification]="Verification Success",[Employer/Business Data Verification]="N/A",[Guarantor/Referee Data Verification]="N/A"),"Verification Fully Success",IF(AND([Applicant Personal Data Verification]="Verification Success",[Employer/Business Data Verification]="Verification Success",[Guarantor/Referee Data Verification]="N/A"),"Verification Fully Success","Pending/Failed View Record"))))))

The case is related with above formula, but I used simple one to explain the problem.

enter image description here

  • Can you please add your calculated field formula as well? There might be extra space added at the end of the string. – harshal gite Nov 9 at 17:40
  • Hi, Harshal, Refer my edited question with the formula, it can not be uploaded as a comment hence restriction of No of characters. thank you – Sameera Nov 9 at 18:18
  • 1
    You must have or had some character that appears to be a space in the value or a space at the end. Filter the list by each value. Whatever one has the least rows, edit each row and save it. Then see what the choices look like. If it’s the same, the issue is in your current formula. If it fixed itself, it was in the formula but not a problem any more. – mannaggia Nov 9 at 23:51
  • after cut and paste the formula, the issue was rectified at the moment, but it will happen in next day. – Sameera Nov 10 at 16:22
0

SharePoint filters never show duplicates unless there is any difference.

Try typing the whole formula yourself in notepad or vs code (anywhere) and then paste it. Sometimes blank space characters get embedded when we copy paste from some website or other source. One way to check is to filter by both the duplicate values and right click on that value in the column and inspect that element in the browser. It will show the encoded characters if any. Compare the values, you will spot the difference and the reason why duplicates are appearing in the filter.

If that does not help then try hitting rest api url (to get item by id) on the 2 duplicate items which you get on filtering. Check how data is stored in both the columns internally. You can identify the difference there.

Hope it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.