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 reference field, but it was pointing to a deleted record. This was not clear at all because the record picker for the related field (I have the record in edit mode) appeared exactly as if it were null/empty.

It used to be that when a reference field had a value but there was no record in the foreign table with that id QB would display a red message indicating this, but now it simply says "Search and Select" as if there were no current value there at all. The only way to tell that there was a value there previously is to start editing the field and then hit backspace; QB will then highlight the field to indicate its value has changed (see pics). So I've been tearing my hair out staring at this record because it appeared to have a null value but it wasn't working with any of my null check formulae.

Is there any way to get the old behavior back for the record picker? I can make the id field itself read only (it's in an admin section of my form anyway) but I'd still like the user to know when about the deleted record when looking at the proxy field.

Better yet, is there a good way to check if a related record has been deleted formulaically so I can adjust my report to include these issues?

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.

6 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 months ago

    I think what you're asking for here is a way to make it obvious to the user that the child record is connected to a deleted parent. Typically what I do is I make a nice clean formula check box field on the parent record with a formula of true called [Donor Exists?]. Then I look that up down to the Child table. If that lookup is false, then there is no parent record.

    So one solution is to have a formula Rich Text field on the form telling the user that the donor has been deleted. So the formula for that would key off of [related donor] being greater than zero, but the look up of [Donor Exists?] would be false.  

    You would have a form rule to only display that warning when that situation existed. 

    Another choice would be to change the record picker fields or the report being used for the drop-down to have the first field be the related donor. In that case, the red number would show as soon as the record went into edit mode. The problem with that solution is that it's kind of clutter for the 99% of cases where the user is trying to choose a donor, and they are seeing the first column of the record picker fields being a number.  

     

     

  • 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

    I thought I might be able to run the check based on a lookup to a field I can expect to always have a value if the record exists, i.e. one that is required at the table level. In my case I tried a string Name field, but it doesn't seem to work:

    Trim([Donor - Name]) = "" or trim([Contact - Name]) = ""

    ...evaluates to false for a related donor or contact. Perhaps the old lookup value still persists in some way? I can't use IsNull here because of the type, but maybe this would work if it were a numeric field...