Count Number of double entries

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.

Thanks you so much for your extended answer, but I'm not that proficient in QB that I can use all these informations.

I was hoping for  simple, easily applicable solution in "plain English" if possible.

I just need to count how many Technicians are registered in more than one State - I have almost 1500 Technician and 50 States. I would like a field in Technician's form that can show me in which States is he registered. 

Feel free to contact me for one on one assistance with setting this up for you.  I can be reached at QuickBaseCoach.com.

When you say "registered" in more than 1 State, can you describe how that information is stored in Quick Base.  Does  the tech record have a child table of State registrations?  If so, just make a Summary field on that Relationship to count the number of Registrations. 

Hi,

I have almost 1500 weight scales calibration technician spread across US. I would like a field in individuals form that can show me in which states is individual registered.

So if John is, in Table, is registered in TX and CA he would have 2 records. I would like John to has a field that can show me that John is registered In CA and TX   on both of his records.

Since John is using one weight kit (calibrated ion 1-1-2018) that has expiration date in CA on 2019 and expiration date in TX on 2020, I would like that Weight Kit Expiration Date be 1 year (so lower when compering in which States John is working )

In order to do that, you would need to change how your data is organized.  You will need to have a table of Techs and a table of State registrations and a Relationship where 1 Tech has many State Registrations.

It is possible to migrate the data to the required format.

I already have that - Table of States and Table of Techs. Those two are related and Form has drop down menu where you can choose one of 50 jurisdictions where tech is registered.

What I should do next?
 

It would take me about an hour to sort this out with you on a GotoMeeting screen sharing call.  I'm not really in a position to take the time to explain the click by click steps to you on this Forum.  Contact me via the information on my website for on on one assistance.

You will need to have a table of unique techs and then a child table to that of the states that they are registered in.  One Tech has many State Registrations.

I think you should probably follow Mark's advice of using two tables - especially as you indicate you are new to the product. But I just want to point out that using script you can easily implement exactly what you want without any additional tables or relationships. Moreover, a script solution will not hit arbitrary limitations as your description of the problem is refined or additional requirements come into view. For example, although you literally asked for a "count" - and that is what I implemented - but you might actually want a summary of the information and perhaps a drill down link to explore related records. All of this would be possible with script in just a few lines of code.

But again I just want to point out the superiority of using script!

Here is my quick demo (it took me only 5 minutes to put together):

View any record and [Count] will display the total number of records with the same [Name].

Count Similar Records ~ List All
https://haversineconsulting.quickbase.com/db/bncw958tt?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=618