Forum Discussion

Jonathan_Gibson's avatar
Jonathan_Gibson
Qrew Cadet
4 months ago
Solved

Deleted records and record pickers / Formula check if related record has been deleted

I have a record that appeared to be missing a related record, but wasn't showing up on a report I have for identifying records with relational issues. It turned out that there was a value in my refer...
Forms
  • JCY's avatar
    JCY
    4 months ago

    I've done something similar before using a query like this:

    var text query = "{17.EX.'" & ToText([Reference Number]) & "'}"; var RecordList matches = GetRecords($query, [_DBID_CONTACTS]); ToNumber(Size($matches) > 0)

    Good luck.

Jonathan_Gibson's avatar
Jonathan_Gibson
Qrew Cadet
4 months ago

Ooh, yeah, It looks like I can work something out utilizing Size() and GetRecord(). Thanks for the suggestion!

JCY's avatar
JCY
Qrew Trainee
4 months ago

I've done something similar before using a query like this:

var text query = "{17.EX.'" & ToText([Reference Number]) & "'}"; var RecordList matches = GetRecords($query, [_DBID_CONTACTS]); ToNumber(Size($matches) > 0)

Good luck.

  • Jonathan_Gibson's avatar
    Jonathan_Gibson
    Qrew Cadet
    4 months ago

    Here's what I came up with that seems to work for my purposes. As a report formula I have:

    var number donorCount = Nz(Size(GetRecord([Related Donor], [_DBID_Donors])));
var number contactCount = Nz(Size(GetRecord([Related Contact], [_DBID_Contacts])));

$donorCount <> 1 or $contactCount <> 1

    I created a filter to show only records where this formula evaluates to true. Then I have another report formula I'm using as a display column, in which I use those same variables (redeclaration omitted for brevity):

    List("<br><br>",
    If($donorCount = 1, "", "<b>Donor #" & [Related Donor] & " not found</b>"),
    If($contactCount = 1, "", "<b>Contact #" & [Related Contact] & " not found</b>")
    )

    This way I can include any other issues I want to surface in the same column on the report.