rollup count values

[rfscott2016]

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

Table 1
ID	Project Name	Created Date	Change Code
1	Project 1	3/2/2022	Project Date Change
2	Project 2	3/2/2022	Project Date Change
3	Project 3	3/2/2022	Project Date Change
4	Project 1	3/22/2022	Project Date Change
5	Project 1	4/12/2022	Project Date Change
6	Project 2	4/22/2022	Project Date Change
Table 2
Org-unit	Project name	Primary Business Unit	Linked field table 1 Project name	Project Date Count
AMER-Unit1	Project 1	BU 1		3
AMER-Unit1	Project 4	BU 4		0
AMER-Unit2	Project 2	BU 2		2
AMER-Unit2	Project 6	BU 6		0
AMER-Unit3	Project 3	BU 3		1
AMER-Unit3	Project 7	BU 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. 

[Tyler_Thorson]

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!

[Stephen_Orr1]

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!

[rfscott2016]

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? 

[Stephen_Orr1]

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.

[rfscott2016]

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?  

[Stephen_Orr1]

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.