Filtering the data(optionset value column) based on specific user names

[Sai47]

Hi all,

I am looking to add in gallery a filter column AB which is (optionset value column) where I need to filter Yes values from AB column for only few users like john xxx, tom ccc etc. I created AB column in userlist table with Yes for the intended users. I am using code as AB in Filter(coluserlist,'Primary Contact Name'=User().FullName=john xxx).AB but is showing error in formula, where for rest of the users should show both (Yes and No)values. Can someone help me in this please.

@BCBuizer can you please look into this which is continuation of Filtering the data based on current user name,

Many Thanks,
Sai

[BCBuizer]

Hi @Sai47 ,

In this new structure, the formula should look like this:

Search(
    Filter(
        Datasource,
        IsBlank(ABDrop.Selected.Result) || 'AB' = ABDrop.Selected.Result,
		IsBlank(MADrop.Selected.Result) || 'MA' = MADrop.Selected.Result,
		IsBlank(KVDrop.Selected.Result) || 'KV’ = KVDrop.Selected.Result,
		IsBlank(SPDrop.Selected.Result) || 'SP' = SPDrop.Selected.Result,
		IsBlank(PDrop.Selected.Result)  || 'P' = PGroupDrop.Selected.Result,
		'MA' in Filter(
			userlist,
			'primary name column'=user().FullName,
			AB.Yes || User().FullName = <EXEMPTUSERFULLNAME?>,
		).MA			
	),
    SearchBox.Text,
    "Itemdescp",
    "ProdID"
);

[Sai47]

Hi @BCBuizer 

Please see the below findings

From the screenshot of userlist table, Is it correct , the exemptuserfullname column in the table as full names are empty for AB.Yes?

After trying with the new code the result is the values are not returned again for AB.Yes||User().FullName='Exemptuserfullname'.

Assuming as there are no names for AB.yes in the exemptuserlist column.

[BCBuizer]

Hi @Sai47 ,

So far I assumed you only have a single name in the userlist.exemptuserlist  column ( and coluserlist.<FULLNAME?> for that matter), but now I read you writing about nameS. Can you confirm the column type an and whether this contains a single name or multiple?

[Sai47]

Yes, the exempt userlist column(text column) is similar to primary name column that contains multiple names, for example I just have given simple screenshot. As I need to exempt the name for AB.yes condition the row is empty and for AB.No there is full name in the exempt list column.

[BCBuizer]

HI @Sai47 ,

Then there's the issue: the equals operator only works if there's only a single name in the exempt column. Instead you can try using the in operator:

Search(
    Filter(
        Datasource,
        IsBlank(ABDrop.Selected.Result) || 'AB' = ABDrop.Selected.Result,
		IsBlank(MADrop.Selected.Result) || 'MA' = MADrop.Selected.Result,
		IsBlank(KVDrop.Selected.Result) || 'KV’ = KVDrop.Selected.Result,
		IsBlank(SPDrop.Selected.Result) || 'SP' = SPDrop.Selected.Result,
		IsBlank(PDrop.Selected.Result)  || 'P' = PGroupDrop.Selected.Result,
		'MA' in Filter(
			userlist,
			'primary name column' = user().FullName,
			AB.Yes || User().FullName in <EXEMPTUSERFULLNAME?>,
		).MA			
	),
    SearchBox.Text,
    "Itemdescp",
    "ProdID"
);

[Sai47]

Hi @BCBuizer ,

The result is same as earlier, no rows returned when there is no name in exempt userfullname column for AB.Yes condition for given code.

Thanks

[BCBuizer]

Hi @Sai47 ,

Can you please give the full info for an example that is not returned, but you expect it to:

- Who is the current user?

- Which record (value of at least the MA column) from DataSource is not retruned, but should be?

- Which record (value of all columns) for userlist should enable the record from DataSource to be returned, but isn't?

[Sai47]

Hi @BCBuizer 

1.I am the current user in this case as I am testing the records.

2.Value of the MA column that should return, there are two MA's (pls see screenshot) under my username which contains 108rows.

3.Records from user list to enable the data to return from Datasource (Exempt user list name and AB columns) as in screenshot.

When I use the given code I need to get rows particular to first and last row(AB.Yes condition).

please note MA and AB are columns created in userlist from datasource table inorder to get user specified rows.

[BCBuizer]

Hi @Sai47 ,

You mention you should get only the first and last rows, but this goes against the logic you mentioned so far: the second item should also be returned because you are exempt from AB.Yes, so you should get all of them. 

In the tests I did things work as described:

[Sai47]

Apologies for misinformation of AB condition and Exemptusername list from last screenshot, Please see the information below 

Exempt userlist name	AB	PrimaryContactName	MA
	Yes	Sai	aa
Tom	No	Tom	aa
	Yes	Sai	bb
Jane	No	jane	cc

where for No condition there is exempt userlist name, tom and jane should get all the rows and sai need to get 'yes' condition rows. When I tried with this I am getting all the rows(Yes and No condition) but should not.