Form/Listbox Form Adding/Assigning Million of Records? 1

[misscrf]

I have an application with projects. In each project, there can be multiple pieces of media. We upload 2 inventories of that media. One is at a folder level and one at a file level. One piece of media could be a TB HDD. A subset of the media is what sources are on it. I have the front end interface set up with a main form for media, and a sub form tab for sources. Each source on a set of media is constituted of 1 or more files on the media.

My challenge is to create an interface where I can view the media inventory and assign 1 or more files or folders to a source. I'm facing a couple if issues.

1) how to use either a listbox or datasheet/continuous form to show potentially millions of inventory file records, with the option to view the folder level or file level detail

2) how to allow a user to select multiple inventory records, and "assign" them to a source. I have the back end table structure set up, but the front end interface is where I am just not sure how to handle

3) how to show the inventory records already assigned to another source

4) how to re-assign inventory records to a different source

5) how to assign inventory records from the folder level and have the file records, that would be in that folder structure, also get assigned (and vise versa)

Does this make sense?

Any ideas, examples or suggestions are greatly appreciated!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Can you be a little clearer? I cannot really picture what you mean by file, folder, source, and inventory item. I think there is a hierrarchy of these items but cannot tell what it is.
No one can use a million record list, so you would need a way to pull subgroups. I often use a treeview for things like this, and only populate the nodes when I expand the list. Probably some understanding of the table structure would help. Also be very specific on how a user would search find an item and then assign it. The trick is not to return a million records, but provide quick ways to search and display and then "Move".

 

[misscrf]

Thank you for the response. I apologize if I was vague. let me break down the table structure for you.
tblProject
[ID]
,[PName]
,[PStartDate]
,[PNotes]

tblMediaTracking
[ID]
,[PKProject]
,[FKMediaType]
,[DateReceived]
,[Description]
,[MediaTag]
,[TotalSize]
,[UsedSize]

tblSourceDiscovery
[ID]
,[FKMedia] (tblMediaTracking.ID)
,[SourceTag]
,[Description]
,[SourceSize]
,[Notes]

tblFileList
[ID]
,[FKMedia] (tblMediaTracking.ID)
,[FileName]
,[Extension]
,[FileSize]
,[ModifiedDate]
,[FullPathName]
,[FullPathMAX]

tblFolderList
[ID]
,[FKMedia] (tblMediaTracking.ID)
,[FolderName]
,[FileCount]
,[FolderSize]
,[ModifiedDate]
,[Path]

tblSourceInventory
[ID]
,[FKSource]
,[FKFileID]


The tables are in a SQL database, and linked to an Access app for the front end. So now for the front end info. I have a look up form for choosing a project. Once a project is chosen (combination of a text box to type a sub-string in, which populates a list box with matches), a user can go view the media that has been received for that project. For each piece of media, you can click a button on the media details form, to import the inventory. The inventory flat file is validated and brought in, separating records into a folder list and a file list.

On the media form, there is a tab control with a sub form for "sources". Without getting bogged down in too many details, a source is a subset of the files on that media. One file could be assigned to multiple sources. What I need to figure out, is how to create an interface that will allow users to easily manage this. Some will want to go in and do any number of things:

1) filter/search/sort file or folder inventory to identify source records to assign

2) be able to shift + select or ctrl + select, etc to assign multiple inventory records to a source

3) if folder view is chosen and assigned from - replicate assignment to the file level

4) show inventory records already assigned to any other sources in that media

The tblFolderList and tblFileList are the inventory tables. From the media or source level, end users need a GUI to view, filter and sort the file or folder list and then assign subsets of records to a source. Ideally, I would also be able to show if a given file or folder is assigned to any sources already. In reality, it's just about assigning foreign keys into the tblSourceInventory table. The hard part is translating that into a GUI that isn't frustrating to use. The users are accustomed to dealing with spreadsheets, so they can filter, sort and just highlight the rows that they want. I need to come up with a way to create similar functionality. Not the same, I'm not trying to create excel in access. Just the same functionality, the same effectiveness at letting a user bulk select and assign rows to a foreign key, etc.

does all that make sense? lol



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

I would think you could do this with a multicolumn, multiselect listbox. Add features to be able to sort on any of the listbox columns, and filter on any of the columns. You could either identify which ones are already assigned or switch between views of "assigned", "unassigned", "All". So that would give you features really similar to a spreadsheet control. Then you just hit the "to" button to then get a means to assign to the new foreign key item. The one thing I do not like about a standard listbox is that there is no checkboxes to click selected items. With a multiselect listbox it is difficult to select items and change you sort, filter. You can do it but you have to persist the items selected on your own. You can get checkboxes using a listview control, but they are a pain to learn to use. Nothing is documented.
The next option is to use a continuous tabular form. You can add the same features of being able to sort or filter any way you want. With that you can fake a selectable field. You can add a boolean field to the underlying inventory table, "Selected". This can be shown as a checkbox. Then you clear this field once records are assigned.

Not sure of your coding abilities, but if this was me I would do it in VB.net using ADO.Net. You then could use an extremely powerful grid view control to do this. With a disconnected recordset and a sql backend, I would think you would get much better performance and have a lot more flexibility. If you have never done this this the overhead is high, but the payoff is big because the .net controls are so much more powerful. If you think about it Access controls have really not changed much since the first version of Access.

There may also be a place for a Treeview to show the hierrarchy and allow the user to expand or collapse the nodes

Project1
-- Folder A
-----File xx
-----File yy
-- Folder B
-----File ww
-----File zz
+Project2
+Project3

 

[misscrf]

Your response is fantastic, Maj. You really see the exact challenge I am facing. I have studied .Net, but I haven't even begun to build anything legitimate with something like vb.net and ado.net. The back end is SQL, and honestly my preference would be to build an interface like a folder tree, but Access is too limited to feasibly build something like that. This application just started out in Access, and now trying to rebuild it in another tool seems gigantic. I would love to do it, but just not sure I can at this time. (it is constantly growing is scope creep).

I think if I can make a multi-column / multi-select listbox that can be sorted, then the users will be ok. They are all pretty technical. From there, I could create buttons that could change the source of the listbox to be assigned/vs unassigned - but not sure that matters. One file could be assigned to more than 1 source. I might like to put a text box control for searching within the path or file name. I might also need text boxes to filter the modified date/ I will have to look up the multi-column sortable listbox and go from there.

Thank you so much for your response. It is nice to know that my concerns are not just in my head lol.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

I have a lot of class modules that can turn any listbox into a filterable on any field, sortable on any field, find as you type capable. I also have a very robust tree view class to create an tree view in access. Can even add drag and drop capability. So that is easily doable in access. My concern may be with the millions of records. In order to be efficient the code may have to be refactored. For example you would not fully populate the tree view. Instead you would only populate the highest level of nodes. Then you would populate only when you expand the nodes. I can walk you through this code and save you some time.

 

[misscrf]

Thank you so much. I would love to look at what you have. So far I have been working on the foundation of the multi-select listbox. My intent is to open the form, when someone clicks to assign inventory files to a source. On open, I will use open-args to set the text box control label that reminds the user what record they are assigning inventory records for.

Then I am determining where to go from there. I have controls for the filtering, and toggle buttons for filtering. My thought is to either force the person to put a filter in, before they will see anything in the list box, or show records in the list box up to a threshold. If the inventory has more than x items, set the listbox to a faux label telling them to filter. Just trying to not make it an annoying interface lol.

I really love the idea of creating a tree view and even dragging an dropping selected items onto their assignment. Would be pretty neat. If it will be a huge amount of work though, I will stick with the list box method for now. This can always be the next version (so we can start using this functionality) and then I can work on upgrading to a node type of feature.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

I looked at some of my code, and these are really flexible for instantiating filter and sort capabilities but may not be very efficient for extremely large data sets. For example you would not want to "find as you type" in an extremely large data set because you would requery after each change. For aesthetics I use a standard listbox, but no column headers. Instead I put labels over the columns that are clickable and sized appropriately. This allows you to click a column and sort on that column. Normally I would use the listboxes recordset property to filter and sort, because without knowing anything about the query this can be applied by just knowing the fields to sort or filter. Makes it very portable, but not efficient. I would think you would want to make and store your queries for each sort. You would have a stored query for each column that differs by the sort order. This would give a little efficiency in execution. You could actually have 2 per label, one ascending and one descending. You just need to keep track of the direction and load the other query. This can be done with a static variable.

 

[misscrf]

good points. I will have to consider all of that in this development. I like the idea of having stored queries, or some functions that add a sort for each field in each direction. It would simplify the process while also controlling the performance hits.

Right now, I am working on tweaking some stored procs that get the count of recs in a table, to show the files/folders in the inventory for a given piece of media. This is a way for users to see that the current media record has inventory records imported. It was slowing things down. I got it to go from 18 seconds for getting a 1.1 million rec count to 4 secs. A bit of an improvement for now.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

One thing you may find frustrating with a filterable/sortable multiselect, is that your selections will not persist after you filter or sort. The items selected collection are stored by their index in the list. So if you select items 10, 15, 20 then sort, you will still show that items 10,15,20 are seleted, but obviously different items than intended. Not sure what happens if you filter below 20 if you get an error. You can save the PKs of these items in your own collection and then span the list and reselect the correct items. That may be an expensive operation. Something to keep in mind.

 

[misscrf]

Definitely good to keep in mind. After all this development, it may end up being an exercise in letting the users see for themselves the issues that this kind of task has.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

You may want to look at the Media Monkey interface to get some ideas. I use MM to manage my music because it is 100 times better than ITunes and it is free. ITunes is worthless for organizing music, only purpose is to send you to the Apple store to buy stuff. I have 35K songs in my library, and MM has all kinds of features to sort, filter, find, edit, remove, assign, and organize. It sounds real similar to what you are looking for. One of the best interfaces and best piece of free software.

 

[misscrf]

Sounds great. Thanks for the tip! I may end up having to grab that to better organize my music too lol.

misscrf

It is never too late to become what you could have been ~ George Eliot