Cycle/loop relationship

[Buckie]

Hi folks. So the problem is not really that complex I reckon but I'm missing some piece of a puzzle here and can't make an elegant solution.

Consider a situation where we have a specific part number that's been superseded by another part number which in turn may or may not have been superseded by yet another part number. Sometimes there's no supersession and sometimes there are 5 or any other number.

A relationship is simple: BasePN = NewerPN, all in a single self-relating table. The problem is that I need to cycle through that list not in a script but rather get an instant result inside a field.

What I need to get is a list, starting from some arbitrary point (record) in that chain up until the very recent one which we know is that last one because its NewerPN field is empty.

One inelegant solution that comes to mind is to create a very long chain of relationships but that will artificially limit me to some maximum number of lookups and I don't like that.

What would be the right way to accomplish that?

[eos]

You only need a newerID or an olderID, but not both.

The idea is to use recursion; If you have a baseID that refers to the preceding SN, and a relationship where

SN::id = SN_newer::id_base

you can – from the context of SN – create a recursive calculation, say, cSNListUpwards as

SerialNumber &
Case ( 
  not IsEmpty ( SN_newer::id ) ; 
  ¶ & SN_newer::cSNListUpwards
)

Note that when you create that field you cannot reference itself because it doesn't exist yet; enter a dummy expression to create it, then close, re-open and re-define it.

[Buckie]

eos,

sorry, perhaps I'm being dumb but I'm having a hard time understanding what you said.

You say I need a newerID or an olderID (by ID we assume PN, right?) but later you reference "id" and "id_base" which are... the same thing basically?

Do SN and SN_newer have the same contents, i.e. the same table?

Is this a one-to-many relationship you're referring to? I just don't understand what makes a calculation recursive if we have a one-to-one relationship.

Still not getting it.

Edited July 31, 2015 by Buckie

[eos]

later you reference "id" and "id_base" which are... the same thing basically?

Do SN and SN_newer have the same contents, i.e. the same table?

SN is the table that holds your serial numbers. SN and SN_newer are two occurrences of that table.

id is the primary key; id_base holds the id of the record the current one is based on (i.e. its predecessor, or – in your terminology – the one it supersedes).

I just don't understand what makes a calculation recursive if we have a one-to-one relationship.

 It is recursive because the field itself – or, more precisely, an instance of that field via a relationship - is used in its definition.

See this file: Recursive_eos.fmp12.zip

Edited July 31, 2015 by eos

[Buckie]

Well, but do we need keys and serials here? Basically PNs act as serials themselves as they're all unique.

Take a look at the following screenshot that illustrates the problem.

Example #1: PN101 got superseded by PN201. PN201 in turn wasn't superseded by anything: it's the last PN in the chain.

Example #2: PN107 got superseded by PN205. We look up PN205 and see that it was superseded by PN303. We look up PN303 and see that it was superseded by PN408. We look up PN408 and see that it wasn't superseded by anything, therefore it's the last one in the chain.

What I need: I want to have another column which in case of PN107 would contain the following:

PN205

PN303

PN404

 

In case of PN205 it would contain this:

PN303

PN404

 

All using a calculation/relationship and no scripts.

Edited July 31, 2015 by Buckie

[comment]

What I need to get is a list, starting from some arbitrary point (record) in that chain up until the very recent one which we know is that last one because its NewerPN field is empty.

I believe I would prefer to note which part a new record replaces in the replacement record, rather than in the original one. But there is no reason why it couldn't work your way too - have a look at the attached example.

 

 

 

RecursiveReplacements.fp7

[eos]

Well, but do we need keys and serials here? Basically PNs act as serials themselves as they're all unique.

It doesn't really matter which fields you use to create the relationship; I happen to always use a primary key, because they are guaranteed to be unchanging – which can not be said for a natural key. 

Take a look at the following screenshot that illustrates the problem.

There is not really a problem; if you don't want to include the current value in the generational list, use the calculation

Case ( 
  not IsEmpty ( SN_newer::id ) ; 
  SN_newer::SN & 
  Case ( 
    not IsEmpty ( SN_newer::cSNListUpwards ) ; 
    ¶ & SN_newer::cSNListUpwards 
  )
)

[Buckie]

Sorry, I just don't see how your solution manages the problem at hand. You simply listed a bunch of non-intersecting SNs and IDs. There are no chains at all in the example.

[comment]

Sorry, I just don't see how your solution manages the problem at hand. You simply listed a bunch of non-intersecting SNs and IDs. There are no chains at all in the example.

Is this addressed to me?

[eos]

Is this addressed to me?

Good question …

Here's a slightly different example, using the OP's sample data.

 Recursive_eos.fmp12.zip

[Buckie]

Comment,

sorry, no! I was talking to eos and didn't actually see your reply before I refreshed the page.

Your example is exactly what I was looking for.

I just need some time to wrap my head around the List of a List... but it works! So "List" was actually the solution here.

And now actually eos's solution works too apart from the fact that it shows one extra record (i.e. duplicates the PartNumberNew) but it also seems to be alright.

But I totally don't get how it works in eos's example.

Edited July 31, 2015 by Buckie

[eos]

sorry, no! I was talking to eos

My sample does solve the problem; you may have wanted to click inside the result list to actually see the list.

And it doesn't matter whether you use List() to create the list; it is about recursion.

[Buckie]

Sorry guys, I'm not good enough and got stumped, but thanks to your examples I slowly start to understand the idea: the field calling itself, if I my understanding is correct. In eos's example it's the Case function that stops recursion if it finds that the field containing newer PN is empty and in comment's case List solves the problem on its own as it only lists things that are present in the chain.

And yes, I did forget to click into the fields.

Thank you so much everyone.