Skip to main content
Question

Data association from a Linked fields and rollups

Z
Forum|alt.badge.img+5
  • zLow
  • New Participant
  • 1 reply

Hi - I'm absolutely stuck trying to achieve the results I want, and I'm hoping for some focused support.

 

I have 3 Tables in my Base:
- Vendors - which contains companies I contract work from.
- Resources - which contains the individual resources that work at those companies, as well as their individual monthly rates. Resources are linked to Vendors.

- Monthly Cost - contains a list of records for each month of the year (eg; January 2025, February 2025, March 2025, etc). 

 

In Resources, I'm assigning Months, from the Monthly Cost table, for when I expect this resource to work.

 

In Monthly Cost table, can see the assigned resources by month, and I created a Rollup field to show the Vendor names associated with the Resources that are assigned to the month. This is using ARRAYUNIQUE, so if I have 5 resources at one vendor, I don't get the vendor displayed once.

 

I also have a Monthly Resource Cost Lookup field that will Look up the assigned Resource's Monthly Rate. The rates are displayed, separated by commas.

 

Lastly, I have a Formula field that is SUMing the Monthly Resource Cost Lookup field, so I get a Total Monthly cost for all resources.

 

All of the above is working as desired and expected.

 

What I cannot figure out is how I can pull the Monthly Cost for Specific Vendors out of this setup. 

 

I created a Dashboard Interface that has a Bar Graph pulling from the Monthly Cost Table, and displaying the Months Along X and the SUM of the Monthly Resource Cost On Y. This is working great. 

 

When I expand the Graph, I can see all of the Months, and their SUMs in a list - this is great.

 

I want to be able to Open that monthly record and see the Cost for each of the Vendors that have resources associated with that month, but it seems I cannot, because of the problem in Bold above.

 

Any help would be greatly appreciated!

2 replies

TimBeeston
Forum|alt.badge.img+3
  • TimBeeston
  • Participating Frequently
  • 16 replies
  • March 29, 2025

Hi ​@zLow,

How are you allocating time to each resource? Your setup looks to be missing a join table between Months & Resources. Adding one would allow you to create iterations for each resource and allocate them against a month.

Here's a revised structure that includes a linking table:

  1. Vendors: Companies (unchanged)
  2. Resources: Individual resources with their base rates, linked to Vendors (unchanged)
  3. Monthly Cost: Month records (unchanged)
  4. Resource Allocations (New): The linking table containing:
    • Link to Resource
    • Link to Month (from Monthly Cost)
    • Hours allocated for this month
    • Calculated total (Hours × Rate)

With this structure, solving your vendor cost issue becomes much more straightforward. You could simply use a rollup field in your Monthly Cost table that groups the Resource Allocations by vendor and sums their costs - no complex formulas needed. Plus, this approach gives you more flexibility with your resources, letting you track hours, handle variable rates, and generate more accurate reports.

This should make your dashboard work as you want it, with each month able to show costs broken down by vendor with a single rollup field.

 

D
Forum|alt.badge.img+4
  • Davedigital1
  • New Participant
  • 3 replies
  • March 29, 2025

You're on the right track, but the missing piece is a way to associate costs with specific vendors inside the Monthly Cost table. Since Vendors are linked through Resources, you need a method to sum costs per vendor for each month.

Solution Approach:

  1. Create a "Monthly Vendor Costs" Table

    • Add fields: Month (Linked to Monthly Cost), Vendor (Linked to Vendors), and Total Cost.

    • This table will store the sum of resource costs per vendor per month.

  2. Modify the Resources Table

    • Add a formula field to multiply Monthly Rate by the number of assigned months.

  3. Use Rollups to Aggregate Costs

    • In Monthly Vendor Costs, roll up the linked resources' costs (filtered by vendor).

    • In Monthly Cost, roll up the costs from Monthly Vendor Costs to get a per-vendor breakdown.

  4. Update the Dashboard Interface

    • Display a breakdown of costs per vendor per month alongside the total cost.

This setup will allow you to open any Monthly Cost record and see the cost breakdown per vendor. Let me know if you need a step-by-step guide!

Reply


Most helpful members this week

Terms & Conditions