Community
Showing results for 
Search instead for 
Do you mean 
Reply

Is there a way to compare attachments filed in ACT to documents in the attachment folder?

Accepted Solution Solved
Bronze Contributor
Posts: 1,446
Country: USA
Accepted Solution

Is there a way to compare attachments filed in ACT to documents in the attachment folder?

Is there any way to compare documents attached to ACT to all the documents in the attachment folder?

 

I'm hoping that there is a way to create a list of documents actually attached to ACT and export to Excel.

 

Then I can figure it out.  Just don't know if there is a way to export from ACT a list of all documents that are attached.

 

Why?  My client has been using the ACT Attachment folder to store all documents regardless if they were ever attached to the document folders.

 

While the question is clear to me, I may have left something out of the question that would help.  If so, please reply with what you need.

 

Many thanks for any assistance.

John Purdy
ACT! Premium V. 18
HP ENVY 17m, 16GB, & Office 365, 32bit, 1TB HDD.

Accepted Solutions
Solution
Accepted by topic author jnpurdy
‎02-15-2018 10:51 AM
Nickel Super Contributor
Posts: 331
Country: Germany

Re: Is there a way to compare attachments filed in ACT to documents in the attachment folder?

To find out what you need requires SQL queries of the more complicated kind as you will have to “scan” several areas of the database.

 

Here is some German sample code from a script I’ve used for ACT!13.

It IS NOT READY TO USE, it just points out the complexity of the task.

 

Looks like you will need the help of a local ACC then.

 

 

Dim cSQLSelectAttachmentsACT13 :

cSQLSelectAttachmentsACT13 = "SELECT TBL_ATTACHMENT.FILENAME AS Dateiname, TBL_ATTACHMENT.DISPLAYNAME AS Anzeigename, TBL_NOTE.DISPLAYDATE AS Notizdatum, " & _

                                                                                              " TBL_NOTE.NOTETEXT AS Notiztext, TBL_ACTIVITY.STARTTIME AS Tätigkeitsdatum, TBL_ACTIVITY.REGARDING AS Tätigkeitsbetreff, " & _

                                                                                              " TBL_ACTIVITYTYPE.NAME AS Tätigkeitstyp, TBL_HISTORY.STARTTIME AS Protokolldatum, TBL_HISTORY.REGARDING AS Protokollbetreff, " & _

                                                                                              " TBL_HISTORYTYPE.NAME AS Protokolltyp, TBL_ATTACHMENT.ATTACHMENTID, TBL_WORKFLOWDEF.NAME AS Workflowname " & _

                                                                                              "FROM TBL_HISTORYTYPE INNER JOIN " & _

                                                                                              " TBL_HISTORY ON TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID RIGHT OUTER JOIN " & _

                                                                                              " TBL_WORKFLOWDEF RIGHT OUTER JOIN " & _

                                                                                              " TBL_ATTACHMENT ON TBL_WORKFLOWDEF.WORKFLOWDEFID = TBL_ATTACHMENT.WORKFLOWDEFID ON " & _

                                                                                              " TBL_HISTORY.HISTORYID = TBL_ATTACHMENT.HISTORYID LEFT OUTER JOIN " & _

                                                                                              " TBL_ACTIVITYTYPE INNER JOIN " & _

                                                                                              " TBL_ACTIVITY ON TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_ACTIVITY.ACTIVITYTYPEID ON " & _

                                                                                              " TBL_ATTACHMENT.ACTIVITYID = TBL_ACTIVITY.ACTIVITYID LEFT OUTER JOIN " & _

                                                                                              " TBL_NOTE ON TBL_ATTACHMENT.NOTEID = TBL_NOTE.NOTEID "

Andreas Schlesselmann
Melville-Schellmann GbR
Germany
www.melville-schellmann.de

View solution in original post


All Replies
Employee
Posts: 244
Country: USA

Re: Is there a way to compare attachments filed in ACT to documents in the attachment folder?

[ Edited ]

http://www.actaddons.com/addons/the_filemanager.asp

Possibly reach out to Corelogix support team to see if their file manager Act! add-one creates a list

Solution
Accepted by topic author jnpurdy
‎02-15-2018 10:51 AM
Nickel Super Contributor
Posts: 331
Country: Germany

Re: Is there a way to compare attachments filed in ACT to documents in the attachment folder?

To find out what you need requires SQL queries of the more complicated kind as you will have to “scan” several areas of the database.

 

Here is some German sample code from a script I’ve used for ACT!13.

It IS NOT READY TO USE, it just points out the complexity of the task.

 

Looks like you will need the help of a local ACC then.

 

 

Dim cSQLSelectAttachmentsACT13 :

cSQLSelectAttachmentsACT13 = "SELECT TBL_ATTACHMENT.FILENAME AS Dateiname, TBL_ATTACHMENT.DISPLAYNAME AS Anzeigename, TBL_NOTE.DISPLAYDATE AS Notizdatum, " & _

                                                                                              " TBL_NOTE.NOTETEXT AS Notiztext, TBL_ACTIVITY.STARTTIME AS Tätigkeitsdatum, TBL_ACTIVITY.REGARDING AS Tätigkeitsbetreff, " & _

                                                                                              " TBL_ACTIVITYTYPE.NAME AS Tätigkeitstyp, TBL_HISTORY.STARTTIME AS Protokolldatum, TBL_HISTORY.REGARDING AS Protokollbetreff, " & _

                                                                                              " TBL_HISTORYTYPE.NAME AS Protokolltyp, TBL_ATTACHMENT.ATTACHMENTID, TBL_WORKFLOWDEF.NAME AS Workflowname " & _

                                                                                              "FROM TBL_HISTORYTYPE INNER JOIN " & _

                                                                                              " TBL_HISTORY ON TBL_HISTORYTYPE.HISTORYTYPEID = TBL_HISTORY.HISTORYTYPEID RIGHT OUTER JOIN " & _

                                                                                              " TBL_WORKFLOWDEF RIGHT OUTER JOIN " & _

                                                                                              " TBL_ATTACHMENT ON TBL_WORKFLOWDEF.WORKFLOWDEFID = TBL_ATTACHMENT.WORKFLOWDEFID ON " & _

                                                                                              " TBL_HISTORY.HISTORYID = TBL_ATTACHMENT.HISTORYID LEFT OUTER JOIN " & _

                                                                                              " TBL_ACTIVITYTYPE INNER JOIN " & _

                                                                                              " TBL_ACTIVITY ON TBL_ACTIVITYTYPE.ACTIVITYTYPEID = TBL_ACTIVITY.ACTIVITYTYPEID ON " & _

                                                                                              " TBL_ATTACHMENT.ACTIVITYID = TBL_ACTIVITY.ACTIVITYID LEFT OUTER JOIN " & _

                                                                                              " TBL_NOTE ON TBL_ATTACHMENT.NOTEID = TBL_NOTE.NOTEID "

Andreas Schlesselmann
Melville-Schellmann GbR
Germany
www.melville-schellmann.de