Formula to find duplicate values in one field.

Question

Hi Community,I'm working with one table, named Jobs. In this table there are Parent Jobs and Sub-Jobs. Typically I would set up Sub-Jobs as a child table to Parent but due to the requirements of the app the two needed to be in the same table.

Each Parent Job has a 6 digit ID (ie; 808888). Each Sub-Job carries that Parent ID and also a Sub-Job ID is tagged on to the end. (ie; 808888.001) I've created a report link on the Parent Job form to show Sub-Jobs that share the same Parent ID. This works fine. What I'm looking to do is hide this report when the Parent Job does not have any Sub-Jobs. Normally we would do this would with a form rule reading a summary field, but I need a work-around for QB to look at the Parent and identify if there are not any other records that share the ID.

If I could get a formula checkbox to read whether there are other records with the same ID I could use that as a form rule to hide the report. But I don't know how to do that.

jordanmcalister · Answer

I'm curious to know what requirements made you not be able to use two separate tables, because that seems to be the ideal situation.

You might be able to use that summary field in a checkbox formula field by first checking if the Job ID has a "." in it as a delimiter and then checking the summary field amount.

Maybe something like:
If
(
Contains(ToText[Job ID],".")=False and [Summary Field]="1",True
)

//Checking first to make sure the record isn't a sub job then checking to see how many records have the same beginning values

If you haven't already made something like this - you can make another ID field that identifies the main job by taking all the numbers left of the decimal point so that the main job and the sub job have the same summary ID so to speak. This id would be used in the summary field, so if there is 1 main job with 3 sub jobs, the summary field would return "4"

------------------------------
Jordan McAlister
------------------------------

markshnier__you · Answer

The challenge here is that in order to do the summary feel that you need a relationship and I suspect that the job number is not the key field to the table. What is the key field to the jobs table. Is it record ID or is it the job number

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------

jordanmcalister · Answer

Could she create a same-table relationship where the job table has a relationship with itself, since each job can have many jobs?

------------------------------
Jordan McAlister
------------------------------

markshnier__you · Answer

Yes, a table can be related to itself and yes one parent may have many children. &nbsp;The question is what the key field is and it's it's record ID, how will the data actually get connected. &nbsp;It might take some one time &nbsp;Excel work.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

heatherbryant2 · Answer

Hey guys,Thanks for the responses. Here's some more info:- The one-table requirement came about due to reporting and wanting to have all jobs in one table. I probably could have created two tables separately, and then a summary table for reporting. But I didn't, and at this point it's too late to turn back.- The key field is not the Job #, it is the Record ID#, but it could be the Job #- There aren't a ton of Sub-Jobs in the app yet, so ​​​If I changed the key field it wouldn't be me too messy to have to go back and relate them to the Parent Job. I have people adding Sub-Jobs from the Parent Job so I could add the Related Job #&nbsp;to the Add button and have QB relate the Sub-Job to the Job automatically- This really isn't a make-or-break the app function, it's more of a user-friendly initiative.&nbsp;I think&nbsp;​you guys solved it with the same-table relationship. Based on the info provided above do you agree?THANKS!------------------------------Heather Bryant------------------------------

Forum Discussion

Formula to find duplicate values in one field.