Loop through table to find first value

[ciaran_mcgowan]

Hi all,

I have an issue where I have to find the first ID of a row in a table for each row. Every row contains an ID, but it also has the ID of that row's predecessor and there is no limit to how many predecessors a row can have. See example below:

ID	Name	Previous ID	Complete
1	Person 1		No
2	Person 2	1	No
3	Person 3		No
4	Person 4	3	Yes
5	Person 5	2	No
6	Person 6	5	Yes

I need to add a column called Original ID which will look like below. Any thoughts?

ID	Name	Previous ID	Complete	Original ID
1	Person 1		No	1
2	Person 2	1	No	1
3	Person 3		No	3
4	Person 4	3	Yes	3
5	Person 5	2	No	1
6	Person 6	5	Yes	1

[Anil_Babu_Samineni]

What was the logic behind? that may helps you

[Chanty4u]

how the original id column defined from?  what is the caluclation ?

[ciaran_mcgowan]

I don't have access to the logic but it looks to be incremental rows. No set order or logic that I can use to link. I'm thinking that this is going to require multiple FOR loops.

[rahulpawarb · Accepted solution]

This can be achieved using Hierarchy function. Please refer attached sample application.

Regards!

Rahul Pawar

[ciaran_mcgowan]

I created a hierarchy and joined it to my table using the ID fields and brought in the new field which was the top position from the hierarchy.

Thanks!

[rahulpawarb]

Cheers!

Rahul Pawar