Ok, sounds like you're understanding the essentials. So, here are some ideas to get you going.
First, since you're going to use your Route folder many times, create a new user-defined field called _fRoute, which is a calculated field, and define the expression to be the full path of your Route folder, including the trailing backslash:
Field: _fRoute: Calculated:
D:\Archive\Media\Game Footage\MW2\Video\
Now, the following expressions (untested) can be used as auto-import rules for the given fields to set values as you've asked, and they'll all use the [_fRoute] field so that you don't have to change all the expression should you someday change the Route path. You'll have to define a auto-tag rule for each field you want set, unfortunately. You can test each in a Panes view, as expression columns:
I'd like all files located inside route or inside any sub-folders to be tagged:
Field = "Media Sub Type" Value = "Other"
Field = "Category" Value = "Game"
Field: Media Sub Type: Value: Other
ifelse(isequal([Filename], [_fRoute], 8), Other)
Field: Category: Value: Game
if(isequal([Filename], [_fRoute], 8), Game)
And if possible for files living inside just the route:
Field = "Keyword" Value = "Raw"
Field: Keyword: Value: Raw
ifelse(isequal([Filename (path)], [_fRoute]), Raw)
Then i set up a Project folder inside the same route as before for Vegas Completed movies = "Route\Project Name"
Field = "Projects" Value = "Project Folders Name"
Field: Projects: project's folder name
ifelse(isequal([Filename], [_fRoute], 8),
ifelse(compare(listcount([Filename (path)], \), >, listcount([_fRoute], \)),
if(isequal(FileFolder(,0), Preview),
FileFolder(,1)),
FileFolder(,0)))
Note: this one looks to see if the route is a substring of the Filename, and then verifies the existence of sub-folders (by comparing the number of path components), and sets the project name to either the folder name of the parent or parent's parent (in the case of previews).
And a Sub folder for Previews = "Route\Project Name\Preview"
Field: "Keyword" Value = "Preview"
ifelse(isequal([Filename], [_fRoute], 8),
ifelse(compare(listcount([Filename (path)],\), >, listcount([_fRoute], \)),
ifelse(isequal(FileFolder(,0), Preview), Preview)