we need a report of the hard-coded access notes in ACLU that diverge from their ancestor notes

[regineheberlein]

User story

Will is requesting a report of any hard coded series or subseries restriction notes that are different from what is at the level above it.

Sudden priority justification

This is part of wrapping up the restrictions review project. The staff member who did the review is leaving at the end of next week and we need to do QA before then.

Resource record URI's to pull this data from:

1711, 1712, 1713, 1714, 1715, 1716, 1717, 1718

Fields to include:

uri, eadid, cid, title, date, note type, note text, level

[regineheberlein]

The logic should be: any aggregate-level restriction notes that differ from their child notes should be flagged

[regineheberlein]

Further logic tweak: do not include leaf components with blank notes; do not include the collection level.

[regineheberlein]

#when level (column G) is 0 (collection), do nothing
=IF(G3=0,"", 
#otherwise, if I is blank, flag it as missing the required attribute (these are migration artifacts)
    IF(AND(I3="", J3<>""), "missing required type attribute", 
        #otherwise, if level is smaller than the next row's (i.e. it's an aggregate) 
        IF(G3<G4, 
            # check that value of I is the same until level of G decreases by one
            IF(OR(AND(I3="", J3=""), INDEX(FILTER(I4:I$55096,G4:G$55096>G3),SEQUENCE(1,1,1,1))="", INDEX(FILTER(I4:I$55096,G4:G$55096>G3),SEQUENCE(1,1,1,1))=I3, "", 
           # otherwise, flag as type discrepancy
           "type discrepancy"), 
        #otherwise, if G is the same level as previous, grab flag from previous
#otherwise don't flag
IF(AND(G3=G2, AND(I2<>"", J2<>"")), M2, ""))))

[regineheberlein]

This needs to be run one more time at the very end of the review process