Count Number of double entries

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • In Progress
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?
Photo of Milos Trbojevic

Milos Trbojevic

  • 124 Points 100 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
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.
Photo of Milos Trbojevic

Milos Trbojevic

  • 124 Points 100 badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
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. 
Photo of Milos Trbojevic

Milos Trbojevic

  • 124 Points 100 badge 2x thumb
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 )
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
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.
Photo of Milos Trbojevic

Milos Trbojevic

  • 124 Points 100 badge 2x thumb
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?
 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
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
Photo of Milos Trbojevic

Milos Trbojevic

  • 124 Points 100 badge 2x thumb
this is exactly what I need, but unfortunately I don't know how to implement this.

Can you please advise? Should I create IOL field (btw what's IOL)?



 
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Milos,
  
The IOL technique is an advanced technique which I do not recommend for your skill level.  There are also some other requirements that you communicated to me by email to do with determining the nearest expiry date of the Calibration equipment based on the state which the technician services.

I suggest a native solution will meet your needs as your technicians presumably have a limited number of states which they service.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,322 Points 20k badge 2x thumb
As I said I think you would be better off to initially pursuing a native solution as Mark suggested and then move on to scripting solutions as your experience and requirements grow. I often answer questions just accepting the conditions a user describe assuming there is good reason for their approach to the problem. Using script is so powerful that it can pretty much absorb any scenario or assumptions a user makes.

However, in your case I think you need to use some type of parent/child relationship and I question if merely displaying the number of "double entries" is a totally adequate solution. Don't you need to further identify what the double entries are and drill down into that data? So if I were you I would further engage with Mark to further develop your requirements and proceed to a native solution first.

Once you do that I would further proceed to learning how to use script with QuickBase. Once you learn how powerful using script with QuickBase is you will have an epiphany and exclaim "What was I thinking - script is much better than native."

In the [Notes] field in every record of the Pastie Database there is an hyperlink pointing to this pastie which describes how to setup the IOL Technique:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294
(Edited)