Forum Discussion

HeatherBryant2's avatar
HeatherBryant2
Qrew Assistant Captain
6 years ago

Formula to find duplicate values in one field.

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 ...
Formulas and functions
Relationships
JordanMcAlister's avatar
JordanMcAlister
Qrew Captain
6 years ago
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's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
6 years ago
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's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      Yes, a table can be related to itself and yes one parent may have many children.  The question is what the key field is and it's it's record ID, how will the data actually get connected.  It might take some one time  Excel work.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      markshnier2@gmail.com
      ------------------------------
      • HeatherBryant2's avatar
        HeatherBryant2
        Qrew Assistant Captain
        6 years ago
        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 # 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. 

        I think ​you guys solved it with the same-table relationship. Based on the info provided above do you agree?

        THANKS!

        ------------------------------
        Heather Bryant
        ------------------------------