Get the ID of a previous row that is not always a static number of rows before the row in question?

Question

I know my question sounds generic, but I wasn't able to specify all of the details in the title without writing a paragraph. I'll try to make it straightforward here.

I have a database table of forms/ordered form-items. Each form-item can be indented a certain 'level'. For example, all rows where Indent Level (IL) = 0 are top-level items on that form. If IL = 1, and comes directly after an IL = 0 row, then the IL1 row's parent would be that previous IL0 row. But, there can be X number of IL1 items, whose parent would all be that same previous IL0. This pattern can continue down to 6 levels of nesting (where IL6's parent would be the previous IL5)

Here is an example of a form, with the correct ParentItemID filled out by me manually (this is the column I'm trying to calculate dynamically):

ItemID     FormID     SortOrder     Indent     Description          ParentItemID
1000       1          1             0          Main Item 1          NULL
1001       1          2             0          Main Item 2          NULL
1002       1          3             1          Sub Item 1           1001
1003       1          4             1          Sub Item 2           1001     
1004       1          5             2          Sub Item 2-1         1003
1005       1          6             2          Sub Item 2-2         1003
1006       1          7             2          Sub Item 2-3         1003
1007       1          8             3          Sub Item 2-3-1       1006
1008       1          9             1          Sub Item 3           1001
1009       1          10            0          Main Item 3          NULL

Here is what this would look like when actually translated from the raw data in the db into a form, just as a visual example:

I'm trying to use LAG, which works on the first IL1 item, but from there each of the following items refers to the previous row. I guess I could run a bunch of updates, filtering down to only each indent level, but that isn't ideal. I see that LAG takes an offset parameter, but for some reason I can't wrap my head around how i could calculate that dynamically to always refer to the previous item (based on sort order) that has an indent level of 1 less than the current indent level.

Here is the sql to create the table and populate with data:

create table FormItems(
    ItemID int,     
    FormID int,
    SortOrder int,
    Indent int,
    Description nvarchar(100),
    ParentItemID int
    )

insert into FormItems
select 1000,       1,          1 ,            0,          'Main Item 1'          ,NULL union
select 1001,       1,          2 ,            0,          'Main Item 2'          ,NULL union
select 1002,       1,          3 ,            1,          'Sub Item 1'           ,1001 union
select 1003,       1,          4 ,            1,          'Sub Item 2'           ,1001 union     
select 1004,       1,          5 ,            2,          'Sub Item 2-1'         ,1003 union
select 1005,       1,          6 ,            2,          'Sub Item 2-2'         ,1003 union
select 1006,       1,          7 ,            2,          'Sub Item 2-3'         ,1003 union
select 1007,       1,          8 ,            3,          'Sub Item 2-3-1'       ,1006 union
select 1008,       1,          9 ,            1,          'Sub Item 3'           ,1001 union
select 1009,       1,          10,            0,          'Main Item 3'          ,NULL

Answer 1

Probably the simplest method is apply:

select t.*, tp.itemId
from t outer apply
     (select top (1) tp.*
      from t tp
      where tp.formId = t.formId and
            tp.indent = t.indent - 1 and
            tp.sortorder < t.sortorder
      order by tp.sortorder desc
     ) tp;