Filter lookup values only on Add or Edit page

ASP.NET Maker General Discussion (ASP.NET Maker)
1

I have a table where i use a lookup table where i would like to show only records for the current user.

I did a Filter like this:

“[IdUtente]= ‘” + CurrentUserID() + “’”

This works fine when i edit or add with the select box but when in list or single page view if there are values not referring to this user they are shown with their id instead of the look up values.

How can I always show the correct lookup values but when adding or editing limiting these values only to the ones related to the current user?

The table I am refering doesn’t contains UserId but it is inferred by its OfficeId.

Thanks a lot.

John

2

You can check for the page id in your filter. For example:

(CurrentPageID() == “add” || CurrentPageID() == “edit”) ? “… filter …” : ““

3

Hi,

I did the following filter:

(CurrentPageID() == "add" || CurrentPageID() == "edit") ? "[IdUtente]= '" + CurrentUserID() + "'" : ""

It works fine when Add and Edit (like my original one, "[IdUtente]= '" + CurrentUserID() + "'", did) but in view page it still shows the field id instead of the corresponding lookup label.

wrong_lookup_in_view_page
wrong_lookup_in_view_page697×236 11.8 KB

What’s wrong?

Thanks a lot,
John

4

Enable Debug and see if your filter is working correctly.

5

Hi,

I did the test with debug changing filter three times as in below logs but nothing changed.

I have no clue.

What’s wrong?

Test #01

Filter:


(CurrentPageID() == "add" || CurrentPageID() == "edit") ? "[IdUtente]= '" + CurrentUserID() + "'" : ""

view:


10:27:16.229 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

10:27:16.311 +01:00: SELECT COUNT(*) FROM (SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie]) EW_COUNT_TABLE

10:27:16.316 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] ORDER BY [DateStart] ASC

10:27:16.363 +01:00: SELECT COUNT(*) FROM (SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2)) EW_COUNT_TABLE

10:27:16.368 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) ORDER BY [Denominazione] ASC

10:27:16.416 +01:00: SELECT COUNT(*) FROM (SELECT * FROM [CS_Chiusure_Uffici]) EW_COUNT_TABLE

10:27:16.422 +01:00: SELECT * FROM [CS_Chiusure_Uffici] ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

add:

10:29:41.377 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

10:29:41.485 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] WHERE 0=1 ORDER BY [DateStart] ASC

10:29:41.491 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) AND 0=1 AND [IdUtente]= '2' ORDER BY [Denominazione] ASC

Test #02

Filter:


(CurrentPageID() == "add" || CurrentPageID() == "edit") ? "[IdUtente]= " + CurrentUserID():"[IdUtente] IN ( select distinct [IdUtente] from CS_Utenti)"

view:


10:44:52.724 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

10:44:52.729 +01:00: SELECT COUNT(*) FROM (SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie]) EW_COUNT_TABLE

10:44:52.733 +01:00: SELECT COUNT(*) FROM (SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2)) EW_COUNT_TABLE

10:44:52.738 +01:00: SELECT COUNT(*) FROM (SELECT * FROM [CS_Chiusure_Uffici]) EW_COUNT_TABLE

10:44:52.743 +01:00: SELECT * FROM [CS_Chiusure_Uffici] ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

add:

10:44:02.864 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

10:44:03.299 +01:00: SELECT * FROM [CS_Chiusure_Uffici] WHERE [IdChiusura] = 10 AND [IdUfficio] = 3

10:44:03.359 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] WHERE [IdChiusura] = 10 ORDER BY [DateStart] ASC

10:44:03.364 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) AND [IdUfficio] = 3 AND [IdUtente]= '2' ORDER BY [Denominazione] ASC

Test #03

Filter:


(CurrentPageID() == "add" || CurrentPageID() == "edit") ? "[IdUtente]= '" + CurrentUserID() + "'" : "[IdUtente]<> ''"

view:


01:00:22.023 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

01:00:22.177 +01:00: SELECT COUNT(*) FROM (SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie]) EW_COUNT_TABLE

01:00:22.182 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] ORDER BY [DateStart] ASC

01:00:22.208 +01:00: SELECT COUNT(*) FROM (SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) AND [IdUtente]<> '') EW_COUNT_TABLE

01:00:22.213 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) AND [IdUtente]<> '' ORDER BY [Denominazione] ASC

01:00:22.260 +01:00: SELECT COUNT(*) FROM (SELECT * FROM [CS_Chiusure_Uffici]) EW_COUNT_TABLE

01:00:22.267 +01:00: SELECT * FROM [CS_Chiusure_Uffici] ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

add:


01:00:39.329 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

01:00:39.387 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] WHERE 0=1 ORDER BY [DateStart] ASC

01:00:39.394 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE [IdUtente] IN (2) AND 0=1 AND [IdUtente]= '2' ORDER BY [Denominazione] ASC
6

Is the lookup table User ID protected? If yes, you may not be able show the lookup value correctly.

Create a view without User ID protection and use it as the lookup table instead.

7

Hello,

I removed the User ID protection. I am using a view for lookup.

No the view is ok. But the Edit and Add are missing the filters. The compo for IdUtente lookup is empty.

FILTER:


(CurrentPageID() == “add” || CurrentPageID() == “edit”) ? “[IdUtente]= '” + CurrentUserID() + “'” : “”

VIEW:


12:30:21.274 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

12:30:21.388 +01:00: SELECT COUNT(*) FROM (SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie]) EW_COUNT_TABLE

12:30:21.392 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] ORDER BY [DateStart] ASC

12:30:21.410 +01:00: SELECT COUNT(*) FROM (SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti]) EW_COUNT_TABLE

12:30:21.418 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] ORDER BY [Denominazione] ASC

12:30:21.444 +01:00: SELECT COUNT(*) FROM (SELECT * FROM [CS_Chiusure_Uffici]) EW_COUNT_TABLE

12:30:21.453 +01:00: SELECT * FROM [CS_Chiusure_Uffici] ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

ADD:


12:30:51.388 +01:00: SELECT * FROM [CS_Utenti] WHERE (UPPER([Username]) = 'T.SCAFFIDI')

12:30:51.433 +01:00: SELECT [IdChiusura] AS [lf], [Descrizione] AS [df], [DateStart] AS [df2], [DateEnd] AS [df3], '' AS [df4] FROM [CS_Chiusure_Straordinarie] WHERE 0=1 ORDER BY [DateStart] ASC

12:30:51.436 +01:00: SELECT DISTINCT [IdUfficio] AS [lf], [Denominazione] AS [df], '' AS [df2], '' AS [df3], '' AS [df4] FROM [CS_View_Uffici_Utenti] WHERE 0=1 AND [IdUtente]= '' ORDER BY [Denominazione] ASC

What’s wrong now?

Thanks a lot,

John

8

Should work. Make sure that you remove User ID protection for the lookup table only. If this is a child field of a dynamic selection list, make sure that you have selected a value in the parent field first.

9

Hi Michael,
like that worked!

Only point now is about deny deleting a record in the detail table row when it is not related to own offices.

I tried with Row_Deleting as follow

string userid = CurrentUserID();

string sql = "SELECT STRING_AGG ( ISNULL(IdUfficio,'N/A'), ',') AS uff FROM CS_Utenti_Uffici where IdUtente = " + userid + ";";

// retrieve the offices list associated with the user

// var lstOffices = GetConnection().Execute(sql);

var lstOffices = Execute(sql);

but I always got -1

The query string is fine and when executed is Sql Server Management Studio return the list of associated offices separated by a comma but when I try to run as shown I got always a -1.

What’s wrong?

Regards,

John

10

CurrentUserID() should return the user id of the logged in user. If it is “-1” that means you are logged in as the super-admin. Make sure that you have enabled User ID Security and log in with the non admin user.

Alternatively, if the user id can be retrieved from the record, use the user id value from the record.

11

Hi Michael,

the problems seems to me related somehow in the way I was querying the database to retrieve the comma separted values list of the office ids.

I implemeted as follow and now is working as expected:

// Recordset Deleting event

public bool Row_Deleting(Dictionary<string, object> rs) {

    // Enter your code here

    // To cancel, set return value to False and error message to CancelMessage

    

    string userid = CurrentUserID();



    string sql = "SELECT STRING_AGG ( ISNULL(IdUfficio,'N/A'), ',') AS uff FROM CS_Utenti_Uffici where IdUtente = " + userid + ";";



    string lstOffices = ConvertToString(ExecuteScalar(sql, "DB"));



    // Check if the office being deleted is associated with the ones belonging to the user 



        if (lstOffices.Contains(ConvertToString(rs\["IdUfficio"\]))) // the substring exists in the string

            {

                return true;

            }

            

        else // the substring does not exist in the string

            {

                CancelMessage = "Impossibile eliminare chiusure non appartenenti al proprio ufficio.";

                return false;

            }

}

Many thanks for helping.

Best regards,

John