Handle conflicting changes

[Deamo]

I'm looking for help on the best way to handle a conflicting change scenario in my database

In the database, there are 3 users that have the ability to export a list of jobs, make changes and import that spreadsheet again -- everyone else can only see the forms/ reports. This (I believe) is going to be where most conflicts appear.

The reason they need to export & import the list, is due (in part) to the way in which invoices are received from our contractors - they send a list of jobs they've completed as a spreadsheet, which is then compared to our entire list to see if it's been paid.. the format of these spreadsheets vary to a large degree.

I'd rather not implement a record lock on the form, as that would then rely on the user to check that their changes were applied.

What other options are there?

 

[jdraw]

I'd suggest some standardized procedures and formats would be first on my list.

 

[Deamo]

It is on mine as well. I've repeatedly stated as much.
I've been told that to do so it too cost-prohibitive currently (requires customer management system changes at our end too), given all other changes that are happening.

My database is basically a stop-gap measure until a proper permanent solution is developed - currently the users have to make do with a shared excel spreadsheet, with all the trouble that creates.

I've tried using a me.requery to update the form with any changes prior to saving the record with no luck

 

[jdraw]

Perhaps a simple procedure. 3 users have the ability to export, modify and re-import data.

Divide the incoming spreadsheet into 3, assign the first 1/3 to special user1, second 1/3 to special user2 etc.
Do something to prevent multiple changes by multiple people. Organize the "authoritative records" by some repeatable means. You have chaos at present where each special user can modify records unknown to the other special users.

Or, take the original speadsheet and divide it on some basis so that you know who will be working on what.

This is NOT a Me.Requery problem at the moment.

What is the impact on your business because of the current procedures?

What resources are committed to the "proper permanent solution"?

 

[Deamo]

Sorry, more clarification.. There will be roughly 19 users that will be using the database (not necessarily all at the same time).
3 of those users will be able to export/re-import. All others are restricted to the forms.

The import only updates 1 field in the database - InvPd

I was shot down trying to have everyone make changes through forms, as that would make for more work when checking the invoices to be paid.

Current issues:
- Overlapping job details for same reference number - users just select the next unused number in the spreadsheet
- Incorrect/ missing details
- Sharing violations crashing the spreadsheet/ corrupted spreadsheets
- Spreadsheet size, currently ~12MB

I was initially approached to see about adding some restricitions to the existing spreadsheet, to standardise it to a degree. Given the state it was in at the time, i suggested it be replaced by a database.

As for the permanent solution - unknown

 

[jdraw]

What is the impact on your business because of the current procedures?

What resources are committed to the "proper permanent solution"?

Usually an organization that can not manage its finances is on a one way path to destruction. For an organization to knowingly accept and continue with the status quo, as you have described, is pure folly. It would appear that a good audit of their finances and related practices is warranted and hopefully not too late.

Good luck.

 

[Deamo]

Found a workable solution for now

When the user clicks to save their changes and close the form, I'm comparing the current and old values of the required field against a lookup to the table, and if either of those don't match then any changes made on the form are undone, the form is refreshed and the user is prompted to check and re-update as necessary.