A customer calls me this week saying they need to create Sage ACT queries and search for up to 600 zipcodes per query. That means entering in the Zipcode query 600+ times. Wow, that will take a while. I told him to give me a couple of minutes to dream up something.
Here's what I came up with. I thought I'd share it with everyone in case you have to do this yourself. It is going to sound a bit complex, but trust me, it isn't really.
First off, you may not know this, but a lot of the files associated with Sage ACT are written in XML - which means "plain text." You can open these files using Notepad. Do I suggest you do this all the time. Well, heck no. Or if you do, you are of course making a copy of the file, right? Nod your head yes. Good.
Some of the files that are in XML include:
- Reports
- Layouts
- Dashboards
- Queries
These files are located in the Supplemental file folders for your database. The majority of time, these live on a server. If you are on a remote database, they live also on your workstation. If you click on Help - About ACT - and then on Database Information, you can find out there where the supplemental file folders live.
 |
 |
Once you have found this location, look for the Queries folder. That's where we will look for our sample file we create to kickstart our little project.
We will use my customer's request as our example. In his case, we needed to document a query for each user. This is a pretty common thing to map a territory for a sales person by zipcode or state. He sent me a spreadsheet with the zipcodes in one column and the username in another column.
| The first thing I did was get into the database and start an Advanced query. The reason I did this was to create our sample query file. I used Zip as the field and added two query entries. I made sure they were separated by the OR parameter. Next, I went to Windows Explorer to go find this file I just created. | Remember, I said these files are plain text. Using Notepad to open the file, I will search for and get a piece of the code out of the file that builds the Zip code statements. The image below shows two arrows. They mark the beginning and the end of the statement we need. We are going to copy the statement shown here. You will need to come back and use the beginning and ending parameters. Those stay the same no matter what criteria you enter. We will talk about that a bit later. |
 |
 |
Our mission is to build a replica of that statement 600+ times. Yikes. Hold on Nellie. I'm getting there. My favorite tool of all to build and concatenate statements is Excel. And guess what, I had that spreadsheet with all the Zip codes sent to me as my starter spreadsheet.
Opening the spreadsheet, I copied a range of zip codes for the first sales person and pasted them into a new worksheet in the workbook. On yet another new worksheet, I copied and pasted the XML statements as shown in the image above. The line we care about manipulating is the "<dataItem><![CDATA[37415]]></dataItem>" line. This is our model. In the spreadsheet, I changed this line to XXX. When I pasted in the text, Excel very nicely put each line into a separate row. Convenient of it do that. I pasted this all into Column A. Here's where it gets a little strange. I know I had 626 zip codes I needed to map. There are 17 lines in this XML statement. I had 626 zipcodes. 17 times 626 equals 10742. I scrolled down and highlighted all the XML code, then clicked on the little plus at the corner of the last cell and dragged down the plus sign to row 10742. That gave me 626 copies of the XML code. Ok, part one done.
In column B, at row 1, I started typing in a range of numbers in rows, starting with 1 to 5. I then highlighted the rows, and double clicked the plus in the lower right corner of the cell. This filled in a number series to the last line that contained text. This is an old trick I use all the time. This way I can keep rows of data arranged together correctly, even if I go and sort the data, which is exactly what I am going to do next.
I placed the cursor in A1, selected all data, clicked on Data Sort and Sorted by Column A. The purpose is to get all the similar lines lumped together. Remember I said I changed that dataitem line to XXX? I scrolled down the column till I found the first occurence of XXX. At that point, I changed that first XXX to =Sheet2(a1). In other words, I mapped that line to the first zipcode on the other sheet. I did the same thing in the row immediately below. Then I dragged down the plus sign in the lower right corner all the way down to the end of the XXX lines. Next, I highlighted all the newly created zipcodes, and selected Copy. Then, I selected Paste Special, and pasted back in Values. The reason I did this is we are going to now use the zipcodes to build a correct dataitem statement, and this wouldn't work until I had real data, and not a formula.
Scrolling back up to the first zip code, I moved over to column C, and entered the following formula: Pretend that A17 is the row number of the first zipcode. =concatenate("<dataItem><![CDATA[",A17,"]]></dataItem>").
Once again, I used the plus sign to build this formula down to the last zipcode. I then copied all the rows, and pasted special into the rows on Column A. I now had a set of 626 query statements with unique zipcodes. Heading back to the top, A1, again, I selected all data and now sorted by Column B in ascending order. This is how I get all the lines back in order correctly. Delete Column B and save the file as DOS Text. Opening the file with Notepad, I copied all the text in the file. I went back to my sample query file, and replaced the two criteria items in the file with what I had just copied from my text file. NOTE - the last criteria statement needs to say "<logicalOperator>End</logicalOperator>" instead of <logicalOperator>Or</logicalOperator>.
You are telling ACT that is the last statement. Save the file and run the query. This all sounds complex, but once you get the hang of it, it saves a ton of time. I built two of these in the time it took my husband to get a third of the way through one the hard way. Needless to say, when he saw what I had dreamed up, he brought up his file and we used my technique to finish his.
Sometimes thinking out of the box can save you mountains of time. Hope you find this helpful.