[query] Reverse "search" for an organisation name (but no reference)

[stevieflow]

We know this is good and useful, and anchored in the specific string in the participating-org/@ref field

https://d-portal.org/ctrack.html?/participating-org@ref=XM-DAC-47066#view=main

A question we may also get asked is "can we consolidate instances where a publisher has used our name, but not the reference?"

This isn't as straightforward, as we don't have a clear guidance on what exactly a name should be

The query is whether it could be feasible to take the various strings we know are in use (as we've seen in the test environment) and then search for any use of these, but without an organisation reference....

Thanks!

[xriss]

Its easy to list such activities so you can go complain to the publishers, doable now in dquery but a bit complicated.

https://d-portal.org/dquery/#SELECT%20DISTINCT%20aid%0AFROM%0A(%0ASELECT%20*%0AFROM%20xson%20WHERE%20root='/iati-activities/iati-activity/participating-org'%20%0AAND%20xson-%3E%3E'@ref'%20IS%20NULL%0A)%20a%0AINNER%20JOIN%0A(%0ASELECT%0Axson-%3E%3E'@ref'%20AS%20%22@ref%22%20,%0Axson-%3E'/narrative'-%3E0-%3E''%20AS%20%22/narrative%22%0AFROM%20xson%20WHERE%20root='/iati-activities/iati-activity/participating-org'%20%0AAND%20xson-%3E%3E'@ref'='XM-DAC-47066'%0AGROUP%20BY%201,2%0A)%20b%0AON%20xson-%3E'/narrative'-%3E0-%3E''%20=%20b.%22/narrative%22%0A

That give Activity IDs that should probably have XM-DAC-47066 added as a @ref in participating as it matches one of the names used.

Another option is finding and adding appropriate @ref at import time, probably not too hard just going on exact string matches but it would be under the dportal fixes the data rather than the publishers fix the data umbrella.