Count Number of double entries
Hi, In a table I have technicians that are working in more than one State. How I can count how many are they that are working in 2 States, and how many are working in for example in 3 States?
Forum Discussion
You can do this with a summary report and observe visually, but I assume that you want to see actual fields on the tech record.
To do this you will need to float up the unique states into separate fields on the tech record. Then you can see them and obviously have a formula to count them. This method will work for a limited number of maximum states which a tech works in.
I assume that you want to measure this over a given time period. For flexibility the first step is to create a formula field which flags the Jobs records (I assume 1 Tech is dispatched to many Jobs) with a checkbox true if the job date was within range. [Job Date is in Analysis Range?]
for example Year(Today()) - 1 = Year([job date])
would flag last year's jobs with true.
1. On the relationship between One Tech has many jobs, make a summary of the minimum record ID subject to the filter that [Job Date is in Analysis Range?] is checked and call it called [Record ID#1 of Job 1].
2. Make a new reverse relationship where 1 Job has many Techs. On the right hand side of the relationship for the reference field use that field [Record ID#1 of Job 1]. Look up the State field and call it [Tech State 1]. Now the Tech record knows the 1st State.
Clean up the right side of that relationship by deleting the Add Tech and Techs fields as you don't need them Now, flip back to the regular relationship where 1 Tech has many Jobs. Lookup up the field [Tech State 1 down to the Jobs record.
Duplicate the summary field called [Record ID#1 of Job 1] but call it [Record ID#2 of Job]. Add to the existing filter the additional filter that [Job State] is not equal to [Tech State 1]. You have now essentially just repeated step 2 above. Look up the Job State field and call it [Tech State 2].
You will find that the system automatically also duplicated the reverse relationship for you. Locate this new relationship and Look up the Job State field and call it [Tech State 2]. Now the Tech record knows the 2nd State.
Clean up the right side of that relationship by deleting the Add Tech and Techs fields as you don't need them Now, flip back to the regular relationship where 1 Tech has many Jobs. Lookup up the field [Tech State 1 down to the Jobs record.
Just keep repeating the steps and each time you make the new summary field add the condition that the Add to the existing filter the additional filter that [Job State] is not equal to [Tech State x] where x is the latest Tech State that you have got so far.
Probably 5 loops are enough as it is unlikely, I suppose for a Tech to go to more than 5 States in a year.
If you need a count you can make a formula to count how many of
[Tech State 1]
[Tech State 2]
[Tech State 3]
[Tech State 4]
[Tech State 5]
are not blank.
To do this you will need to float up the unique states into separate fields on the tech record. Then you can see them and obviously have a formula to count them. This method will work for a limited number of maximum states which a tech works in.
I assume that you want to measure this over a given time period. For flexibility the first step is to create a formula field which flags the Jobs records (I assume 1 Tech is dispatched to many Jobs) with a checkbox true if the job date was within range. [Job Date is in Analysis Range?]
for example Year(Today()) - 1 = Year([job date])
would flag last year's jobs with true.
1. On the relationship between One Tech has many jobs, make a summary of the minimum record ID subject to the filter that [Job Date is in Analysis Range?] is checked and call it called [Record ID#1 of Job 1].
2. Make a new reverse relationship where 1 Job has many Techs. On the right hand side of the relationship for the reference field use that field [Record ID#1 of Job 1]. Look up the State field and call it [Tech State 1]. Now the Tech record knows the 1st State.
Clean up the right side of that relationship by deleting the Add Tech and Techs fields as you don't need them Now, flip back to the regular relationship where 1 Tech has many Jobs. Lookup up the field [Tech State 1 down to the Jobs record.
Duplicate the summary field called [Record ID#1 of Job 1] but call it [Record ID#2 of Job]. Add to the existing filter the additional filter that [Job State] is not equal to [Tech State 1]. You have now essentially just repeated step 2 above. Look up the Job State field and call it [Tech State 2].
You will find that the system automatically also duplicated the reverse relationship for you. Locate this new relationship and Look up the Job State field and call it [Tech State 2]. Now the Tech record knows the 2nd State.
Clean up the right side of that relationship by deleting the Add Tech and Techs fields as you don't need them Now, flip back to the regular relationship where 1 Tech has many Jobs. Lookup up the field [Tech State 1 down to the Jobs record.
Just keep repeating the steps and each time you make the new summary field add the condition that the Add to the existing filter the additional filter that [Job State] is not equal to [Tech State x] where x is the latest Tech State that you have got so far.
Probably 5 loops are enough as it is unlikely, I suppose for a Tech to go to more than 5 States in a year.
If you need a count you can make a formula to count how many of
[Tech State 1]
[Tech State 2]
[Tech State 3]
[Tech State 4]
[Tech State 5]
are not blank.