GoldMine Filter (dBase Expression and SQL Query)


In GoldMine, I have been able to create a SQL Query WHERE Statement
(AccountNo IN (SELECT AccountNo FROM ContHist WHERE Ref Like "%Letter%")

I am looking for a Valid GoldMine dBase Expression to go with the SQL WHERE Statement, so that I may create a Filter in GoldMine, that will find all Contact Records that have the Word "LETTER" in the ContHist->Ref Field.

Thank you in advance for your Help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harisha M GEngineerCommented:
Hi sqldata,
    I am not sure what you are asking...

    SELECT * FROM ContHist WHERE Ref LIKE '%Letter%' ;

Harisha M GEngineerCommented:
    Sorry that should have been "
sqldataAuthor Commented:

To create a GoldMine Filter, requires a dbase Expression and a SQL WHERE Statement.
What is the dbase Expression for GoldMine, that will work in conjunction with the SQL WHERE Statement?

Without the dbase Expression version of (AccountNo IN (SELECT AccountNo FROM ContHist WHERE Ref Like "%Letter%"), the GoldMine Filter will not work.

Please Help, Thank You...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

I don't understand how the SQL and the dBase work together in your environment.

But, a similar dBASE expression could be:

AT("Letter",ContHist->Ref Field)#0


"Letter" $ ContHist->Ref Field

In dBASE they could be used as follows:

LIST OFF FOR AT("Letter",ContHist->Ref Field)#0


LIST OFF FOR "Letter" $ ContHist->Ref Field

As far as how your interface with SQL has to go, I don't know.
A lot depends upon how you intend to use this expression within GoldMine. If you are looking for a filter, GoldMine autmatically assumes that you are looking at the CONTACT1 or CONTACT2 table, not the CONTHIST.

Within the Filters and Groups you have the SQL Query tab. This allow you to create a SQL expression to select data from any table in GoldMine. Here's the important part:

     The SQL Query will work on dbase tables!

But, you cannot create a "Filter" on any other table. If you want to select based on the CONTHIST table you have to use a SQL Query or define a Group. (Using the Group Builder you can select 'Completed history activities' and then select 'Letters' in the next screen.)

Creating filters from complex SQL Queries (that do not rely on only the Contact1 or Contact2 table) has long been a wish list item for GoldMine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrb1senior developerCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: Lprager {http:#15376501}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.

John Bush
EE Cleanup Volunteer
That's the fastest four days I've ever seen.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.