Filter gallery based on multiple conditions

[Kiruthika22]

Hi Team,

I have a requirement to filter a gallery based on three different columns in sharepoint.

I have used a if condition in the gallery items property to filter based on the button selected.

The three columns are person type columns ==admin,supervisor,submittedBy

Sharepoint list name----TestSp

I need to display the records in the else condition whereever the

- current loggedin user is present in the admin column (and)

- current loggedin user is supervisor but not equal to submittedBy (and)

- current loggedin user not equal to submittedBy

I have used the below code: It is not throwing syntax error but not displaying any record.

Totally i have 2 items tagged as admin and 2 items as supervisor(SubmittedBy != Supervisor)

So i should see 4 records in the gallery.

=====================

If( varSet = true,

Sort( Filter( 'TestSp', StartsWith( 'Opportunity name', 'txt.Search'.Text ), Status.Value = "Approved" && (Lower('SubmittedBy'.Email) = Lower(User().Email)) && (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank()), (DateValue(Created) >= DateValue(Date_1)) ), Modified, Descending ),

Sort( Filter( 'TestSp', StartsWith( 'Opportunity name', 'txt.Search'.Text ), (Lower(User().Email) = Lower('admin'.Email)), ((Lower(User().Email) = Lower('Supervisor'.Email)) && (Lower(User().Email) <> Lower('SubmittedBy'.Email))) && (Assessment_status.Value = "Draft" || Assessment_status.Value = "Approval Pending" || Assessment_status.Value = "Approved" || Assessment_status.Value = "Rejected") && (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank()), (DateValue(Created) >= DateValue(Date_1)) ), Modified, Descending ) )

Please help me out .

Thanks and Regards, Kiruthika

[victorcp]

Hi,

Perhaps you need to change the 'And' to 'Or', because if you use 'And' all the condition must be true to display the items.

- current loggedin user is present in the admin column (or)

- current loggedin user is supervisor but not equal to submittedBy (or)

- current loggedin user not equal to submittedBy

Any question please let me know

I hope it helps 🙂

[v-jefferni]

Hi @Kiruthika22 ,

Could you please confirm that all the three Person columns allow single selections? Then could you please clarify the logic more specifically? For your scenario, as there are three person columns and based on the logic you give, I can narrow it down to: current logged in user is admin and supervisor but not submittedBy. However, this doesn't seem correct. If this is exact the logic, please try below formula:

If( varSet = true,
Sort( Filter( 'TestSp', StartsWith( 'Opportunity name', 'txt.Search'.Text ), Status.Value = "Approved" && (Lower('SubmittedBy'.Email) = Lower(User().Email)) && (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank()), (DateValue(Created) >= DateValue(Date_1)) ), Modified, Descending ),
Sort( Filter( 'TestSp', StartsWith( 'Opportunity name', 'txt.Search'.Text ), 
    (Lower(User().Email) = Lower('admin'.Email)&&((Lower(User().Email) = Lower('Supervisor'.Email) && (Lower(User().Email) <> Lower('SubmittedBy'.Email)) && (Assessment_status.Value = "Draft" || Assessment_status.Value = "Approval Pending" || Assessment_status.Value = "Approved" || Assessment_status.Value = "Rejected") || (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank())&& DateValue(Created)>= DateValue(Date_1)),
 Modified, Descending 
)
)

Actually, there aren't many places need to modify, except below part:

(Assessment_status.Value = "Draft" || Assessment_status.Value = "Approval Pending" || Assessment_status.Value = "Approved" || Assessment_status.Value = "Rejected") || (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank())

Best regards,

[Kiruthika22]

Hi @v-jefferni , Thanks .

Sorry for replying late.

My requirement is wherever the current loggedin user is present in admin column that records should be fetched and also wherever his name is present in supervisor column that records should also be fetched ..but we need to check one more condition when he is tagged in supervisor we should also ensure the supervisor and submittedby column should not be same for that particular record. 

Note: admin and supervisor cannot be same for the same record.

[v-jefferni · Accepted solution]

Hi @Kiruthika22 ,

Then please try below:

If(
    varSet = true,
    Sort( Filter( 'TestSp', StartsWith( 'Opportunity name', 'txt.Search'.Text ), Status.Value = "Approved" && (Lower('SubmittedBy'.Email) = Lower(User().Email)) && (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank()), (DateValue(Created) >= DateValue(Date_1)) ), Modified, Descending ),
    Sort(
        Filter(
            'TestSp', 
            StartsWith('Opportunity name', 'txt.Search'.Text), 
            (
             Lower(User().Email) = Lower('admin'.Email) || (Lower(User().Email) = Lower('Supervisor'.Email) && (Lower(User().Email) <> Lower('SubmittedBy'.Email))
            ) && ((Assessment_status.Value = "Draft" || Assessment_status.Value = "Approval Pending" || Assessment_status.Value = "Approved" || Assessment_status.Value = "Rejected") || (Assessment_status.Value = 'Drp.Status_1'.Selected.Value || 'Drp.Status_1'.Selected.Value = Blank())) && DateValue(Created)>= DateValue(Date_1)
        ),
        Modified,
        Descending 
)
)

Best regards,

[Kiruthika22]

Hi @v-jefferni ,

Sorry for delayed reply. This was really helpful with some tweaks in the logic added and my issue got resolved.

Thanks for your help.