2 Replies Latest reply on Apr 14, 2014 6:53 AM by Donovan_1

    Relationship to self: How to avoid a new record appears in the LOV?

    Donovan_1

      Title

      Relationship to self: How to avoid a new record appears in the LOV?

      Post

           I have a table with a field that refers to itself:

           Table1
           - keyID (Serial number)
           - name
           - parentKeyID

           In the relationship graph I have duplicated Table1 to Table1LOV.

           I created a value list with

           1st field: Table1LOV::keyID
           2n field: Table1LOV::name
           Include related values starting from: Table1

           The relationship between Table1 and Table1LOV is:

           Table1.keyID <> Table1LOV.keyID

           The idea is that if you create a new record, you do not see that new record in the LOV, otherwise you could choose the record itself as its parent.

           I can't get that to work. The LOV shows the current record as well, although it has not been committed yet.

           As soon as I commit, that value disappears from the LOV, so the idea is working, but not for new records.

           I think that is rather weird, because reasoning like FM, I would start at Table1 and find my uncommitted record. From there I'd go to Table1LOV and find all records that do not match the record in Table1. And I do find that new record!

           So that means the new record must be in Table1LOV, otherwise it would not appear at all in the LOV. But at the same time it is not in Table1, although these are the same tables!

           So FM takes the current uncommitted record from Table1 and then goes to Table1LOV and finds all records that have Table1LOV::keyID <> Table1::keyID. And this includes the record that have not been created yet?

           Is there a way that I can create the LOV so that the current value does not appear?

        • 1. Re: Relationship to self: How to avoid a new record appears in the LOV?
          philmodjunk

               While that does seem a big odd, you have also already described the solution: Commit the new record. You can use the OnRecordLoad trigger to commit the record automatically each time a new record is created and then this will not happen.

          • 2. Re: Relationship to self: How to avoid a new record appears in the LOV?
            Donovan_1

                 Hi Phil.

                 I think it is a BUG. I reported it.

                 Personally I do not like to commit records that are not valid. I'd have to commit the record without a parent first, which should be avoided. If I commit the record first, I need to do all kinds of checks to avoid that the record is left like that.

                 After a lot of experimenting, I found a solution.

                 If define a global field on the table, which I fill in the OnObjectEnter of the parent field with the current value of keyID and I add the following to the relationship:

                 table1::keyID <> table1LOV::g~keyID

                 then it works.

                 This is very weird and looks to be a BUG.