Reference products lost

Question

When validating to try to fix another problem, I noticed that there all of a sudden was no reference products for a bunch of my processes. In this specific case the reference products was lost for a set of processes I created for the production of pellets (I have different versions). It was an easy fix, but I did also notice that in an entire database I imported (the ELCD) which takes more time to fix. Is there something I do wrong or is there a bug in the program?

Comments

I have also had this occur during multiple public data publication quarters causing publication errors. I had made some data manipulations with the SQL tool in openLCA while connected to the Collaboration Server (e.g., updating product flow units) and, though I checked my SQL scripts and was sure none of them should have caused quantitative reference flows to disappear, there were multiple quantitative references missing from processes. For the affected processes, the quantitative references shifted to random elementary flow outputs, e.g., so the result of a manfuacturing process might just be 'Arsenic'.  All have been corrected and with two internal data curation consequences: 1) I no longer use the SQL tool while connected to the Collaboration Server, and 2) a database QC procedure has been enacted to ensure all process quantitative references are semantically logical and are 'product' type flows.

---

Ah

I never used the collaboration server, nor do I use SQL scripts so it must be something else in my case that is causing the problem. I did notice however, after re-importing my ELCD database that it changed the reference flow to an elementary flow just as you write. How did you fix that in the QC procedure?

---

I only need to manage < 1000 product flows of which I am pretty familiar so I generate the quantitative references listed by process and flow type to quickly visually scan the quantitative reference names and flow types with this SQL script:

# Extracts quantitative references by process and flow type:

SELECT p.ID AS Process_ID, p.name AS Process, p.f_quantitative_reference AS Exchange_ID, f.name AS Flow_Name, f.flow_type AS Flow_Type
FROM tbl_processes AS p
LEFT JOIN tbl_exchanges AS e
ON e.ID = p.f_quantitative_reference
INNER JOIN tbl_flows AS f
ON f.id = e.f_flow

---

There were other related-QC procedures that had to be developed because of these incidents. For example, if you had quantitative reference(s) disappear from a multi-output process, I found that, upon discovering and re-instating the quantitative reference, you then have to check that the default allocation factors also get re-instated correctly. Because the product flows don't disappear (just the quantitative reference designation at the process exchange-level), you can also identify 'disappearing quantitative references' by cross-checking the flows table with the exchange tables to look for product type flows that are not also an exchange of the output type (IS_INPUT = 0 in exchange table); that is, if you know which ones should have providing processes in your database, i.e., which ones are CUTOFFs and used only as inputs.

# Extracts product output flows by providing process with exchange details

SELECT pr.id AS Process_ID, pr.name AS Process_Name, f.ID AS FLOW_ID, f.REF_ID AS Flow_UUID, f.NAME AS Flow_Name, f.FLOW_TYPE AS Flow_Type, c.name AS Subcategory, (SELECT c.name FROM tbl_categories AS c WHERE c.f_category = c.ID) AS Category, f.f_location AS Geography, u.id AS Unit_ID, e.f_unit AS Exchange_Unit_ID, u.name AS Unit, pf.ID AS Flow_Property_Factor_ID, e.F_OWNER AS Exchange_Process, e.IS_INPUT AS Input_To, e.id AS Exchange_ID, e.F_DEFAULT_PROVIDER AS Default_Provider, e.AVOIDED_PRODUCT AS Avoided_Product
FROM tbl_flows AS f
LEFT JOIN tbl_exchanges AS e
ON e.F_FLOW = f.ID
LEFT JOIN tbl_processes AS pr
ON pr.ID = e.F_OWNER
INNER JOIN tbl_categories AS c
ON f.f_category = c.ID
INNER JOIN tbl_flow_properties AS p
ON f.F_REFERENCE_FLOW_PROPERTY = p.ID
INNER JOIN tbl_unit_groups AS ug
ON ug.ID = p.F_UNIT_GROUP
INNER JOIN tbl_units AS u
ON u.id = ug.F_REFERENCE_UNIT
LEFT OUTER JOIN tbl_flow_property_factors AS pf
ON pf.f_flow = e.f_flow
WHERE e.IS_INPUT = 0
AND f.FLOW_TYPE = 'PRODUCT_FLOW';

*Sort by process name and conditionally format duplicates to examine multi-output processes' outputs

You can also extract all product flows and cross-check these results with the list generated above to see if there are product flows not being listed in the outputs that should be.