Ideas on flagging a row of a table, if there is an expected change to it? (1 Viewer)

[Sampoline]

Yeah I understand that @Pat Hartman so here is how things are working at the moment.

So there will be some photoshop work done on the images. The person doing the edit work will quality check if the original image is good or not first of all. If the image wasn't camera captured correctly, then they would inform the photographer of an issue. Then that original image would then be replaced by another one. So essentially it becomes a rescan.

I want to see this in the database. So that the editor is confirming the requirement for the replacement. So I wanted the editor to see both the original and the replacement in two different tables to compare and confirm the changes. There have been instances of miscommunication and the replacement wasn't required to be made. In that case, the original remains. That's why I feel having both tables would help. So when the date last modified changes, it logs into that 2nd table. Then editor would check and confirm, or simply delete that row from the table.

This is sort of the requirement I was given by my higher ups too when I explained the situation. Hence why I wanted the two tables, it just seemed to make logical sense to all parties involved.

 

[gemma-the-husky]

Yeah I understand that @Pat Hartman so here is how things are working at the moment.

So there will be some photoshop work done on the images. The person doing the edit work will quality check if the original image is good or not first of all. If the image wasn't camera captured correctly, then they would inform the photographer of an issue. Then that original image would then be replaced by another one. So essentially it becomes a rescan.

I want to see this in the database. So that the editor is confirming the requirement for the replacement. So I wanted the editor to see both the original and the replacement in two different tables to compare and confirm the changes. There have been instances of miscommunication and the replacement wasn't required to be made. In that case, the original remains. That's why I feel having both tables would help. So when the date last modified changes, it logs into that 2nd table. Then editor would check and confirm, or simply delete that row from the table.

This is sort of the requirement I was given by my higher ups too when I explained the situation. Hence why I wanted the two tables, it just seemed to make logical sense to all parties involved.

Surely it would be better to renumber the new file
Make the first image file 1234-A. jpg, and revisions renumbered with a different suffix so you get versions

1234-A
1224-B
1234-C etc

Then for image 1235 you get

1235-A
1225-B etc

and it becomes easy to see what's going on. Releasing new versions is much better than editing existing versions. This way, you don't have to examine the changes personally. You can just get the person who released the changes to annotate the database.

 

[Sampoline]

Surely it would be better to renumber the new file
Make the first image file 1234-A. jpg, and revisions renumbered with a different suffix so you get versions

1234-A
1224-B
1234-C etc

Then for image 1235 you get

1235-A
1225-B etc

and it becomes easy to see what's going on. Releasing new versions is much better than editing existing versions. This way, you don't have to examine the changes personally. You can just get the person who released the changes to annotate the database.

Yeah Gemma you're making a lot of sense. I was instructed that only the supervisor of the project and the editor were going to be reviewing this database. So that's why I was trying to figure out how they could know if the change being made is required or not. To give you more context, I have file movement process ongoing too, Once the image is made, the file(s) is copied from the imaging folder to a separate location. The file(s) in the imaging folder are then moved to the folder that the editor works on. So there is always a copy of the original available.

Say that a rescan is required, the editor will delete those images from their folder and the photographer will reshoot and it will go into the imaging folder as usual. Then when I run the file movement, this image will copy again to the separate location and now the original image will move to a subfolder with the create date and time added to the filename.

The editor will work with this information and review the original and rescan images in the copy folder; deleting the one he doesn't require henceforth. And this keeps repeating.

What do you suggest in this case? Because I was told to do it the two table method. And I'm kinda pressed about getting it done, just stuck on the VBA of it.

 

[arnelgp]

check and test.

 

[Sampoline]

check and test.

Thanks so much Arnel, I will run some tests throughout the day to see its functionality and report right back to you. Thanks for this!

 

[gemma-the-husky]

'just the bottom of the quote

What do you suggest in this case? Because I was told to do it the two table method. And I'm kinda pressed about getting it done, just stuck on the VBA of it.

Hopefully @arnelgp has just given you a practical solution

I just think have to re-examine images that might have changed in the past tales you down a very difficult cycle. I think your organisation needs to find a way that works, that hopefully doesn't involve changing data that has already been managed and processed. I think we thought you were talking about files of data, such as invoices. However even though you are talking about single images, the same principles apply. The current image ought to be the one with the highest version, and all you need is a way to record the changes and reasons for anyone interested in tracing the development. That's why there really needs to be no question of modifying work that has been previously issued as "fit for processing". It's a matter of version management. Point out that it is next to impossible to examine imagines and spot the changes. There are whole quiz books of spot the difference comparisons.

I have a system that saves versions of quotations, Every time a new item gets added, or a price changed, a new version of the quote, with a new version number gets stored. Anyone who is interested can go back and see the changes. I didn't actually track the actual changes, although I could have. What users couldn't do is just change an old version. Any change to the active data would produce a new version. I suppose I could have kept an audit trail of changes, and then played the whole thing it back as a sort of "game history", but then you don't get the historical snapshots. I didn't think of the alternative at the time. In practice the old versions are hardly ever looked at anyway.

 

[Sampoline]

Hopefully @arnelgp has just given you a practical solution

I just think have to re-examine images that might have changed in the past tales you down a very difficult cycle. I think your organisation needs to find a way that works, that hopefully doesn't involve changing data that has already been managed and processed. I think we thought you were talking about files of data, such as invoices. However even though you are talking about single images, the same principles apply. The current image ought to be the one with the highest version, and all you need is a way to record the changes and reasons for anyone interested in tracing the development. That's why there really needs to be no question of modifying work that has been previously issued as "fit for processing". It's a matter of version management. Point out that it is next to impossible to examine imagines and spot the changes. There are whole quiz books of spot the difference comparisons.

I have a system that saves versions of quotations, Every time a new item gets added, or a price changed, a new version of the quote, with a new version number gets stored. Anyone who is interested can go back and see the changes. I didn't actually track the actual changes, although I could have. What users couldn't do is just change an old version. Any change to the active data would produce a new version. I suppose I could have kept an audit trail of changes, and then played the whole thing it back as a sort of "game history", but then you don't get the historical snapshots. I didn't think of the alternative at the time. In practice the old versions are hardly ever looked at anyway.

Yep I will run some tests tomorrow morning and give @arnelgp the all clear when it all works.

But you're completely on point Gemma. I will express this concern to my organisation. I also agree with you that we should only be processing the newest or highest version of a file. Sorry I didn't mention earlier that I was talking about single images, but your point still applies. The organisation's idea is to mitigate miscommunication between the imaging and editing team. Previous batches, we had issues with the metadata. The client requested EXIF data to be consistent from it's capture/shoot to it's final output. There was some inconsistencies between different stages of image processing in the metadata. Hence why I've been requested to run metadata checks on the different stages of processing (after imaging in it's directory folder, after a copy is made in a separate folder location for preservation purposes, after the master is moved to the editing folder and when it is finally in the delivery folder).

The organisation wants to observe at what stage the EXIF metdata was changing. And I guess to further micromanage this, they wanted to know when the Last Date Modified had changed in those images.

 

[gemma-the-husky]

Could you have a recheck option in your programme, then? When you import the file, store the metadata.
Then on request check the metadata to see if it's been changed from when you imported it. If it has, then insist on the file name being changed, rather than trying to identify the specific amendments.

 

[Sampoline]

Could you have a recheck option in your programme, then? When you import the file, store the metadata.
Then on request check the metadata to see if it's been changed from when you imported it. If it has, then insist on the file name being changed, rather than trying to identify the specific amendments.

Hmm, I guess I could, but how would I do that exactly? Is it like a yes/no column that gets unchecked?

 

[Sampoline]

check and test.

Hi @arnelgp I ran some tests today. I deliberately manipulated an image so that the last date modified would change from the previous import, and then ran the import again. The import did not log the changed row into the "tblDirectoryExceptions".

Just a thought, does the date/time format that the data is being captured as matter?
For example I'm getting in Access as DD/MM/YYYY HH:MM:SS AM/PM
e.g. 15/02/2021 2:04:44 PM - but in the file properties of the image, they read as Monday, 15 February 2021, 2:04:44 PM

 

[arnelgp]

test this again.

 

[Sampoline]

test this again.

Hi Arnel, it still isn't logging into "tblDirectoryExceptions".

To test it out, all I did was import the directory fresh. It imports all the images metadata correctly into "tblDirectoryDefault".
Then on one of the images, I rotated and saved it. Changing the last date modified,. Then re-run the import but it doesn't log this into "tblDirectoryExceptions" as expected.

For your reference, I have added a form with a button to run the import:

Dim StrF As String

StrF = "(WHATEVER THE FOLDER LOCATION)"
If StrF = "" Then Exit Sub

sFolderList StrF, True, "tif,jpg"

 

[arnelgp]

i just tested it a 2nd time.
note: i comment out this line:

'If Dict.Exists(MyFile.Path) = False And InStr(MyFile.Path, "thumbs.db") = 0 Then


test it once more.

 

[Sampoline]

i just tested it a 2nd time.
note: i comment out this line:

'If Dict.Exists(MyFile.Path) = False And InStr(MyFile.Path, "thumbs.db") = 0 Then


test it once more.

Hi Arnel, I'm at home at the moment and testing it from some mock jpg and png files it seems to be working. When I head into work tomorrow to check on the TIF's, I'll see if all is good.

So assuming all is well, is there a reason why:

'If Dict.Exists(MyFile.Path) = False And InStr(MyFile.Path, "thumbs.db") = 0 Then

What exactly could be causing the issue? And would I be catching thumbs.db if I avoid this step?

 

[arnelgp]

if you are using the syntax as in your post #32 (you supply "tif,jpg")
then thumbs.db will not be included because the extension is db.

 

[gemma-the-husky]

Here's another small demo, based on another recent thread.

This thread was to import a particular file structure
QAN files | Access World Forums (access-programmers.co.uk)

In the above thread, the user wanted to import text files with the extension ".QAN"
I did a demo to import files with that extension. The demo file he added was QB100833.QAN
I have just added functionality to check if files had already been imported, and report any differences if they had.
This is only using vba. There is no other external library. Put the database and the test files into in a single folder.

I have copied the file QB100833.QAN to 2 other file numbers.

This will now check for any new .QAN files, import them, but will report whether the file date time has changed for any previous imports. I presume if anything else has changed, the filedatetime must also change. VBA will also give you the filesize, but I don't think it gets other stuff. I imagine you could get other file details with fso.

So there are 2 functions in module1

loadfiles, which checks the project folder for QAN files
importfile which processes a new file into the database. The import of a single file takes a fraction of a second

If you change some of the numbers against the chemical names in one of the QAN file the filedate will change, and the import process will pick up the change. Be careful what you change. Changing some details will cause the import to fail, as it is expecting a particular layout, and I didn't add any error handling.

Perhaps the same sort of thing would work for your images. You would need to change the actual import process to match your actual files.

 

[Sampoline]

if you are using the syntax as in your post #32 (you supply "tif,jpg")
then thumbs.db will not be included because the extension is db.

That's a good point. Thanks Arnel. Will update again tomorrow on my test. But things are promising. Thankyou.

 

[Sampoline]

Very in-depth explanation. Thanks Gemma. This will be my homework for the weekend. More than simply giving an organisation a product, I'm more so interested on a personal level to learn and improve my knowledge on databases and code. I've only been doing this for no more than 6 months, but the amount I've learned simply from asking and listening has been great.

I'm still a newbie and can get lost quite a lot, asking for solutions quite often here. But I really like how you all like to challenge the ideas of people, rather than simply giving quick fixes. I'm not always on the same page when the pros are explaining to me, but tbh that's the only way I've learned this much in this short span of time.

Thankyou. I'll play around with your db and keep you posted on any questions I have. Which I probably will lol.

Here's another small demo, based on another recent thread.

This thread was to import a particular file structure
QAN files | Access World Forums (access-programmers.co.uk)

In the above thread, the user wanted to import text files with the extension ".QAN"
I did a demo to import files with that extension. The demo file he added was QB100833.QAN
I have just added functionality to check if files had already been imported, and report any differences if they had.
This is only using vba. There is no other external library. Put the database and the test files into in a single folder.

I have copied the file QB100833.QAN to 2 other file numbers.

This will now check for any new .QAN files, import them, but will report whether the file date time has changed for any previous imports. I presume if anything else has changed, the filedatetime must also change. VBA will also give you the filesize, but I don't think it gets other stuff. I imagine you could get other file details with fso.

So there are 2 functions in module1

loadfiles, which checks the project folder for QAN files
importfile which processes a new file into the database. The import of a single file takes a fraction of a second

If you change some of the numbers against the chemical names in one of the QAN file the filedate will change, and the import process will pick up the change. Be careful what you change. Changing some details will cause the import to fail, as it is expecting a particular layout.

Perhaps the same sort of thing would work for your images. You would need to change the actual import process to match your actual files.