Skip to main content
Question

Formula to find duplicate values in one field.

  • December 4, 2019
  • 0 replies
  • 271 views
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.


------------------------------
Heather Bryant
------------------------------
    This topic has been closed for replies.

    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
    ------------------------------
    Terms & ConditionsAccessibility statement