This worked, thank you. When I create this report link, the displayed Record ID hyperlinks out to the correct "successor" but it's showing the current record's Record ID in the field. While this isn't a problem because I can display a report that shows the successor record information, I'm now trying to create a separate QB report that will filter out any record that has an existing "successor". I'm trying to do this so that users will know what records still require action (e.g. if there is no successor linked to the agreement record and the agreement is about to expire, it requires action).
Qrew LegendTo answer this:
When I create this report link, the displayed Record ID hyperlinks out to the correct "successor" but it's showing the current record's Record ID in the field.
I was assuming that you would show an embedded report of the corresponding record on the form as opposed to that link. It sounds like you are using legacy forms, so just change the form properties to show the records directly on the form.
As for developing that report well now we are talking about brother and sister type records that are not part of a relationship seeing each other. The only way to do that is by the use of a formula query. Have you used any of those before? The syntax can be a little tricky.
But I can probably help you with that syntax if you tell me the Field ID that holds that Record ID of the predecessor record and I guess the exact name of that field. .
I'm using new forms. I ended up using the embedded report like you said and this fits the form need.
I was previously trying to do this but was coming across errors. However, I think it was because I was using the incorrect formula type (rich text instead of text list). I used the following formula to link the "brother/sister" records and I think I got what I need, but please let me know if I'm missing anything.
GetFieldValues(GetRecords("{278.EX.'"&[Record ID#]&"'}"), 3)Field 278 is the predecessor field, Field 3 is the record ID. It correctly pulled the successor or "Renewal Agreement" ID.
Qrew LegendWell done. Looks good to me.