[Vtigercrm-developers] vtiger_attachments vs vtiger_notes?

[Alan Lord]

On 02/12/14 07:03, Vikas Jain wrote:
> Alan,
>
> vtiger_notes has document information, if it is related to a internal
> file then its filelocationtype is set "I" which means internal. This
> document will have a file relation stored in seattachmentsrel table, to
> get the actual detail of file like name, path, size etc you need to look
> into attachments table. If a Contact/Lead etc is related to a Document
> then we store the info into senotes table, so you need to first query
> the senotes table to get the related documents then join notes table
> with seattachments and attachments table to get the file information.
> Let me know if this helps.

Thanks Vikas,

I worked it out yesterday in the end ;-) I'm using a query like this:

> SELECT vtiger_attachments.attachmentsid AS fileid,
>        vtiger_attachments.path AS filepath,
>        vtiger_attachments.name AS filename,
>        vtiger_leaddetails.leadid AS ownerid,
>        vtiger_crmentity.smownerid AS smownerid
> FROM vtiger_attachments
> INNER JOIN vtiger_seattachmentsrel
>   ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
> INNER JOIN vtiger_senotesrel
>   ON vtiger_senotesrel.notesid = vtiger_seattachmentsrel.crmid
> INNER JOIN vtiger_crmentity
>   ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
> INNER JOIN vtiger_leaddetails
>   ON vtiger_crmentity.crmid = vtiger_leaddetails.leadid
> WHERE vtiger_crmentity.setype = 'Leads'
> AND vtiger_crmentity.deleted = 0
> AND vtiger_leaddetails.converted = 0
> AND vtiger_attachments.name LIKE '%grid%'


Al