Replacement for Stonefield Query

[fritzys1]

The team at Stonefield Query just informed me that they are not going to pursue a solution to the bug that I keep running into.   I have been using SQ for building mailing lists that depend on 2 time based interactions with the client along with a number of standard filter types.  To be more specific, I look for all the clients that have used my services over the last 18 months, but haven't used my services over the last x number of months depending on the type of services they use.  I run serveral additional filters from there from within ACT and then drop the whole thing into Excel to deliver to my fulfillment house.  Any ideas on a replacement for SQ?  BTW, the bug is that it chokes on writing the group back into ACT.  It completes the filtering, displays a window stating the number of contacts found and that it has written the group, but the group doesn't get written in ACT.  It only fails 30% of the time, but each run is 2+ hours long.  Any help would be appreciated.

[DougHennig]

Unfortunately, this isn't a Stonefield Query issue but an Act issue. The Act SDK, under circumstances that neither we nor Sage can reproduce, occasionally fails to write contacts to a group as it's supposed to. Since it's internal to Act, other applications will have the same issue.

[fritzys1]

Thanks for the input.  The question is, however, since I can't rely on the SQ/ACT to acheive a solution, does anyone know of a product or approach that will?

[Roy_Laudenslager]

Have you tried using the advanced query in ACT! to create the lookup?

[fritzys1]

Ya, thanks for asking, but as far as I'm aware, you can't run 2 time domain filters in ACT.  The only time domain filter that I'm aware of is contact activity.  Let me know if I'm wrong.  I'd rather be wrong and have an answer...

[Roy_Laudenslager]

I went back over the thread and I have a couple of suggestion. You're right that the contact activity lookup works on the whole database and it's a single time parameter. There are a couple of workarounds that I can think of. Instead of have SQ write to a group, could you have the output be a write of a code like a simple X to an empty field in the database? If so you could then do a lookup on that field for your export. The other workaround would be similar except you would do one contact activity and then place a code in one field for the lookup and then do the second contact activity lookup and then place a code in a second field so that you could use and advanced query to combine the two lookups.

[fritzys1]

Great suggestions!  I'm doing quite a bit of that now to execute the entire sequence.  I'll have to scratch my head to see if there is a combination that would eliminate the group functions.  Based on your inputs, I assume that you are not aware of anything similar to SQ out there.

[GLComputing]

If you can update a Contact field from SQ, you could then use ACT!'s Group Membership Rules to add the contacts to the group

If using ACT! 2010, you could use Excel alone via the OLEDB2 interface to get the data. You wouldn't write back to ACT!, but could do the report you need to send directly. See http://blog.glcomputing.com.au/2009/07/connecting-to-act-by-sage-data.html

The OLEDB option prior to ACT! 2010 didn't give access to activities, so if you haven't upgraded, you could try Crystal Clear Essentials from http://www.adsprogramming.com/products.htm - it also doesn't write back to ACT!, but could do the report you need to send directly.

Wondering... if the query fails and you run it again, does it still fail?

As a final option, if you want to send the report with a method above and still note the action in ACT!, you could try bringing the Excel data back (to contacts or groups) with something like itImport

[fritzys1]

Great inputs!  I'll have to research the details, but to let you know, repeated attempts do not appear to work without other changes.  My process to achieve a solution and further diagnose the problem, includes various combinations of application restarts and system reboots.  More specifically, SQ is run from a file server running XP Pro, the ACT server is running Vista Home Premium; I attempt to run SQ on the ACT server versus it's normal location; I attempt to run SQ without the client running on the XP box, I restart SQ, I restart the ACT server, etc. always logging the actions to see if I can find the event that reconnects the function.  I have identified that the function can write the group from one computer while simultaneously having a failure on another, but have not found a magic combination that reconnects the function.  Also to note, rebooting the ACT server has never solved the problem.  Thanks for any thoughts!

[GLComputing]

It sounds possibly like a resource issue...

Might be interesting to look for any messages in the windows Event Viewer on either machine when it fails... or,  as it fails with repeated attempts when it does fail, next time run it again and watch the resources with Task Manager or Microsoft's Sysinternals Process Monitor - http://technet.microsoft.com/en-us/sysinternals/bb795533.aspx

If you try these and can see something obvious, please post back... if Sage can't replicate it, it's not easy to fix. But, with the right info, they might figure out the cause.