Data Importing

Just been working for a client on a data import. The import had to read a flat file but produce a primary and several secondary records depending on the data involved.

I toyed with an agent firstly, but this is really restrictive when it comes to adding secondary data, without having the initial load of the data into a temporary table, and then having the agent go through the temporary table and complete the secondary tasks.

My solution was to use a pivotal agent to call a method on a c# appserver rule. The main dll is not transaction dependent. The code reads in the file into a data table (thanks to Andreas Knab's blog, this is childs play) then for each row gets together the information for a new or update to the main table. The code then calls another ASR which is within a transaction and returns the new or updated record id, which is then used to create secondary records using the same principle. Errors are collated in an arraylist.

At the end, the errors arraylist is written to a file to check the import, and we are done.

What impresses me is the speed of adding / processing records within c# compared to similar code within an agent. The c# code takes minutes to update / create 1000+ records. An agent would take considerably longer on the same specification machine.

One thing I have learnt is the use of transactions. I originally started with one appserver, which would fall over when the number of records added reached 200+. This was due to the transaction not being able to be expanded, and hence at this point all the modifications were rolled back. By creating a wrapper around the transactional appserver, the transaction is only around one update / insert. If the code does fall over, the records before are all saved and you don't have to start again.

My offline task now is to create a more generic update. Use a config file to determine which file to import, what the columns map to as well as any linking required / conditions. This seems simple enough, but I think it will be down to the syntax and flexibility of the import configuration whether it is a real benefit.

Search Result Lists

One of the great things that was introduced with 5.9 was the ability to use Active Search Results Lists on a form.

This feature really expands the usability of a form to more than one level, allowing you to display, for example, Contacts associated with the parent and child of the record you are on, support incidents on subsidaries and anything else you can create a query on.

I have recently used this to replace the Show Emails / Show LEs on the company / contact forms to 2 SRLs under the activities listing. This was a useful addition for a client.

Sharepoint & Pivotal

Having run out of patience with users filling up the database with documents, a client has requested to link Pivotal with Sharepoint.

This became my first delve into webservices. The project involved creation of a sharepoint folder and subfolders whenever a company was created and displaying the result in a websegment. The issues I faced where mainly not understanding the webservice requirements, but a few days of playing with them got it working.

I then had to do a mass production of folders for the existing companies, by calling the .NET appserver rule via an agent, and seems to work reasonably well.

Things that I still need to include are code when the company changes name (creating a new folder, moving contents of old folder, then deleting the old folder) and security. It may be necessary to prevent certain users adding additional folders and seeing the contents of the folders I have created, but that is for another day.

First Post

My first post.

Thought I would share a few things about myself.

I am a Pivotal Consultant / Developer who has worked mainly with Rich Client since Active Access 3.1. Have a good understanding of all client / server development, including integrations and other things.

Worked originally on the client side, as a developer / system admin, but moved to a consultancy, 4 years ago now, have just moved again to another consultancy.