adding/changing filenames of documents

[davideps]

Hello,

I am new to SugarCRM and am using the community edition Version 6.1.4 (Build 5822) on Ubuntu 10.04, installed via faststack.

I imported documents indirectly by importing notes that linked to document titles. But, I cannot edit these documents at all. They do not have filenames associated with them and the edit/browse button is not available in the edit layout. Because several required fields are empty, it will not save. How can I add the filenames and other required data? If this is not possible, is there another way to import at least some of the data necessary to create documents? I have a spreadsheet of document names and full paths to text and pdf files.

-david

[sts]

Are you sure you have imported the files itself to sugar? Otherwise the notes link to the attached document is corrupt.

To import notes with attachment there are two steps neccessary:
1) import the notes from a csv-file, e.g. Notes.csv
2) copy the attached files into the directory specified in config.php with parameter "upload_dir" (in my case it's "cache/upload" - I didn't change the default setting). Each file has to be named as the id (!) of the note to which it belongs, e.g. the file name could be "899ca248-c177-32e9-24dc-4dbe711d0ba1".

In step 1) there might be another difficulty: per default sugar does not want to import 'filename' and 'file_mime_type' fields even if they are filled correctly in the Notes.csv-file and these fields have been marked as importable in Studio. The reason is, that in modules/Notes/Vardefs.php these fields have been marked as 'importable' => 'false'.
To change this you have to create a file e.g. vardefs.ext.php in directory custom\Extension\modules\Notes\Ext\Vardefs which contains these rows:

 <?php
$dictionary['Note']['fields']['file_mime_type']['importable']=true;
$dictionary['Note']['fields']['filename']['importable']=true;
?>  

After performing a "Admin->Repair->Quick repair and rebuild" it is possible now to import a note including the reference to an attachment (not the attachment itself !). To get the attachment into the system now step 2) as described above is needed.

After finishing both steps 1) and 2) it should be possible to do everything with the imported note and it's attachment as with notes created manually.

[davideps]

Stefan,

Your response was extremely helpful, though I was not clear enough with my question. I think my problem is in the Documents module. I created a relationship between Notes and Documents. I then imported notes and mapped one column to Document name. So, now I have a bunch of documents without associated filenames or revisions and cannot edit them to correct the problem. Below I pasted code from modules/Documents/vardefs.php that I think is preventing me from just manually adding the information one document at a time in the sugarCRM interface. Could you recommend a fix similar to your first post?

Also, I actually didn't realize sugarCRM could actually import file content. I thought "filename" was just a link. If the content is fully indexed and quickly searchable, that's a plus--otherwise I just need quick access to bibliographic info and a link to the file. Here is the possibly problematic code:

'document_name' =>
array (
'name' => 'document_name',
'vname' => 'LBL_NAME',
'type' => 'varchar',
'len' => '255',
'required'=>true,
'importable' => 'required',
),
'name'=>
array('name'=>'name', 'vname' => 'LBL_NAME', 'source'=>'non-db', 'type'=>'varc
har'),
'filename' =>
array (
'name' => 'filename',
'vname' => 'LBL_FILENAME',
'type' => 'file',
'source' => 'non-db',
'comment' => 'The filename of the document attachment',
'required' => true,
),

'uploadfile' =>
array (
'name'=>'uploadfile',
'required' => true,
'vname' => 'LBL_FILENAME',
'type' => 'file',
'source' => 'non-db',
),

[skipped stuff]

'revision' =>
array (
'name' => 'revision',
'vname' => 'LBL_DOC_VERSION',
'type' => 'varchar',
'reportable'=>false,
'required' => true,
'source'=>'non-db',
'importable' => 'required',
'required' => true,
),

[sts]

How did you import notes ? Can you give an example (maybe screenshot) of a table + document which fields are set and which are not?

I tried to do the same as I thought you did (creating a one-to-many relation from notes to documents, import notes from csv file), but during the import there is only the possibility to import data into the 'notes' db table, not into 'documents' (the table which contains the data for the documents module) or 'notes_documents_c' (the table that manages the one-to-many-relation between the notes and documents). So it is unclear to me what exactly you did here: "mapped one column to Document name".

Neither 'documents' nor 'notes_documents_c' can be filled using the import mechanism.

The files for documents are stored in the same directory as files attached to notes. They too are named with an id, but not the id of the document itself but the 'document_revision_id' (one of the fields in 'documents' table).

[davideps]

Stefan,

Thank you for your patience. Hopefully the screenshots and data attached will help clarify what I am doing (or trying to do!).

Ideally, I'd like to import bibliographic information (Title, Year, Etc) attach it to Authors (Contacts) and to Notes (many notes per document). Document content can be loaded into SugarCRM, but I'm more concerned with storing links to the files. I don't mind importing the data via several passes in different modules.

-david

[sts]

Hi David,

I've created the same relation documents=>notes in my 6.2.0beta test installation (running under WindowsXP) and imported the notes using your csv file. Except slight differences (e.g. the field 'Documents_Notes' in your notes detail view is called 'Documents' in my version) all works fine. In my case there is no problem editing the imported document data. Off course 'Filename' is not set after import, but I have a button to attach a file (Sugar copies the file into the Sugar directory cache/upload) and fill this. So it is no problem to change and save the document data.
I'm not sure at the moment why there is no such button in your installation. Is this only a problem for documents created during notes import or is it the same if you try to manually create a document? Is there a upload button in the 'Create Note or Attachment' page? Do you have the chance to try out version 6.2.0beta (in a separate test environment off course)?

Regards
Stefan

[davideps]

Hi Stefan,


When creating a Document or Note from scratch, I have a "Choose File" button as shown in the attached screenshots. Is this what you mean by an "upload" button? I thought this was just creating a link to the current file.

I tried 6.2 first, but had problems installing beyond those covered in the installation script's instructions. There were some permission denied errors in a php script and I could not seem to get it to run all the way through. I could try again. The newest faststack I saw was for version 6.1 and it was up and running in 10 minutes.

I could not find a way to import metadata about documents (Title, Year, etc) in 6.1, which is why I'm starting to do so indirectly via importing Notes. Is there a way to directly import documents and/or document metadata in 6.2?

In your opinion, what is the fastest/easiest way to import:

metadata for 500 documents
four thousand notes linked to those documents
about 400 contacts linked to those documents


thank you again for your help,
-david

[sts]

Yes, "Choose File" is the upload button I mentioned. The styling of this button and it's label depends on the browser and operating system, so it looks different in my installation.

Using this button and the functionality behind it does not create a link to the file but copies it from the selected directory on the user's environment into the <sugarcrm>/cache/upload directory on the sugarcrm server. This ensures that every sugar user who is alllowed to see a document is able to get access to it via documents and notes module respectively.

[sts]

There is one case when the document editview does not show an upload button: in my case after uploading a document if I open the editview again. Then the 'filename' field is set and cannot be changed (but additional data can be set).

It is still unclear to me why in your case there is no upload button even if the filename is empty.

Maybe this could be a workaround: try adding a new revision to the document (select "Create" button in section "Document Revisions" in detailview of a document). This opens a page where another revision of that document can be uploaded.

[sts]

What can you do to repair the documents which have no filename when there is no upload button?

I've tried out how to add a file to documents module if the upload button is not available. Here my results. For step 2 you need access to the database:

1) copy the file to cache/upload directory on the sugar server. Rename the file to the 'document_revision_id' (from 'documents' database table) of the document object for which the file has to be attached. The new name will be '3f4a7d4a-3d42-f240-dca7-4dbfc7e0ccaf' for example.

2) Fill the needed parameters in 'document_revisions' table with this sql statement:

 update document_revisions 
set filename='FILENAME_WITH.EXTENSION', file_ext='EXTENSION', file_mime_type='MIME_TYPE' 
where id='THE_NEEDED_DOCUMENT_REVISION_ID' and revision=1 
  

FILENAME_WITH.EXTENSION => the original filename (you can choose ANY aceptable filename here...)
EXTENSION => the extension of the file, e.g. 'doc', 'pdf', 'jpg' (should be the real one, otherwise you might not be able to open the file afterwards)
MIME_TYPE => depends on the mime type of the document, e.g. 'application/msword', 'application/pdf', 'image/jpeg' (the same as before)
THE_NEEDED_DOCUMENT_REVISION_ID => the value of the document_revision_id as described before, something like this: '3f4a7d4a-3d42-f240-dca7-4dbfc7e0ccaf'
revision => should be always '1' after a notes-import

When I perform these steps on my 6.2.0beta installation I can set the filename for the document without using the upload mechanism in sugar. I can open the file without problems then when clicking on the link in sugar documents module.

For a larger number of documents to import you'll have to look at a mechanism to automate the steps described here. Some ideas on that, e.g. using Talend, you can find here: http://www.sugarcrm.com/forums/showthread.php?t=35330.