How to implement folder browsing with multi select?

[kiwipeet]

Hi all.

I'm working on a data conversion project for a a document management system. We have tens of thousands of folders to map from the old system to the new. I'd like to build a basic form which allows users to browse, filter and select folders in 2 panes. to assist mapping from old to new.

The left pane would allow multiselect, the right pane a single select. When the user clicks ok, the result is stored in a table, or a table is updated.

The folder information (old & new) is the result of SQL Server queries. but ideally i'd like to display this as folders.

I'm a novice when it comes to forms and programming. I'd appreciate any advice from users about the best way to achieve this. Is this doable? or rocket science?

Any suggestions or advice would be greatly appreciated.

Regards

Peter

 

[CJ_London]

It is certainly doable and need not be complex but would need to know more before being able to provide a full answer.

Where are you starting from? A blank screen or do you already have the folders already mapped into a table?

Another concern is you mention tens of thousands of folders - a multiselect list is not an ideal way to handle this number of entries, you would have to use a subform and you can't multiselect a subform, but you can filter and sort.

Can you be more specific about what you mean by folder - ideally provide some examples of 'old folder' definition and 'new folder' definition.

 

[kiwipeet]

Thanks for your quick response.

It's pretty common that document management systems allow filing/classification using a folder concept. So they appear like directory tree structure, (see attached) but it truth the 'folders' are simply data in tables. It follows a pretty basic parent child hierarchy. (It's a single table self join. Think common HR schema with employee /manager relationship).

I can select/manipulate the data from SQL Server so it will be returned looking something like:

Level 1 folder ABC, Level 2 folder DEF
Level 1 folder ABC, Level 2 folder DEF, Level 3 folder JKL
Level 1 folder ABC, Level 2 folder GHI
Level1 folder ABC, Level 2 folder GHI

Each folder has a text description and an id. Several folders from the old system will be be mapped to single folders in the new system.

In the example above. I would want to be able to select folder DEF from the old system and a folder 'XYZ' in the new system, and then click save/update. The tool would update a field "mapped_to" in the old system with 'XYZ' or insert to a table that says DEF->XYZ.

Once that folder has been mapped, it would be removed from the list of folders that need to be mapped. (select list where is-mapped is null)

In short I'm trying to build something that will be familiar looking to users, (mimicking folders) So we can get several people filtering through the data, and mapping from old to new. Hopefully avoiding double handling.

 

[kiwipeet]

Now I think about it, even showing the data in a table layout would probably be ok, rather than folder/trees (if that's too hard.)

 

[CJ_London]

I think you need to copy your folder tree into a table as a starting point - not sure how you could update the tree with 'mapped to' except by changing the folder name.

Presumably you would also need an 'unmap' facility to correct errors.

Is your old tree stable - i.e. not subject to any changes to folder names, or additions/deletions. If yes then the tree can be copied to a table, if no then still copy but would need an update routine as well.

Suggest you would need to store the folder 'path' as well as the folder name - in that way you can mimic a tree.

I have a document management system used in a variety applications, but it does work off the windows folder system rather than a SQl server tree - but could be adapted if you can get your tree into a table.

Will your users be starting with a 'new' folder and go looking for the 'old' folders or the other way round?

 

[kiwipeet]

I think you need to copy your folder tree into a table as a starting point - not sure how you could update the tree with 'mapped to' except by changing the folder name.

The current 'Folder' structure is already in a table. I can extract/convert/manipulate it however I like, and place results in a table for my purposes. For simplicity lets assume that I have only 2 tables, the first contains a list of old folders, and teh 2nd contains a list of new folders. Each folder has it's own unique id.

My idea is to add an extra field on the table of old folders called "mapped to" and set that to be the id of the new target folder.

The user would select a dozen folders on the left, and select a single folder on the right. Click "Go/map/make it so/Kazam!". The magic code behind the button would update the "mapped to" field for the dozen folders in the 'old system' to the the id of the new 'target' folder.

During migration with the use of cunning queries the migration tool can map files from their old folder to their new intended target.

Presumably you would also need an 'unmap' facility to correct errors.

Good idea. I hadn't got that far yet. To 'unmap', i'd just need to set the field to null.

Is your old tree stable - i.e. not subject to any changes to folder names, or additions/deletions. If yes then the tree can be copied to a table, if no then still copy but would need an update routine as well.

Yes it's stable. It's under change control. uses can't move things without admin assistance.

Suggest you would need to store the folder 'path' as well as the folder name - in that way you can mimic a tree.

Yep.. and that's really where i'm at... how I would display the data so that it has the appearance of a folder tree, and allow multi-select

Will your users be starting with a 'new' folder and go looking for the 'old' folders or the other way round?

Good question. I would expect they'd work from old to new.

The underlying tables and process i'm happy with. It's making a user interface that I have no clue about. I guess I was hoping to a few pointers about what objects/controls I could use to do this, and a bit of feedback about whether it would be a relatively straightforward thing to achieve, or whether it would be a complex project.

Thanks again for your advice and input. Much appreciated.

 

[CJ_London]

OK, Sounds like datawise you are good to go.

But first I need to know the table structure for both old and new tree tables (ie. field name, field type and ideally a small example of contents or expected contents). It will make subsequent advice that much easier

i.e.Note - wrap in code tags to preserve formatting

[U]TblOldTree[/U]
[U]FieldName    FieldType      Example[/U]
OldTreeID    AutoNumber  1234
OTPath       Text            ABC\DEF
OTName      Text            GHI
NewTreeFK   Long            9876
 
[U]TblNewTree[/U]
[U]FieldName     FieldType      Example[/U]
NewTreePK    AutoNumber  9876
NTPath         Text             STU
NTName        Text              XYZ

Need to see if you are using recursive ownership i.e. parent/child for the 'path' and you have contradicted yourself a little bit so need to double check your meaning

This implies that they start with new and go find the old

The user would select a dozen folders on the left, and select a single folder on the right

But then you say

I would expect they'd work from old to new.

which implies they find an old folder and go find the new, so multiselect not really required

As explained in an earlier post, I don't think a multiselect list is the way to go but I'll give you two options to try for selecting. Once that is agreed, the rest is pretty straightforward.

 

[kiwipeet]

Thank you very much for your input.

As this project has developed although my underlying requirement is still that same, How I view the problem has changed.

My original request would have been the 'gold plated version'. But I believe the tool I need to do the job can be simplified a great deal.

I'm going to start a new thread with a new simplified approach.

Thanks again for your help.

Regards

Pete