Tips & Tricks – Moving Survey Images/Attachments into the Default Fields Using SQL

Back to blog home

September 19, 2018 | Krzysztof Tchorzewski

This blog is about InfoNet, now known as InfoAsset Manager.

InfoNet will store an unlimited number of images and attachments per object. However there are old default fixed attachment fields, these have a green background to the data row.

For some users their exports or reports are set to pull attachments from the default ‘green’ fields.

screenshot of InfoAsset manhole survey screen - before migrating files

Manhole Survey Attachments before migrating files into the default fields.

If all the survey files have been imported into InfoNet as attachments in the ‘white fields’ but are needed to be in the default fields, they can be migrated into the default fields using SQL.

If there are multiple attachments for the survey with the same purpose set this process will take the first in the Attachments list with that purpose.

1. With the relevant network open, open the SQL Select window, set the ‘Object Type’ to the required survey (e.g. “Manhole Survey”), copy & paste the relevant sets of SQL lines below for the attachments which require migrating, update the text in the square parentheses ([**Survey type**]) to match the ‘Object Type’ (e.g. [Manhole Survey]), then press Apply or Run to perform the update.

Screenshot from InfoAsset showing the SQL commands required

The SQL commands entered to migrate the Manhole Survey attachments.

2. Once the SQL command has run, check the properties for the surveys which should have been updated, the attachments will now have moved into the desired locations on the Attachments tab.

3. If anything appears incorrect use Edit & Undo (Ctrl + Z) to return the fields back to their previous state.

A. If you only wanted to copy the files into the green fields and retain the original file references as they were, simply omit the lines of SQL which begin with ‘DELETE ALL FROM’.

B. Subsequently if the DELETE commands are omitted initially, they can then be run separately after the SET commands have run, i.e. after checking the files have moved into the ‘green fields’, to then remove the now duplicated file records.

Screenshot of InfoAsset showing post migration manhole survey screen

Manhole Survey Attachments after migrating files into the default fields using SQL.

Purpose = Location Photo

SET location_photo=attachments.db_ref WHERE (attachments.purpose="Location Photo" AND location_photo=NULL);
SET location_photo_filename=attachments.filename WHERE (attachments.purpose="Location Photo" AND location_photo_filename=NULL);
SET location_photo_ref=attachments.description WHERE (attachments.purpose="Location Photo" AND location_photo_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=location_photo AND attachments.filename=location_photo_filename AND attachments.description=location_photo_ref);

Purpose = Location Sketch

SET location_sketch=attachments.db_ref WHERE (attachments.purpose="Location Sketch" AND location_sketch=NULL);
SET location_sketch_filename=attachments.filename WHERE (attachments.purpose="Location Sketch" AND location_sketch_filename=NULL);
SET location_sketch_ref=attachments.description WHERE (attachments.purpose="Location Sketch" AND location_sketch_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=location_sketch AND attachments.filename=location_sketch_filename AND attachments.description=location_sketch_ref);

Purpose = Internal View

SET internal_image=attachments.db_ref WHERE (attachments.purpose="Internal View" AND internal_image=NULL);
SET internal_image_filename=attachments.filename WHERE (attachments.purpose="Internal View" AND internal_image_filename=NULL);
SET internal_image_ref=attachments.description WHERE (attachments.purpose="Internal View" AND internal_image_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=internal_image AND attachments.filename=internal_image_filename AND attachments.description=internal_image_ref);

Purpose = Location View

SET location_image=attachments.db_ref WHERE (attachments.purpose="Location View" AND location_image=NULL);
SET location_image_filename=attachments.filename WHERE (attachments.purpose="Location View" AND location_image_filename=NULL);
SET location_image_ref=attachments.description WHERE (attachments.purpose="Location View" AND location_image_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=location_image AND attachments.filename=location_image_filename AND attachments.description=location_image_ref);

Purpose = Plan Sketch

SET plan_sketch=attachments.db_ref WHERE (attachments.purpose="Plan Sketch" AND plan_sketch=NULL);
SET plan_sketch_filename=attachments.filename WHERE (attachments.purpose="Plan Sketch" AND plan_sketch_filename=NULL);
SET plan_sketch_ref=attachments.description WHERE (attachments.purpose="Plan Sketch" AND plan_sketch_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=plan_sketch AND attachments.filename=plan_sketch_filename AND attachments.description=plan_sketch_ref);

Purpose = Photo

SET photo=attachments.db_ref WHERE (attachments.purpose="Photo" AND photo=NULL);
SET photo_filename=attachments.filename WHERE (attachments.purpose="Photo" AND photo_filename=NULL);
SET photo_ref=attachments.description WHERE (attachments.purpose="Photo" AND photo_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=photo AND attachments.filename=photo_filename AND attachments.description=photo_ref);

Purpose = Sketch

SET sketch=attachments.db_ref WHERE (attachments.purpose="Sketch" AND sketch=NULL);
SET sketch_filename=attachments.filename WHERE (attachments.purpose="Sketch" AND sketch_filename=NULL);
SET sketch_ref=attachments.description WHERE (attachments.purpose="Sketch" AND sketch_ref=NULL);
DELETE ALL FROM [**Survey type**].attachments WHERE (attachments.db_ref=sketch AND attachments.filename=sketch_filename AND attachments.description=sketch_ref)

Find out more

Contact us

Tags: InfoAsset Manager, SQL, survey

About the Authors

Krzysztof Tchorzewski

Krzysztof Tchorzewski

Technical Product Engineer


Krzysztof works in the Innovyze Client Services team, specializing in supporting customers with asset management solutions.