I never know the experience level of folks asking these types of question, so my response may be "too technical", but...
Whenever I cannot accomplish something like this directly within GoldMine, I jump to directly to SQL. I rather simple SQL trigger could be defined to effectively "watch" those 120 fields and automatically UPDATE the desired "sum" field any time one of those 120 fields changes.
So, my simple answer is: Use a SQL trigger.
Hope this feedback helps.
Instead of trigger You can also run sql job that will sum those fields every period of time that you wish.
The question is what is your goal.
Maybe there other ways to achieve your goal.
Thank you John and Shaul for your answer!
Quick question, I just watched a video in GoldMine Training Center that GM cannot perform SUM query without Microsoft SQL Server. Is this true?
I'm not understanding that statement. Unless your on an absolutely ancient version of GoldMine, that is dBase, then Microsoft SQL Server is the database back-end for your data. I'm not sure what is meant by "GM cannot perform SUM query without Microsoft SQL Server"?!?!
What version of GoldMine are we talking about here?
I don't know what you mean when you say "GM cannot perform SUM query without Microsoft SQL Server"
I am running on the GM Premium Edition, Version 2016.1.0.188
I am very new to Gold Mine and I watch the self training video:
It shows that the SUM query its using Microsoft SQL Server, therefore I just want to confirm that.
1 of 1 people found this helpful
All queries use Microsoft SQL server under the hood. The problem the speaker in the video is running into is that GoldMine quietly the ACCOUNTNO field to your query when it sends it to the server and, in some cases where you're using aggregate functions, group bys, etc. you will run into issues with having that extra field in the select list that make the query syntactically illegal. As the speaker found, you can, however, run the queries directly in SQL where no funny-business is happening with your queries...
Thank you Doug for the explanation.
I create the query to sum up the columns per row already.
By any chance, do you know how to integrate that query to the existing total field?
"By any chance, do you know how to integrate that query to the existing total field?"
This is where you would either need to go the SQL trigger route, as I previously suggested, OR the SQL job route, that @Shaul Bel previously suggested. Both would require use of a tool like SQL Server Management Studio,and most likely go beyond the scope of a simple Q&A like this forum.
Hope this feedback helps.
Be more specific in your question.
What fields do you want to sum and in what direction.
Do you want to sum them per contact or per all contacts There are all kinds of sums.
In each contact there is a custom tabs that calls "Expense".
Expense tabs consist of many field that represent client's expense.
Example of the fields: Food, car, pets, house, mortgage, etc.
I need to sum all these expenses field to see the total expenses.
I know that we can do it with dbase expression.
However, there are 100+ fields that need to be sum in 1 field (total expense).
I already have the formula to sum all of the fields, but it does not work when I try to copy paste it in dbase expression, I thinkbecause there is a limit character in dbase expression.
Yes, there's a limit.
You would do best to have an SQL trigger, upon the update of any of the fields in that field view, sum them all up and store the number in another contact2 field that you could then use like any other.
Thank you Doug for your answer!
I have quick question regards the SQL trigger in GoldMine.
What section in GoldMine Training Center or GoldMine Definitive Guide that coupe SQL trigger?
Is it in the Automated Processes?
Creating sql triggers is not within the scope of using or administering goldmine -- it is basically custom programming.