Consultant Database Design

[egordin]

Hello,

I just recently started using Airtable to manage our consultants and we need some help with the database design.

The goal of the Airtable is to give us a financial and project management understanding of where we stand with our consultants, and to also share that understanding with them. To that end, we have the following tables:

1. Projects
2. Consultant
3. Estimates & Proposals
4. Payments

This seems to work well as we link each estimate/proposal to a consultant and to a project. We also link each payment to a consultant and to a project. At this level everything makes sense and we love the high level view over everything.

What we’d really like now is another table that allows us to enter/track each consultant’s status for each project, as well as calculate the total estimates for that consultant for that project, and the total payments for that consultant for that project.

I can’t figure out how to create this last table (I’m probably thinking about it wrong).

Any ideas? Thank you in advance!

[HammerOz]

Hi

What is the consultant’s status you mention? is it determined by open tasks and the like?

I’m not quite sure why you need another table, but I might not be on the right path!

Have you tried grouping Estimates (and payments) both by Project and consultant simultaneously then selecting a summary ⌄ type for the amount ?

[W_Vann_Hall]

I’m also unclear as to what exactly you’re trying to do, here, so the remainder of this reply may be entirely off-base [no pun intended]. If so, feel free to ignore…

It sounds to me, at least in part, you may be hoping to take what is essentially the result of a vertical calculation – e.g., payments or estimates, summed – but express it as a collection of sub-calculations as determined based upon a horizontal criterion (e.g., per Consultant). If such is the case, it almost certainly cannot be implemented using ‘normal’ Airtable practices.

Instead, you may wish to take advantage of some of the techniques I outline in Missing a table? and its companion demo base. There also may be some commonalities between what you wish to accomplish and the requirements of the user I tried to fulfill in this reply and its companion base.

Again, both of these solutions address edge conditions, so there’s an excellent chance you won’t need either of them. This post is just in case you do…

[egordin]

Thank you both for your suggestions - sorry that I wasn’t clear but I really appreciate it. I think I’ve figured out a solution for now!