Excel Import - Excel data structure

[Dennis Riedel]

Hi Hannah

We are looking into the Excel import and the adaptations needed to make it work regarding the requirements gathered in the Helsinki meeting. Lukas explained to us the importance of documents and clauses, that can be replaced by future documents and clauses.
His example was as follows:

Document A with Clauses 1 and 2.
Document B with Clauses 3, 4 and 5
"Document B replaces Document A in the way that: Clause 3 replaces Clause 2, Clause 5 is new/added and Clause 1 is removed."

Looking at the Excelsheet (water-almost.final.xls) you have sent on Januar, 6th, we see the following columns for documents and clauses:

(Document)
"Part of direct followup (y/n)"
"If part (y): Code of following document"

(Clause)
"Has/is direct follow up (clause specific)"
"Has parent clause (y/n)"
"If parent: Number of parent clause"
"Is a parent clause"

I guess they are related to the concept described before with Document A and Document B. I will now try to take a practical example from the water Excel to see how this works:

Code of document: A/Res/56/192 (International Year of Freshwater)
This document has a follow-up, as noted in the columns "part of direct followup" and "Code of following document"
It has three clauses (2, 3, 4) and all of them have direct follow-ups (clauses) in the follow-up document, see "Has/is direct follow up (clause specific!)" and "Is a parent clause", both of them say "yes" for all three clauses.

We then look into the follow-up document A/Res/57/252, going directly to its clauses. It has six (2, 3, 4, 5, 6, 7) of which three have parent clauses, actually the three clauses from the "parent(?)" document (A/Res/56/192). From the example given above I would make the following conclusion:
A/Res/56/192 with clauses 2, 3 and 4
A/Res/57/252 with clauses 2, 3, 4, 5, 6, 7
"Document A/Res/57/252 replaces A/Res/56/192 in the way that (taken from the column "Has a parent clause: clause number of parent clause"): Clause 252:3 replaces 192:2, Clause 252:4 replaces 192:3 and Clause 252:7 replaces 192:4; Clauses 252:2, 252:5 and 252:6 are added.

Is that correct?

The clauses in document A/Res/56/192 themself have parent clauses (3,4,5) but I cannot find document "A/Res/56/192" as "follow-up document" in the column "If part (y): Code of following document" of any document. But I see below the document "A/Res/56/192" another one called "A/Res/55/196" which is related to the "Freshwater Year". But it does not contain any follow-up information. But the Excel is "work in progress", so maybe this pending.

I hope I explained myself. The way the Excel import worked so far did not take into account any replacement or succession of clauses or documents over others. It was just a straight sequential import.

In the wiki we have the following spec:

Document relations

• document relations aka parent id (optional about upto 15)
• make it possible to specify the type of relation, make possible to create the direction in both direction
• either select a document in the system or a quick way to create an "in draft" document
• followup: there can be zero or one
• recalls: there can be zero or many
• closely related: there can be zero or many

Clause relations

• closely related: there can be zero or many
• optionally make it possible to associate zero or many tags to the relation

I think "followup" is already taken care of, as described in the example above. Recalls I do not know. We have the "Recalled documents" sheet within the Excel, but I do not know what to make of it or how to relate the titles in there to any other document. What is "closely related"? Is this to be taken from the Excel?

I am sending this email to our mailing list at dev-un-in...@googlegroups.com. The mailing list will provide us with a central point for all the conversations regarding the project, all explanations and solutions we might come up with. It would also be nice if you could join the list, to be aware of any discussion or questions that come up to which you may provide helpful insights.

To conclude: Maybe you can give us some feedback on these columns I mentioned above and how they should help us to fullfill the requirements of document and clause relations.


Thank you! and Regards,
Dennis

[Dennis Riedel]

Hi Hannah

Thank you for the information.

>Indeed, the work here is not complete yet. I have cc'd Roxana Bobolicu, who has mainly been working on this excel sheet, so that we can complete this as soon as possible. We are having a chat on Thursday. Is that early enough for a newer >version?
Absolutely. At this time you can still introduce changes to the Excel file. So if you want to add columns for document tags, now is the time! :-)

>Recalled documents work as follows: a document x recalls documents y, z, a and b. The way we have inserted it in the excel sheet thus far (not too many though - as you can see, there are only 16 rows of recalled documents - works this >way: in row 2 of the main sheet, we have the Convention on the Rights of Persons with Disabilities. This document recalls all the documents that are mentioned in Row 2 in the "Recalled documents" sheet.
But the Excel itself, or better, the document "Convention on the Rights of Persons with Disabilities" does not give any hint that it is the document that recalls all the other ones from the "Recalled documents" sheet. I mean, there is no column that hints to that. Despite that it is the first document in the list... (?)

Anyway we can also introduce features later in the document administration, if the information is not to be taken from the Excel right now.

Regards,
Dennis

On Tue, Jan 19, 2010 at 12:55 PM, Hannah Birkenkötter <h.birke...@googlemail.com> wrote:

Hi Dennis, 

Looking at the Excelsheet (water-almost.final.xls) you have sent on Januar, 6th, we see the following columns for documents and clauses:

(Document)
"Part of direct followup (y/n)"
"If part (y): Code of following document"

(Clause)
"Has/is direct follow up (clause specific)"
"Has parent clause (y/n)"
"If parent: Number of parent clause"
"Is a parent clause"

I guess they are related to the concept described before with Document A and Document B. I will now try to take a practical example from the water Excel to see how this works:

Code of document: A/Res/56/192 (International Year of Freshwater)
This document has a follow-up, as noted in the columns "part of direct followup" and "Code of following document"
It has three clauses (2, 3, 4) and all of them have direct follow-ups (clauses) in the follow-up document, see "Has/is direct follow up (clause specific!)" and "Is a parent clause", both of them say "yes" for all three clauses.

We then look into the follow-up document A/Res/57/252, going directly to its clauses. It has six (2, 3, 4, 5, 6, 7) of which three have parent clauses, actually the three clauses from the "parent(?)" document (A/Res/56/192). From the example given above I would make the following conclusion:
A/Res/56/192 with clauses 2, 3 and 4
A/Res/57/252 with clauses 2, 3, 4, 5, 6, 7
"Document A/Res/57/252 replaces A/Res/56/192 in the way that (taken from the column "Has a parent clause: clause number of parent clause"): Clause 252:3 replaces 192:2, Clause 252:4 replaces 192:3 and Clause 252:7 replaces 192:4; Clauses 252:2, 252:5 and 252:6 are added.

Is that correct?

Yup, all of this is correct :) 

The clauses in document A/Res/56/192 themself have parent clauses (3,4,5) but I cannot find document "A/Res/56/192" as "follow-up document" in the column "If part (y): Code of following document" of any document. But I see below the document "A/Res/56/192" another one called "A/Res/55/196" which is related to the "Freshwater Year". But it does not contain any follow-up information. But the Excel is "work in progress", so maybe this pending.

Indeed, the work here is not complete yet. I have cc'd Roxana Bobolicu, who has mainly been working on this excel sheet, so that we can complete this as soon as possible. We are having a chat on Thursday. Is that early enough for a newer version? 

In the wiki we have the following spec:

Document relations

• document relations aka parent id (optional about upto 15)
• make it possible to specify the type of relation, make possible to create the direction in both direction
• either select a document in the system or a quick way to create an "in draft" document
• followup: there can be zero or one
• recalls: there can be zero or many
• closely related: there can be zero or many

Clause relations

• closely related: there can be zero or many
• optionally make it possible to associate zero or many tags to the relation

I think "followup" is already taken care of, as described in the example above. Recalls I do not know. We have the "Recalled documents" sheet within the Excel, but I do not know what to make of it or how to relate the titles in there to any other document. What is "closely related"? Is this to be taken from the Excel?

Recalled documents work as follows: a document x recalls documents y, z, a and b. The way we have inserted it in the excel sheet thus far (not too many though - as you can see, there are only 16 rows of recalled documents - works this way: in row 2 of the main sheet, we have the Convention on the Rights of Persons with Disabilities. This document recalls all the documents that are mentioned in Row 2 in the "Recalled documents" sheet. We have inserted a minimum of information for each recalled document, namely the name, code and date of adoption, the legal value and type of document as well as a link to the document. Further, we have indicated whether the recalled document is also part of the main sheet. I hope this makes things clearer! I don't know whether it makes sense to import this information from the excels - given that we haven't done that much work in this regard, we could also manually insert these document-document relations later on, once the backend database is ready. It depends on how much work it would be for you to program an import for this sheet (I guess). 

On the "closely related" option: It is different from both recalled documents and direct follow-up. Closely related documents are documents that appear as a "package" (most of the time; sometimes, there are more than two docs of this sort). An example would be a declaration from a world summit and a plan of action. These documents need to be presented together. However, this information is not in the excel; we thought it might make most sense to insert this manually once we have imported all the data from the excels into the backend database. 

Lastly, I have a question regarding tags: Thus far, we don't have document tags, but only clause tags in the excel sheet. Would it make a great difference for the programming if we added a couple of columns for document tags? Seeing that we have both document and clause tags, it would be a lot of work if we had to attribute all the document tags manually after having imported the excel. It would be great if you could let me know if that's possible. We would then create a new template and send it to you as soon as possible. Thanks a lot!!

I am sending this email to our mailing list at dev-un-in...@googlegroups.com. The mailing list will provide us with a central point for all the conversations regarding the project, all explanations and solutions we might come up with. It would also be nice if you could join the list, to be aware of any discussion or questions that come up to which you may provide helpful insights.

I'll definitely join the group :) 

Thanks a lot for all your work and let me know if anything else is unclear regarding the excels!

Best wishes, 

Hannah