0
id itemId correlationId
1 A
2 B A
3 A
4 C B
5 D B
6 E D

Hello, I have a Notes database with a similar structure like the table above. This table contains a unique id for each document. It also contains an itemId, which is not unique, and a correlationId which creates a relation to another item (itemId). I want to select all documents, which have a specific itemId (in this example A) and every document which is correlated to this itemId (directly or indirectly). The result should look similar to my table. I was wondering if I need to write multiple requests or if it is possible to write only one formula.

SELECT @Contains(itemId; "A");

SELECT @Contains(correlationId;"A") => retrieve itemId: B ==>SELECT @Contains(correlationId;"B") => retrieve itemId: C, D ==> SELECT @Contains(correlationId;"C") (no result) and SELECT @Contains(correlationId;"D")

lucas
  • 55
  • 2
  • 10
  • What is the purpose of the selection? Is it a view selection formula, or for some other purpose? – Phil M Jones Aug 09 '21 at 14:25
  • Is this for a view selection formula? If I'm understanding your requirements (e.g., "directly or indirectly") correctly, you're going to need to use a looping construct (i.e., For, While, DoWhile) and do lookups within the loop body to compare the current document's correlationid against other documents' itemids. (First you'd compare against 'A', then on the next iteration you'd compare against the list of documents that matched against 'A', etc., until you find no more matches and exit the loop. But lookups aren't allowed in view selection formulas, unless something has changed recently. – Richard Schwartz Aug 09 '21 at 20:12
  • @RichardSchwartz yeah that's right, I am trying to create a looping construction. But I do not want to use it for a view, but for a REST API. Otherwise, I will just do this loop manually. I was just wondering if there was a more efficient way. Thank you for your reply. – lucas Aug 10 '21 at 08:46
  • Interesting. In that case, I'm not sure I'd want to be using a SELECT statement. Rather, I'd just use the loop build a list by doing dblookup calls, and appending each result until you get a lookup that doesn't add any new items to the list. – Richard Schwartz Aug 11 '21 at 12:41

0 Answers0