Complex Country, State, City Suggestions

I work for a company that deals with several states, cities, and countries.

Any suggestions/ideas would be helpful as I try to simplify this somehow.

Here is an example:

Customers
     United_States
           AK
                Cordova
                      Project 1
                           Quotes
                           Designs
                      Project 2
                           Quotes
                           Designs
           OH
                Amelia
                      Project 1
                           Quotes
                           Designs
                      Project 2
                           Quotes
                           Designs
     International
           Canada
                NB
                      Project 1
                           Quotes
                           Designs
                      Project 2
                           Quotes
                           Designs
           Greece
                Athens
                      Project 1
                           Quotes
                           Designs
                      Project 2
                           Quotes
                           Designs

Thank you for your time and input.

Hi. I’ll be following to see responses. See my post on 4 number JD numbers. I’m having a similar issue with several clients.

Here’s an idea I’ve never tried but have had in my mind for a while.

Countries already have a numerical identifier that everyone agrees on: their international dialling code. Australia is 61, for example. The UK 44. USA is 1 obviously, is Canada the same? That’s unhelpful but hey.

So do many cities and states. I know the USA’s area codes are well defined. Australian states each have a code.

Could you construct numbers for the first part of your tree, up to the point where you reach your projects/clients, using these already-known numbers?…

Otherwise I think I’d just be advocating for a use-the-alphabet-with-numbers-after-the-items approach like explained here.

Thank you both for your replies it has been very helpful! I am trying to come up with something that will play well with SharePoint, PowerAutomate, and CRM365.

This is what I have so far and then I have an explanation below the example:

Africa_101
Canada_102
Caribbean_103
Central_104
   MO-Joplin_104.100
      Project 1_104.100.01
      Project 2_104.100.01
East_105
Eastern Asia_106
Europe_107
Middle East_108
North America_109
South America_110
West_111

The above regions line up with a choice field on our CRM and the city and state is also entered in the CRM when creating a new customer. This means with Power Automate when a customer is created I can automate creating the folder in sharepoint and create the relationship of the CRM customer to the folder location in SharePoint using the JD number (104.100) as a reference. (A column on the customer will have the JD number permanently stored and can be used by power automate to find the sharepoint folder)

Another plus is emails sent from the CRM can be tagged with the number automatically.

When an opportunity (project) is created on a customer it can nest inside the correct folder automatically because of the JD reference on the customer and I can tag the opportunity with the project reference JD number (104.100.01). Hope that makes some sense I am still trying to unpack it all from my mind.

I will experiment with brackets though as well to see how I like the look.

In my mind I don’t see it as a big of a deal to have a 4 digit number so is there something I am missing? For example I am sure there are more than 999 state-cities in the central region so if the number gets to something like this 104.8015.03 is that really a big deal?

I still need to do some more reading on your site though because I just found your site last night and has got me thinking.

In a perfect world I would have the users only use CRM to interface with the files then I could do just about whatever with the structure but unfortunately it would be hard to sell to leadership. They are too used to traditional folder structures so I am brainstorming a solution that can possibly work together.

This topic has come up so many times at the office and I always hit a brick wall however I think you are on to something here and I finally have hope that we can move toward better organization.

Thanks again guys!!

1 Like

Agree, not a big deal.