Solved: Auto-generate Transaction No with security filters

Rome18 · 03-13-2022 12:49 AM

Hi There

I have a normal Expense Table with a Transaction Number Column.

I give this [Transaction No.] a formula to auto compute it with a very simple formula like [_rownumber]-1 (and some formating using concatenate).

What I am asking is, it seems if I add security filters to the table to only show their own user's data, this formula does not work, the formula calculates individually for every user (even though the first data of Users B is on row 20 the result of the formula is still 1. It supposedly generate number 19).

Is there a way to auto-generate this [Transaction No] as a whole data? not as individual data.

Thank you in advance

I am still new in this Appsheet environment 😄

Suvrutt_Gurjar

Hi @Rome18 ,

Hope you were able to take a look at the article shared and the section below.

I am sorry that I have no further inputs in this regard.

Please evaluate Google sheet based approach shared by another AppSheet expert @Koichi_Tsuji

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Auto-sequential-numbers-using-Googlesheet-formul...

Someone else may have solution for your requirement of non repeating serial numbers in multi user environment with AppSheet expressions.

View solution in original post

TeeSee1

Here are a few documents regarding the generation of key (unique) values.

https://help.appsheet.com/en/articles/1726585-sequential-keys

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Refining-UNIQUEID-expression/m-p/338017

https://help.appsheet.com/en/articles/1726606-manually-generating-uniqueid-key-values

Check them to see if they help you with your problem (perhaps not directly but get what you need in the end - the generation of unique transaction numbers)

Rome18

Okay Thanks before, but I don't think this is what I asked. What I don't understand is, how Appsheet read [_Rownumber] column as an individual column if security filters were applied, the formula on my [Transaction No.] column worked perfectly just before I added security filters, and [Transaction No.] is not my key values of the table, I have another column [Expense ID] for my key value.

My Security Filters:

IF(ANY(Current User[Role])="Superuser",TRUE,[Maker]=ANY(Current User[User ID]))

After I added the security filters it seems to me like my formula is calculating individually for each user. Even after I changed the formula to COUNT(Expense[Expense ID])+1 it does not count all Expense ID in the database, but only count [Expense ID] that specific users have made.

TeeSee1

Hmm,

When I use a security filter, [_ROWNUMBER] is not affected.

COUNT(Expense[Expense ID])+1, however, should change depending on who is the logon user because the app only sees what is available to that user.

Rome18

This is my data: (all this row, the maker is User A only)

This is user B (have 0 data)

My Formula on Initial Value of [Transaction No.] :

When I add new data from users B:

The new data on sheets (is on row 18 but [_rownumber]-1 generate 1

Maybe this helps @TeeSee1?

Suvrutt_Gurjar

Have not gone into details of your requirement. However it sounds that you are using sequential number generation in a multiuser environment.

Just wanted to add a separate aspect of AppSheet. In a multi user environment, AppSheet does not recommend serial number generating expressions. You may wish to take a look at the following informative post by @Steve and the help article.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Serial-Numbers-If-You-Must/m-p/286300

https://help.appsheet.com/en/articles/1023086-what-is-a-key

Rome18

I kind of did try this stuff.

I already have a column called [Number], and this is my initial value

works well for User A:

But now for user B, my MAX(Expense[Number]+1) generate = 1 again

Suvrutt_Gurjar

That is why shared the article on caution with you.

If user B has NOT synced the app before adding the new row, user B's device will NOT have updates of user A. User B 's device will see the Expense table as empty if not synced before adding the new row and will add new row with number as 1 instead of next sequence number 17. Alternatively, if you are using security filters, please evaluate what rows are reaching each user's device.

Rome18

I tried to sync first before adding new data with user B from the desktop and from my phone. But still, the value of [_rownumber] in the first user B data generates number 2 instead of 18

Rome18

I think I figured out what is really happening. It is not that [_rownumber] does not work.

This only affects the first entry from each user.

This is a photo of the first data being uploaded to the spreadsheet. The [_Rownumber] is still error and shows 2 (should be row number 18) even though I have 17 rows in the spreadsheet already

After I wait for a while, the value auto-corrects the [_rownumber]. But because my [Transaction No.] formula is on the Initial Value Column, the value is still E.00001

Then if I add another data, the [_rownumber] work normally. [_rownumber] - 1 = 18

Is there any way to prevent this stuff?

Suvrutt_Gurjar

I think your next entry works because the app has synced while doing the first entry, so it has the latest data. Now after adding a 5 -6 rows for user B , please see what user A ' s devices adds , without syncing first.

Rome18

It works because the Appsheet auto-corrects it when the data sync-ed. But it only auto-corrects the [_rownumbers].

My [Transaction No.] column, which has an initial value formula of CONCATENATE("E.", RIGHT("0000"& ([_RowNumber]-1), 5)) isn't corrected, unless the users edit it again and the initial value recalculate. what I need is [Transaction No.] and [_rownumber] are correct from the start. Is there any possible way to do that?

Suvrutt_Gurjar