Possible race condition?

1minManager · 01-19-2021 04:12 AM

I have an App used by a construction co. Basically what this bit does it allow the app user to add the items they have fitted. Each different item is added as its own record. This formula in [Number Remaining] stops the user from saying they’ve fitted 6 items, from a inital total of 10, if they have already fitted say 6. In this case if they’d fitted 6 and put in another 6 then [Number Remaining] would be -2 and trigger a seperate Valid_If formula

The issue I have is somehow the user has added a heating cyclinder twice. When there is only 1 in total. I cannot see how this has happened, nor replicate it.

My hunch is its somehow to do with the Select() reading the table, but not the current record. Or how this all works if the internet is not behaving. This is the abrevated formula:

ANY(Select(Labour[Farrier],AND([Category]=[_ThisRow].[Category],[Task]=[_ThisRow].[Task])))
Get the total number of this item for this house type

-
SUM(Select(TimesheetCompleted[Number Complete],AND(
[Plot]=[_ThisRow].[Plot],
[Category]=[_ThisRow].[Category],
[Task]=[_ThisRow].[Task])
)
))
deduct off any records already saved to the table

+[_ThisRow].[Number Complete]
add the number of items in this record

Any ideas or suggestions?

Rafael_ANEIC-PY

Hello @1minManager , i’m building a similar app i believe, and i’ve found it useful to remove the user ability to make duplicate entries of items, since that can lead to some really unexpected behaviour in my experience. I’m not sure if this would be viable for your app design tho.

Also i recall reading a few times that the ANY() function can be quite unpredictable, I advise switching it for an index() function instead, which is said to never have any problems at all.

1minManager

The duplicate suggestion wouldn’t work. If there are 6 items its required they be able to fit one item per day until they are done

Rafael_ANEIC-PY

How about limiting duplicate items per day instead?

Yeah, if they both were saved in a short span of time there’s a chance Appsheet didn’t catch up in time to stop them both from being saved

1minManager

While I remember as well. The two records in question are exactly 1min and about 50m apart. So the only way I know this could potentially happen is if they had 2 phones with the same login

Steve

One user’s app cannot see any changes made from another user’s app until a sync occurs. If two users allocate the same component, the over-allocation will only be seen by both after three syncs:

User 1 syncs.
User 2 syncs, gets user 1’s allocation, can see over-allocation.
User 1 syncs again, gets user 2’s allocation, can see over-allocation.

There is no way to avoid this.

1minManager

Understood, but this is an issue with just one user.

Austin

A clarification on Steve’s statement: These would be devices not necessarily Users per say so 2 phones, a phone+tablet, tablet+laptop, etc. and depending on how you have the offline/sync tab set up this issue could occur even days apart.

1minManager

The two records in question had both a Initial_Value = Now(), Change_Timestamp and a Change_Location column . Showing that they were created 1 minute and about 10m apart. Google Sheets cell history function matches what Appsheet has recorded in terms of timings.

Does Appsheet have anway of record device info so that we can deduce if two devices are being used?

Steve

CONTEXT("Device")

1minManager

Isn’t there a way to pull in the app version as well? Searched but couldn’t find anything

Steve

Nope; the app version is not available programmatically.

Austin

A solution would be a sort of staging table that works via workflows cause those will prohibit an add if it breaks any of the valid ifs where as a form I believe is checked locally whether it is valid to add.

1minManager

So an update on this. Its happened again. The user has confirmed he’s only using one device.

His process was:
1 = He created a TimesheetCompleted table record
2 = He went a view called “My Timesheets” the TimesheetCompleted table records weren’t there
3 = So he then added another record and it let him

In this setup there are 2 relevant tables. Timesheet is where they give me a clock in & clock out time. A subtable of that is TimesheetCompleted which records the individual work items they completed that day.

When he went into create the second record where [Task]=“Gutter rise & fall high level” the first thing that should have stopped him was the suggested value for TimesheetCompleted[Tasks]. Here is its abbreviated formula:

IFS(
…
[Timesheet].[House Type]=“Farrier”,Select(Labour[Task],AND([Category]=[_ThisRow].[Category],[Farrier]>0)),
…
)
-
Select(TimesheetCompleted[Task],AND(
[Plot]=[_ThisRow].[Plot],
[Category]=[_ThisRow].[Category],
[TimesheetCompletedID]<>[_ThisRow].[TimesheetCompletedID],
[Number Remaining]=0,
ISBLANK([Apprenticed To])
))

So it basically says, goto Labour[Task] and create a list of tasks where there is more than one for this house type. Then minus off all the completed tasks except ones done by apprentices. You can see from the spreadsheet photo he already booked all 16 [Task]=“Gutter rise & fall high level”. So i cannot see why it even let him choose that again from this list.

I’ve tried to diagnose using airplane mode and even logging in as that user. But I cannot replicate it. I’ve also logged into those records and the app will not let me save the record because it can see I’m trying to book 32 items where only 16 existed.

Anyone any other ideas?

Austin

I think step 2 is the most important part here? Appsheet makes the changes available instantly on the local device so even if it doesnt sync that record should still show up in the table.

Are these formulas using slices?

1minManager

Hi @Austin_Lambeth Austin

Yeah I thought that was weird too. Yes that view is using a slice. Also it relies on a virtual column in the Timesheet table to SUM( the TimesheetCompleted(Payment] column.

But even with this, does it explain why the TimesheetCompleted[Task] column would ignore a record youv’e just created in its Suggested formula?

Austin

It could, the likely cause is an effect where it is recalculating the value when they go to create a new record. To try and test for yourself I would make an entry and very quickly try again to make it. Would test with and without connection(don’t know if that would make a difference but could).
I guess you would technically call this a race condition. A possible solution depending on the speed of your app would be to sync on save of the form to create this entry as that will force it to acknowledge the first entry.

1minManager

I have tried this both with and without airplane mode on and cannot get it to fail

1minManager

This is not really practical as the sync time is 30-40 secs (its a BIG app!)

Austin

oh jeez. I now noticed your screen shot also shows a 3 minute difference in time. I really am stumped by this. My gut says this isn’t likely a “bug” per say but more likely a thing in the formula that we can’t seem to notice.