I have two tables: BEN and EMP. Inside EMP there is a column called empCode, which is also present in BEN. In my EMP table, I setup a multiple relation that matches the empCode with the same column in my BEN table, and also a rollup column that does a count via the relation column. With that, I can count how many times the empCode matches rows inside BEN empCode column.
Now, I would like to create another count column, but this time I have a text column called “spot” inside the BEN table that contains either a 0 or 1. I would like to reflect how many times it was 1 while also matching the empCode as well. So for example, let’s say I have 4 items counted already but only 2 of these items in BEN have 1, then the count in this new column within EMP should be 2.
If I’m understanding correctly, what you should be able to do is add a rollup column to your BEN table, and configure it to take the Max->hubspot via the benMatch relation column.
Does that give you the result you are looking for?
@Darren_Murphy Essentially yes. Those with hubspot status 1 are completed, so they should be counted. But, it should count only where the empCode matches. In essence that new column should count the number of completed items: those that have hubspot status 1.
For instance, let’s say I have those two rows in EMP table with their respective empCodes. If I would have 20 items in BEN that have hubspot status 1 for WXC… and 25 items that have hubspot status 1 for RH1, then the EMP column completed should be 20 for row with WXC and 25 for row with RH1, regardless of how many of them in BEN have hubspot status 0.
@Darren_Murphy will try that and get back to you, thank you. Here is the MySQL code to illustrate this:
CREATE VIEW emp_ben_view AS
SELECT
e.id,
e.empCode,
COUNT(b.empCode) AS empCode_matches,
SUM(b.hubspot) AS hubspot_ones
FROM
enw_emp e
LEFT JOIN
enw_ben b ON e.empCode = b.empCode
GROUP BY
e.id,
e.empCode;
