Hi all. I’m trying to generate UIDs for helping out with site inspection reports for structures. The structures are comprised of “Components” and each “Component” is comprised of “Sub-Components”. Each “Sub-Component” has a inspection lists (“Lists”) that are checkboxes that need to occur for each “Sub-Component”. Each checkbox is a coded item (“Code”) that must be inspected. There are multiple “Codes” for each of the “Lists”.
The UID format that is desired is this:
“Site ID”. “Component ID” . “Sub-Component ID” . “Code”
The problem I am running into is that the data have been provided in Excel. There is a “SiteVisits” Tab, and a “Lists” tab. The SiteVisits tab has 350+ records, with columns containing Site IDs, Component IDs and Sub-Component IDs, and a column on the “Lists” required for each Site.

The “Lists” Tab contains a descriptor of each checkbox and the associated “Code”. What I am trying to do is concatenate the easy parts of the UID (“Site ID”. “Component ID” . “Sub-Component ID”), with each of the “Code” values on the “Lists” tab.

I am trying to tell FME to explode the column that outlines all the “Lists” on the SiteVisits tab, and then Join all of the relevant “Codes” for whichever “List” is required for that site, and then generate the codes with an AttributeManager. I’d then end up with a whole bunch of UIDs, examples are shown below:

There are around 350 Sites. Each site has multiple lists, and each list has multiple codes, and there are a total of 73 lists, so I’m looking at an output of around 20000+ UIDs. I have attached a trimmed copy of the data provided. This contains the “SiteVisits” and “Lists” tabs mentioned above, with examples of the desired UID outputs in the “SiteVisits” tab.
If anyone has any ideas, it would be greatly appreciated :)