Help me solve a challenge - add a fundamental variable list for each client

Ask for Help
1

Hi all,
There are some incredible minds here, which will surely be able to solve this challenge I’ve been battling with for quite some time now.
In a nutshell:
My CRM app has a list of clients (rows).
For each client there a “status” cell (you guessed it, it’s to know the current milestone the client’s project is at).
Now, here the problem: a fundamental mistake I did when I built the app is to think one dimensionally. This means, one client can only have one project at any given moment.
I need to add a multi-dimensional approach = multiple projects per client. Each project should be able to have:

  1. Status
  2. If not part of status - ongoing/ended/cancelled/etc.

On top of that, there’s an additional challenge:
each such project should have it’s own:

  1. “action log” - any actions our team makes in regards to this project. This log is currently single-dimensional, i.e. an action can only be on the client level.
  2. “event log” - any events which are related to a specific project. These are actual events, such as meetings, trips, etc. Again, currently logged on a single dimension.

The essence of this challenge is my desire to avoid a total overhaul for the app, i.e. building it again from scratch as multi-dimensional. Ideally, I’d like to change as little as possible so it won’t effect the ongoing scripts which run on time triggers, form submissions, etc. I assume I’ll need to build some kind of “helper sheet” in which we’ll store the multiple project names of each client. It’s also important to note that project names (and the project itself) is different from client to client.

In regards to all current data which is stored per client (single dimension): I think it’ll be close to impossible to revamp it as multi-dimensional so I am willing to keep it as is. Of course, any ideas on this front are also more than welcome.

In my eyes, it’s a rather impossible set of challenges. I would like to hope that in someone else’s eyes it’ll be a no-brainer and a simple solution.
Thanks!

2

Is there a reason why there isn’t a projects sheet that is linked with with the client’s email/ID?

3

That’s the helper sheet I was referring to but I’m not sure how to fully implement it seamlessly. I’d really like to avoid making major changes to the dozens of scripts which are running behind the scenes. Happy to hear your ideas. I’m sure someone else’s eyes will see it as a much less complicated challenge than I see it.

4

Hm…I’m not one to mess with Google scripts unless it’s absolutely necessary. I don’t have a great answer for you except create a separate sheet that lists out all projects for each client and use relations to match client > project(s).

1 Like
5

Thanks @Robert_Petitto . Appreciate your input.
Still hoping there’s someone with a solution out there :slight_smile:

6

I would do as @Robert_Petitto suggested, and split your data across multiple related tables. So something like this:

  • Clients

    • Projects
      • Action Log
      • Event Log

  • where each of the above is a distinct table, and

  • Projects are related to Clients via a ClientID

  • both Actions and Events are related to Projects via a ProjectID

Anything less is likely to be a hacky workaround that will most likely just increase your level of Technical Debt, which you’ll pay for (with interest) in the long term.

Scripts can be re-factored/re-written.

3 Likes
7

Thanks you both, @Darren_Murphy & @Robert_Petitto !