Q: Which SQL data types are supported

A: A.N.D.I. uses LS:DO to access sql data

Some data types are definitly not supported by Lotus Notes LS:DO

You should retype some fields using the 'CAST' statement in the SQL query 'SELECT' like this:

SELECT F1, CAST(YESNO_FIELD AS INTEGER), F2 ...

Q: How to attach files in a rich text field during import?

I have a file containing 2 fields: 'Code' and 'FilePath'. 
Filepath contains a valid path to a file (i.e. 'C:\Docs\test.doc')

A: We will use the QuerySaveScript feature in the 'Process/Extra Processing' section to add some LotusScript when the document is saved.

Dim rtitem As NotesRichTextItem, FilePath As string  
FilePath = RecordDoc.GetitemValue("FilePath")(0)  
' create a new rich text item  
Set rtitem = New NotesRichTextItem (RecordDoc, "Body")  
' append attachment to rtitem 
Call rtitem.EmbedObject(EMBED_ATTACHMENT, "", FilePath)

This assumes that you are creating new documents and not updating existing ones.

If you are updating existing documents, you will rather use this kind of script:

Dim rtitem As NotesRichTextItem, FilePath As string  
FilePath = RecordDoc.GetitemValue("FilePath")(0)   
If DestDoc is Nothing then ' create a new rich text item  
Set rtitem = New NotesRichTextItem (RecordDoc, "Body")  
else 
' remove any existing Body item 
call DestDoc.RemoveItem ("Body") 
call DestDoc.RemoveItem ("$File") ' remove also attachments  
' create a new rich text item in the destination doc 
Set rtitem = New NotesRichTextItem (DestDoc, "Body")  
end if  
' append attachment to rtitem 
Call rtitem.EmbedObject(EMBED_ATTACHMENT, "", FilePath)

In that script, if we are updating an existing document, we first remove any exiting 'Body' item. 
WARNING: If we want to remove the previous attachment, we have to remove all attachments (the $FILE items). 

This script could be improved by checking if the file should be re-attached in the destination document. The checks could be done on the file name or on a revision field.

Q: How is it possible to access database on our AS/400 via odbc? 

A: You need to have the ODBC driver installed and running on the AS/400 (this is an option you have to pay for).

As an alternative, you can use the Text Export command (CPYTOIMPF) wich can be scheduled on the AS/400. 
Another tip with this command is that if the destination file does not exists, it is created with EBCDIC charset but if the file already exists in ASCII, it it kept with this charset. So, if you want to create file in ASCII, you should have a void ASCII file which will be used as a template. You issue a copy command to create the destination file from this template. You use the 'Append' mode of CPYTOIMPF to fill the file.
This command has been successfully used with A.N.D.I. customers for more than 5 years

Q: How to delete unmatched documents? 

A: . The Import/delete activity can be used to delete Notes documents selectively. This is really like an Import/update except that the documents are deleted. You have to provide a key list of documents to delete like you would do for updates.
But this involves that you already have a list of the documents you want to delete.

In some circumtances, you may want to delete unmatched documents: documents that you don't want anymore.

There obviouly could not be an 'unmatched document delete' directly. 

With ANDI, you have 2 approaches: one is to get a list of item to delete ; the other is to traverse a list of all items and delete only those that meet some conditions (in fact we will use a Reject condition).

See Andi Help - Appendix B, for detailed solutions.

Q: XML import - how to make a multivalue field from multi nodes? 

A: The main idea is to transform the node content into a string that can be converted with an @Explode formula that you will use in an 'Extra Field' formula with the same field name to recompute the values.

Example:

xml data:

<?xml version="1.0" encoding="UTF-8"?>
<stock>
    <partNumber id ="1234">
        <part serial = "1615"/>
        <part serial = "3132"/>
    </partNumber>

    <partNumber id = "2345">
        <part serial = "61512"/>
        <part serial = "13223"/>
    </partNumber>

</stock>

xsl stylesheet:
<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output indent="yes"/>

<xsl:template match="/">
<xsl:apply-templates select="stock"/>
</xsl:template>

<xsl:template match="stock">
<xsl:element name="stock"><xsl:apply-templates select="partNumber"/></xsl:element>
</xsl:template>

<xsl:template match="partNumber">
<xsl:element name="part">
<xsl:element name="partNumber"><xsl:value-of select="@id"/></xsl:element>
<xsl:element name="partSerialList"><xsl:apply-templates select="part"/></xsl:element>
</xsl:element>
</xsl:template>
 
<xsl:template match="part">
<xsl:text>;</xsl:text><xsl:value-of select="@serial"/>
</xsl:template>

</xsl:stylesheet>

transformation result:

<?xml version="1.0" encoding="UTF-8"?>
<stock>
<part>
<partNumber>1234</partNumber>
<partSerialList>;1615;3132</partSerialList>
</part>
<part>
<partNumber>2345</partNumber>
<partSerialList>;61512;13223</partSerialList>
</part>
</stock>

The @Formula for the 'partSerialList' field should look like this:

@Trim(@Explode (partSerialList ; ";"))

Q: What is a 'No-End loop' error?

A: When exporting documents by traversing a view or collection, Andi protects itself against no-end loop by checking if the next document is different that the current one.

No-end loops could happen when a view index is corrupted. Andi includes this protection to avoid endless process.

The counter part of this is that if a view index is build on a multi-valued field (such a the ($Users) view in names.nsf) Andi could detect a 'No-End loop' because the document does not change between two entries.
Such views should not be used with Andi.

The ($Users) view is basically a system view used by Domino for identifying logins.
You should rather use the ($VIMPeople) view instead or use a @Formula like {Form="Person"}.

Q: How to use FTP with A.N.D.I.?

A: Andi activities can be triggered by a java agent (Andi release 4.1). This java agent can include instructions for connecting to an ftp server.

A sample agent is provided here. It includes the FTPConnection class (Authors: Bret Taylor & Julian Robichaux).