Solved: Code Retrieving Incorrect Data

JasperHotsheet · 11-10-2022 05:07 AM

I have been using the code below to fetch the last randomly generated "REQ ID" for the user that is currently login in to the application.

LOOKUP(MAX(SELECT(REQUISITIONSP[_ROWNUMBER],([ SUBMITTED BY]=USEREMAIL()))),"REQUISITIONSP","_ROWNUMBER","REQ ID")

For some reason on occasion it has been pulling the last "REQ ID" from users that aren't the current user. I'm not even sure how its possible as it can't fetch row numbers that aren't connected to the current user that is logged in. Its pulling the row number and req ID from the same table at the same time.

WillowMobileSys

While there is nothing inherently wrong with your approach, I think it might be better to the use the MAXROW() function. You may have to save the row reference in one column and then use another column to dereference the row to get the value you want.

For example, maybe you use MAXROW() to assign a column named [Max Submitted Row] which saves the row key to this column. You can then dereference that row to get at any column value like this:

[Max Submitted Row].[Req ID]

As for why "other" users rows are being selected... Do you allow deletion of rows? Can users modify other existing rows?

I caution against using [_ROWNUMBER] in any way. While rows do tend to get the same row number over time, there is never any guarantee a row will always be assigned the same row number - even from one Sync to another. Definitely do not use it as a key column.

View solution in original post

WillowMobileSys

While there is nothing inherently wrong with your approach, I think it might be better to the use the MAXROW() function. You may have to save the row reference in one column and then use another column to dereference the row to get the value you want.

For example, maybe you use MAXROW() to assign a column named [Max Submitted Row] which saves the row key to this column. You can then dereference that row to get at any column value like this:

[Max Submitted Row].[Req ID]

As for why "other" users rows are being selected... Do you allow deletion of rows? Can users modify other existing rows?

I caution against using [_ROWNUMBER] in any way. While rows do tend to get the same row number over time, there is never any guarantee a row will always be assigned the same row number - even from one Sync to another. Definitely do not use it as a key column.