LinkedIn
Twitter
Hi,
How to avoid repeating/duplicates entries when multiple users use the app
Expression in Initial value to for incremental SL NO
MAX(TEST[SL NO])+1
Valid If formula: suggested by @Suvrutt_Gurjar in this post to avoid duplicates.
Note : This expression allows to save initially but after saving , while clicking the edit button, it shows valid if error.
ISBLANK(
FILTER(
"TEST",
([_THIS] = [SL NO])
)
- LIST([_THISROW])
)SL NO ( Serial Number should be sequential in nature.
I get double entries when there are multiple users at a time.
How to fix this issue ?
Dear @Suvrutt_Gurjar ,
I have read many articles in tips and tricks, finally they say it is not consistent and it is unreliable due its drawbacks. I worried so much because a tech giant Google's appsheet could not make it possible which normal multi user supermarket billing POS software has. I have seen in many reliance super markets and banks which has multiuser POS systems and queuing systems. mine's (app) is kind of multiuser parcel management app where incremental numbers are set to each invoices. I don't know what logic they use to solve this issue in other platforms and don't know whether will it suits in appsheet. I am really frustrated due the limitation of Appsheet.
@Suvrutt_Gurjar sir,
I could see in an app called vyapar ( billing and account app/ software). It indicates the user if the Invoice number already used by another user and askes to enter higher number and in the second attempt automatically it adds the invoice number + 1 without user initiation. that was pretty cool !! Appsheet has to take this type of requirements into consideration and bring it into effect.
I seek your attention for the workaround which I have developed. I am requesting you to test the workaround for the consistency and comment about its reliability . If it is reliable method then I will post in TIPS and TRICKS of appsheet community. I sincerely thank you all for helping me to achieve this.
@scott192 @Suvrutt_Gurjar @AleksiAlkio @TeeSee1 @WillowMobileSys @MultiTech @Koichi_Tsuji @Aurelien @lynchk21
What I have did :
1. I have created a test app which has one simple table.
2. It has column [ SL NO] , and other some columns like name, date, etc .
Note : a special column [ KEY] is added and it was set as key column of the table.
3. A bot was created with name : Set SL NO with wait condition
Bot configuration : When this EVENT occurs: When a row is added
Process :
Step 1 . : Check for previous row condition
Expression logic used : checks the table has only one record if not checks the previous row column [ SL NO] is not blank.
The expression assistant shows the error for the expression while saving the editor, error did not stop the app functioning.
Step 2 : Set SERIAL NUMBER ( this branched step is executed if the previous row column [SL NO] is not blank).
Set these column(s) expression :
Step 3 : Wait for the condition (ISNOTBLANK).
Condition is :
The expression assistant shows the error for the expression while saving the editor, error did not stop the app functioning.
Expression Logic : Checks the previous row column [SL NO] is not blank.
Step 4 (last ) : SET SERIAL NUMBER .
same step 2 is repeated.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Logic behind this workaround :
whenever new row is added by multiple users. when the table has the key column with initial value as UNIQUEID(), it will not overwrite the row and instead it creates new row for the each record.
Now the column [SL NO] has to be filled with sequential manner without duplicates and repeating of same number.
Firstly the step 1 checks whether the table has only one record, if yes step 2 is executed if not the bot checks for previous row [SL NO] column whether it is blank or not . If it is not blank step 2 will be executed and If it is blank step 3 will be executed.
Step 3 is a wait condition step , until the condition is met other steps are not executed. if the condition is met it will execute the step 4 .
Lets have an User case with 3 users concurrently :
1. when 3 users adds a row concurrently ( which means server will receive 3 new rows) . these 3 rows are not overwritten due to the UNIQUE ID () present in the key column .and automatically it triggers the bot 3 times sequentially .
now bot starts its work.
During the time of adding the 3 new rows to the database, [ SL NO] will be blank.
step 1 will be executed which checks for condition. step 2 : SERIAL NUMBER is set since there may be new or some records in the database.
step 3 is applicable to the 2nd and 3 rd record / row. it will wait for the condition since 1st record [ SL NO] will be blank. once the previous row [ SL NO] ( ie. 1st record [SL NO]) is not blank , step 4 ( SET SERIAL NUMBER) will be executed. It is a kind of queueing the numbering system.
With Regards
Jaichith J
When I use virtual column, still was appunting to [SL NO] column who could have the same original problem (duplicate).
So, if for any reason you have a duplicate [SL NO] you can identify and solve it through the virtual column (Line 5):
[Virtual SL NO]
MAX(SELECT(PRODUCTOS[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))+
IF([_THISROW].[SL NO]-MAX(SELECT(PRODUCTOS[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))>1,[_THISROW].[SL NO]-MAX(SELECT(PRODUCTOS[SL NO],[_RowNumber]<[_THISROW].[_RowNumber])),1)+
COUNT(SELECT(PRODUCTOS[SL NO],AND([_RowNumber]<[_THISROW].[_RowNumber],[SL NO]=[_THISROW].[SL NO])))You can named virtual column any you prefer. [Virtual SL NO]
And the way to get values for [SL NO] column Still is by a Bot: Set new values and use the given formula .
This formula in the bot must to be enough to get unique SL NO. I tried several times and not get duplicate values. But if ocurrs the [Virtual SL NO] will solve it.
Let me insist.
Virtual column it's only a backup for possible duplicate values in [SL NO] column. But if you get this values through the bot this not have to happen.
An improve of virtual column formula is the following:
Line 1: Select the max value of existing records without taking into account new records.
Line 3: Look if some records were deleted. If so, add them
Line 5: Look if there are duplicate records to consider and increase the account.
I could not understand the logic of the formula (given by you) So I have applied directly by changing the table name alone.
brother you are nearer to the solution because in virtual column , I am getting the sequence but I could find some thing that whenever there is duplicate in the normal column [ SL NO ] there is repeating same number after some rows. I request you to fix the expression so that the error is repeated again. kudo !!
Request : for time being we shall work with common names such as TEST - table, SL NO etc. so that testing could be easier. expression which we share can be in HTML.
again same pattern repeating.
Virtual column SL NO Expression :
MAX(SELECT(TEST[SL NO], [_RowNumber] < [_THISROW].[_RowNumber])) +
IF(
[_THISROW].[SL NO] - MAX(SELECT(TEST[SL NO], [_RowNumber] < [_THISROW].[_RowNumber])) > 1,
[_THISROW].[SL NO] - MAX(SELECT(TEST[SL NO], [_RowNumber] < [_THISROW].[_RowNumber])),
1
) +
COUNT(
SELECT(
TEST[SL NO],
AND(
[_RowNumber] < [_THISROW].[_RowNumber],
[SL NO] = [_THISROW].[SL NO]
)
)
)
Normal column SL NO ( Expression)
MAX(
SELECT(
TEST[SL NO],
[_RowNumber] < [_THISROW].[_RowNumber]
)
) +
IF(
[_THISROW].[SL NO] - MAX(SELECT(TEST[SL NO], [_RowNumber] < [_THISROW].[_RowNumber])) > 1,
[_THISROW].[SL NO] - MAX(SELECT(TEST[SL NO], [_RowNumber] < [_THISROW].[_RowNumber])),
1
) +
COUNT(
SELECT(
TEST[SL NO],
AND(
[_RowNumber] < [_THISROW].[_RowNumber] - 1,
[SL NO] = [_THISROW].[SL NO]
)
)
)
I had improve the formula FOR [Virtual SL NO] (Line 5: Count all duplicate values and add it to [Vitrual SL NO])
MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))+
IF([_THISROW].[SL NO]-MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))>1,[_THISROW].[SL NO]-MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber])),1)+
COUNT(SPLIT(SELECT(TEST[SL NO],
[_RowNumber]<[_THISROW].[_RowNumber] )," , "))-
COUNT(SPLIT(UNIQUE(SELECT(TEST[SL NO],
[_RowNumber]<[_THISROW].[_RowNumber] ))," , "))The function it works but i don't know why virtual column is not calculating, even if i edit the row or made any change in the table (add, delete).
Please help with this. Here a screenshot for the test of virtual column, as you can see, the "Expression result" is ok but the value given for [Virtual SL NO] is wrong.
- [SL NO]: (Yellow) Normal column written by the bot.
- [Virtual SL NO]: (Blue) Result of virtual column when record was created, but is not re-calculate to the correct Expression Result
| User | Count |
|---|---|
| 18 | |
| 11 | |
| 11 | |
| 8 | |
| 4 |
