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.

JCY's avatar
JCY
Qrew Trainee
4 months ago

In the old grid edit mode, if I click on a reference field (which is blank because the value no longer exists) like I'm going to edit it, it will show the existing/deleted value.

Regarding checking if a related record has been deleted: I think you would need some separate record or way to know if it was deleted, like a history table. But if you just want to know if it's not there (i.e., missing), you could have a formula query based on whatever is the common/related factor.

  • 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.