How to avoid repeating/duplicates when multiple users use the app

[jaichith]

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 ?

[Tsach]

This is the biggest disadvantage of appsheet.
You cant force the users to be synced between them .

[jaichith]

buddy, Do you have any work around to solve this issue ?

[Tsach]

Not really .
I normally do not let users work on the same row .

[jaichith]

It is not possible on my case.

[jaichith]

Here actually, they don’t work on same rows intentionally… But when they create new row/record by nature of the incremental expression they create duplicates.

[Suvrutt_Gurjar]

Ensuring serial number generation in multi user app is not technically possible in AppSheet. Please go through the article below

Sequential keys - AppSheet Help

I am aware , your may ask “any work around available?”. I am sorry that I am not aware of a 100% work around, even though some workarounds that work in most of the cases could be possible. One such workaround is given below.

Please search community for “serial or sequential numbers” in the tips section and “Q&A” section.

Please read the caveat and limitation at the end of the tip post for sure. Also please ensure appropriateness for your use case if you plan to implement..

Custom Row Number to generate sequential number , … - Google Cloud Community

[jaichith]

@Suvrutt_Gurjar

Please search community for “serial or sequential numbers” in the tips section and “Q&A” section.

There are several posts and articles and to be frank , literally, I don’t know what to be done next .. ..

[Suvrutt_Gurjar]

Well, I think you will need to spend some time to anslyze those and evaluate if any one fits your case or not.

I believe that will be inevitable process in any app development or for that matter in any endeavor. Community / help articles may suggest some options. But we being app owners of our respective apps, we will need to take final calls for our respective app.

[jaichith]

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.

[scott192]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Generating-Sequential-Numbering-Using-App-Script-Tasks/m-p/451776

[jaichith]

Hi sir, how long does the script/bot takes to update new serial number?

[jaichith]

@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.

[jaichith]

Dear sir, @Suvrutt_Gurjar @scott192 @AleksiAlkio

These are some Chatgpt responses. which workaround would be easier ?

Option 1 :

Queueing System: Implement a queueing system where each user’s request to add a new record is queued. Each record in the queue is then processed one by one, ensuring that serial numbers are assigned sequentially.

Implement a queue for new entries.

1. Users submit new entries which are stored in a temporary table.
2. A background process reads from this table, assigns sequential serial numbers, and moves entries to the main table.

----------------------------------------------------------------

Option 2:

To avoid duplicate entries and ensure sequential serial numbers (SL NO) in your app when multiple users are using it simultaneously, you can follow these steps:

1. Use a Workflow/Action to Set SL NO: Instead of relying on formulas in the column definition, use an action to set the serial number. This ensures that the serial number is generated and assigned at the time of saving the entry.

2. Create an Action to Set SL NO:

   • Go to the app editor.
   • Navigate to the “Behavior” section and create a new action.
   • Set the action to be triggered on form save.
   • Use the expression MAX(TEST[SL NO]) + 1 to set the next serial number.

3. Workflow Rule to Trigger the Action:

   • Create a workflow rule that triggers when a new record is added.
   • Set the action you created above to be executed when the workflow rule is triggered.

4. Transaction Locking Mechanism: To prevent race conditions where multiple users might try to save at the same time and get the same SL NO, consider implementing a locking mechanism. This can be done by creating a “Lock” table with a single record that you update during the transaction. Here’s a step-by-step guide:

   • Create a new table called “LockTable” with a single column “Locked” (Boolean).
   • Before starting a transaction, update the “Locked” field to TRUE.
   • Check if the “Locked” field is TRUE before proceeding. If it is, wait for a short period and retry.
   • Once the “Locked” field is FALSE, proceed with your transaction.
   • After the SL NO is assigned, set the “Locked” field back to FALSE.

5. Implementing the Locking Mechanism:

   • Step 1: Create the LockTable

     CREATE TABLE LockTable ( Locked BOOLEAN DEFAULT FALSE );

   • Step 2: Create an action to lock the table before saving

     appsheet

     UPDATE LockTable SET Locked = TRUE WHERE Locked = FALSE;

   • Step 3: Create a conditional workflow that checks the Locked status before allowing the save

     appsheet

     IF( SELECT(LockTable[Locked], [Locked] = TRUE), WAIT, MAX(TEST[SL NO]) + 1 );

   • Step 4: Create an action to unlock the table after saving

     appsheet

     UPDATE LockTable SET Locked = FALSE WHERE Locked = TRUE;

By using the workflow and action mechanism, along with a locking mechanism, you can avoid duplicate entries and ensure sequential serial numbers even when multiple users are using the app simultaneously.

[scott192]

A few seconds. But the main thing is that there is a clear handover from the appsheet client to the script and the script is in control over which number is returned.

[jaichith]

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

[MiguelMx]

Many trials. But finally i have a solution. No matter how many users or if you delete rows. Serial number not will be affected.

Not bots are required. Just a virtual column with the next formula:

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)

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 4: If there are no deleted rows, just add 1 to the consecutive

[jaichith]

Name of virtual column is [SL NO]? Some thing other than [ SL NO ]

[MiguelMx]

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.

[MiguelMx]

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.