Help with filtering a gallery

[Lefty]

Hi,

I have the following on my Items property on my Gallery allowing me to group and search the items this works fine: 

GroupBy(
    Search(
    Uview,
    TextInpSearch_1.Text,
    "UserName", "Ro", "Unit"), 
    "UserName", "GroupedDATA"
)

I now need to extend this to only display records for the logged in User where they are part of the same region.

I have another column called 'UGroup' in my SQL view, which contains values for different regions, I presume I need to use a Filter on the above, but getting stuck. Can someone help with the code I need to allow me to only show users when the logged in user is part of the same region found in the UGroup column?

I do have a Nested gallery, but I do not think it would affect what I need to do with the main gallery.

I also want to be able to override this for 1-2 users, and I think I would need to hardcode there username?

@timl @iAm_ManCat - Could either of you help me resolve this please?

[NandiniBhagya20]

Hi @Lefty ,
Is there any list where you are storing user details with region 

GroupBy(
    Search(
    Filter(Uview, 'Created By'.Email = User().Email),
    TextInpSearch_1.Text,
    "UserName", "Ro", "Unit"), 
    "UserName", "GroupedDATA"
)

[Lefty]

Hi @NandiniBhagya20 

This won't work as I don't have a 'created by' value in SQL, sorry i should have mentioned its a SQL view I am using.

It's the 1 View, which contains all the data I need in my gallery

As mentioned the 'UGroup' contains the region details and the UserName column contains the user name. I also have a UserEmail field in the SQL view, which probably is better suited to reference against?

[Lefty]

Hello

Is anyone able to assist me with this please

[timl]

Hi @Lefty 

Just to clarify, the schema of your Uview view looks like this:
Uview(UserName, UserEmail, Ro, Unit, Ugroup)

I assume there's a 1:1 mapping between UserName and Ugroup? In other words, a user can only belong to a single Ugroup?

In this case, you could lookup the Uview for the current user and then filter Uview to return only those records that match the Ugroup for the user.

With({selectedUgroup:LookUp(Uview, UserEmail=User().Email).Ugroup},
     GroupBy(
        Search(
               Filter(Uview, Ugroup=selectedUgroup),
               TextInpSearch_1.Text,
               "UserName", "Ro", "Unit"),
               "UserName", "GroupedDATA"
       )
     )
)

To handle the case where you want to override this for 1 or 2 users, you could either hard code the username as you say, or you could return a new column in your Uview that indicates that the filter should be overridden for a give UserEmail address.

[Lefty]

Hi @timl 

Thank you so much for this I just couldn't get it to work, this seems to display to me only records specific to the region the user is in.
I tried hardcoding my email address in the above code, but no matter where I place the condition it errors on me, could you please advise where in the code I can hardcode my email to bypass any filtering please?

Also just so I understand the code, you have created a context variable to store the logged in users Ugroup value. and then later on filtered the SQL view to display only the values which match the selectedUGroup, is my understanding correct?

[timl · Accepted solution]

Hi @Lefty 

>> Also just so I understand the code, you have created a context variable to store the logged in users Ugroup value. and then later on filtered the SQL view to display only the values which match the selectedUGroup, is my understanding correct?

Yes, that's all correct!

To simplify this, you could create a global variable that stores the group of the logged in user in the OnStart of your app. 

In this section, you can also set a variable that defines whether the logged in user should be able to override the filter.

Set(varUgroup, 
    LookUp(Uview, UserEmail=User().Email).Ugroup
);
Set(varAllowOverride, 
    User().Email in ["lefty@email.com","user2@email.com","user3@email.com"]
);

You can then adapt your gallery formula like so:

GroupBy(
     Search(
            Filter(Uview, Ugroup=varUgroup || varAllowOverride=true),
            TextInpSearch_1.Text,
            "UserName", "Ro", "Unit"),
            "UserName", "GroupedDATA"
    )
)

[Lefty]

That's brilliant,

Thank you so much 

Is it ok if I tag you for further help in other questions, as working with SQL is proving somewhat challenging, and I find not everyone is as clued up with SQL as they are with say SP.

[timl]

Hi @Lefty 

Glad to help. Sure, feel free to tag me on any future SQL related questions.

[Lefty]

Hi @timl 

Hope you can help, the business have changed how varAllowOverride will be captured, there is now a column in the same Uview and a column name called DCode and where its contains "GT345" those user are the users who should see all the data I tried something like:

Set(varAllowOverride, 
    LookUp(Uview, DCode = "GT345"));

 but on my gallery it states on the = sign incompatible types of comparison.....

clearly i've not done this correctly, can you help?