Deleting Fields and Custom tables in Act!

One of the great things that the Act! link for Sage 50 accounting (AL4A) gives the Act! user, is the ability to query Sage 50 fields created in AL4A including the building of dynamic GROUPS to report on or use in Marketing Automation.

The problem

A fundamental design aspect of Act! is that if you are using a field (created in Act!), in Groups in a dynamic query, you cannot then delete the Act! field or Custom table until you remove it from the query. The program within AL4A to remove the Sage data (for whatever reason), will fail to remove the fields or custom tables.

If you have an AL4A field in the Contact or Company entities that you have used, then it will fail to remove it. This is more straightforward to resolve, because you can remove it manually with the standard tools “Define Fields” function.

With the standard Act! Custom tables function, you will not be allowed to delete the fields or whole custom table if a field is used in the dynamic group query until you have removed it from the query.

If you use a field from AL4A custom tables (Invoices or SOP) and then remove the Sage data using the AL4A program, it will fail and the custom table will remain in “limbo” and cannot be removed by the program. You will need to follow the following process.

The solution

  1. Remove the field(s) from the queries BEORE you remove the Sage data. It will then run without issue.
  2. If you have run it and now have the issue, follow these steps.
    1. Remove the field(s) from the query.
    2. Close Act! and have all users logged off.
    3. Install MS Management Studio (requires system reboot).
    4. Log into Mgt Studio and navigate to the Act! database affected and go to the dbo.TBL_SYSENTITY table and delete the affected SL_ table(s).

Mgt Studio SysEntity table

    1. Reboot the computer
    2. Re-run the accounting link field creation process.