Discussions

 View Only
  • 1.  Formula Query to search for matching records with the exception of a specific field not matching

    Posted 14 days ago
    I currently am using a Formula Query to identify when specific fields match it returns the Record ID# that has the matching data. This is to help identify duplicate record entries for things that can not be unique fields.

    It works just fine.

    I am now trying to figure out a Formula Query to look for records that should share the same data in 3 specific fields but doesn't.
    The logic would be When [Field 5] equals [Field 6] but not equal to [Field 7] return [Record ID#]

    What needs to go in the butnot location for my logic to work for this Formula Query?
    GetFieldValues(GetRecords("{5.EX. ' "&[Field 5] &" '} AND {6.EX. ' "&[Field 6] &" '} butnot {7.EX. ' "&[Field 7] &" '} AND {3.EXE. ' "&[Record ID#] &" '}" ),3)

    ------------------------------
    Jovan Vucovich
    ------------------------------


  • 2.  RE: Formula Query to search for matching records with the exception of a specific field not matching

    Posted 14 days ago
    The operator for Not Equals is XEX.  sort of the opposite of EX.

    So try this

    GetFieldValues(GetRecords("{5.EX. ' "&[Field 5] &" '} AND {6.EX. ' "&[Field 6] &" '} AND {7.XEX. ' "&[Field 7] &" '} AND {3.XEX. ' "&[Record ID#] &" '}" ),3)


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula Query to search for matching records with the exception of a specific field not matching

    Posted 13 days ago
    Thank you,
    I had mis-type in my example, it was supposed to be XEX.

    It worked to that [field 7] not matching when the rest do match.

    I was thinking the XEX was an exclusion operation to exclude the display of that field for the listed matching fields, which why I needed it for the [Record ID#].  But thinking about it, that is what the 3 at the end of the formula is for to display that data in Field ID 3.



    ------------------------------
    Jovan Vucovich
    ------------------------------