Again, I have to apologize for the rather long break between posts. The Minnesota National Guard and I have been seeing quite a lot of each other over the past few months. Reminds of a post I wrote over a year ago called “Supportive Commitment“, what was written in that post is still true today. This is a great company to work for and they are very supportive of the efforts of U.S. Military Forces.
Today I want to look at creating a Formula field in Stonefield Query for Sage SalesLogix. In the Report wizard you find the controls that allow you to create a formula field in Step 2 – Data Selection. I added the Account Name to the list of fields selected for this report and then changed the table to the Address.
The formula that I am going to write for me demonstration will have address 1 display on the first line and the have the City/State/Zip field display on the second line all with in same formula field. Under the list of available fields along the left, you will see the “Formula…” button . Selecting this opens the “Formula Editor” below.
I added the name for my formula as “Custom Address”. When I left the “Name” field the “Heading” auto populated, the value was still editable so I put a space between the two words. I then selected the button to the left of the formula space which brought up the “Expression Builder” dialog.
To build my formula I selected the my two fields from the list of available fields for this table. I then added some VB scripting that inserted a carriage return so the City/State/Zip field would list on teh second line. I was able to test my syntax on this form which helped alot. When I was satisfied with my formula I selected the “OK” button which returned me to the “Formula Editor” dialog. Here I selected the “OK” button which then returned me to the “Report Wizard”. The list of available fields now contained my CustomAddress formula field, I moved the field into the list of Selected fields for this report. I selected Next and moved to Step three which allows me to establish filters for this report.
As you can see I only want Accounts that start with the letter “A”. I then previewed my report to see the results, here are the first few records.
The one thing I played with is the filtering. I decided I might want to write the report using other letters so I changed the filter to ask me what letter I wanted to filter by at run time.
When I previewed the report I was provided the following dialog so I could select the letter I wanted to use.
Pretty quick and easy, real good stuff.
I am going to try and look at graphs in this environment and then we will probably wrap up this series of post. Until then do good things and keep smiling.
