AnsweredAssumed Answered

Self-Join not showing current record

Question asked by navarro on Nov 17, 2018
Latest reply on Nov 17, 2018 by erolst

Hello,

 

In my project I need to assign a "parent" document to each document.

 

Let's imagine that I have entered the following documents:

 

Document 1

Document 2

Document 3

Document 4

Document 5

Document 6

Document 7

...

 

All documents but Document 1, may have a "parent" document. So for example,

 

For Document 1:

 

Parent Document: N/A

Name: Document 1

 

 

For Document 2:

 

Parent Document: Document 1

Name: Document 2

...

 

For Document 7:

 

Parent Document: Document 5

Name: Document 7

 

 

So I should be able to assign a "parent" document to each document.

 

The Document table has the following relevant fields:

 

_kp_Document ID

__kf_Document ID

DocumentName

 

I have two TOs to the Document table:

 

  • Document TO
  • Document_PARENT TO

 

 

I have set up the following self_join:

 

Document_PARENT:: _kp_DocumentID = Document:: __kf_DocumentID

 

I have set up several scripts and a Document_Picker layout based on the Document_PARENT TO that has portal that shows records from the Document_PARENT TO

 

 

In order to asign the "parent" document to the current document the CardPicker script uses a Card window to show the available records on the Document_Picker layout.

 

My problem is that the picker shows all the records entered so far, and I would like to show all but the current record.

So, when assigning the "parent" record to the Document 7record I get the following list:

 

Document 1

Document 2

Document 3

Document 4

Document 5

Document 6

Document 7

...

 

Instead of:

 

Document 1

Document 2

Document 3

Document 4

Document 5

Document 6

 

...

 

To achieve that, I have tried to set up the self-join as follows:

 

Document_PARENT:: _kp_DocumentID ·=/ Document:: _kp_DocumentID

AND

Document_PARENT:: _kp_DocumentID = Document:: __kf_DocumentID

 

But it doesn't work.

 

I have tried to set up a filter in the portal on the Document_Picker layout but that is not allowed by FMP.

 

 

Any guidance as to how to set up a self-join not showing the current record?

Outcomes