Help

Re: rollup count values

2719 0
cancel
Showing results for 
Search instead for 
Did you mean: 
rfscott2016
6 - Interface Innovator
6 - Interface Innovator

I have 2 tables where I am trying to count the number of times project date changed. 

Table 1     
ID Project Name Created DateChange Code  
1Project 13/2/2022Project Date Change 
2Project 23/2/2022Project Date Change 
3Project 33/2/2022Project Date Change 
4Project 13/22/2022Project Date Change 
5Project 14/12/2022Project Date Change 
6Project 24/22/2022Project Date Change 
     
     
     
Table 2    
Org-unitProject name Primary Business UnitLinked field table 1 Project name Project Date Count 
AMER-Unit1Project 1BU 1 3
AMER-Unit1Project 4BU 4 0
AMER-Unit2Project 2BU 2 2
AMER-Unit2Project 6BU 6 0
AMER-Unit3Project 3BU 3 1
AMER-Unit3Project 7BU 7 0

In table 2, I have a lined field from table 1 project name. The value is blank. I use the following rollup command

 

rollup linked record field Linked field table 1 Project name aggregation COUNT(VALUES)

 

All the values are 0 -

What am I doing incorrectly. The linked field is not the key in table 1, its just a field. 

 

 

6 Replies 6
Tyler_Thorson
6 - Interface Innovator
6 - Interface Innovator

Hey there,

I am having a bit of trouble following how your table is structured here, so I might be missing an element of what you are trying to accomplish.

My understanding is that you are simply trying to record the number of times a field has changed.
In general, this would be easiest to accomplish with an automation:

1. Create a Number Field that will store the number of times the Date has changed. I'll call it "# of Date Changes" for example. Set it to be an Integer and set the Default value to 0.

2. Create an Automation that Triggers when a Record is updated in Table 1 and that looks only for changes in the Project Date Field.

3. Create an action that updates a record and use the ID from the trigger. Set the value of {# of Date Changes} to be a dynamic value with the formula:    {# of Date Changes} + 1

4. For extra data security you can set the field permissions to allow no one to edit except automations. This will prevent users from changing the number.

Hope that helps!

Roll-up fields work like lookup fields but let you apply an aggregation to the lookup. You'll need to populate your linked records field tied to your roll-up field for the roll-up field to show any values (same as lookups).

If you're just looking to count the linked records, you can use your existing linked record field + a count field. I would add conditional logic on the count field where Change Code is 'Project Date Change' to only count this type of change.

Hope that helps!

This table only has the same change code type. I am counting the # of times the project name is listed. If I group by project name, the value is provided there. All I am trying to do is to get that count. Does that clear up what I am trying to do? 

 

Yes, thanks. Ideally, you would have one table for unique Projects and one table for unique Org Units. Then you would link these two tables together using a linked record field. Then you can add a count field to the Org Unit table to count the number of linked projects.

Tyler, in your example, Step 1. the # of Date Changes is that field in Table 1 or Table 2? In table 1, if I group by Project name, I do get the proper count. Assuming the # Date Changes in Table 2, step 2 I would just need to know when a record is added since this table only has when a date has changed. In Step 3, I would have thought the record id is different in table 1 and table 2 for the same project name (which is what I need to link to. Is that not the case?  

Yep, I understand. Ideally, you would have a table of unique projects and another table of unique org types. Then you could use a linked record between. Finally, you would create a count field (or roll-up with count(values)) pointed at the linked record field.

Your current structure looks like you have one record per project in Table 2. This does not use best design practices when modeling relational data and will complicate things later on.

If you need to capture metadata about the relationship between unique projects and unique org units such as specific project dates per org unit for the same project, you can employ a junction table approach. However, for your current requirements, this is overkill.