The Ledger : query

An Easy Way to Open a Group of Records

Karen Badham — Fri, 28 Aug 2009 17:51:00 GMT

Have you ever had a situation where you need to edit a whole list of records? There are many ways you can do this, from editing each record individually, to global change, to import. But what if what you are trying to accomplish can't be done with global change or import and the task seems too daunting to hunt and peck for each record? Don't despair; there is a tool you can use. I call it Open Query as a List.

Create a query with the proper criteria that fits the list of records you want to edit. On the Results tab double click the top row to open the first record. Once the record is open, make the necessary edits and click Save and Continue. Then use the blue arrows at the top to scroll through each record in the query. This is much easier than searching for each record!

Take a look at the solution, How to scroll through records, for step-by-step instructions.

Five Step Method for Creating the Perfect Query

Kellie Wingfield — Thu, 23 Jul 2009 14:01:00 GMT

Ever wonder, "How am I going to get a list of this information?" Oftentimes, you need to pull data from our system, but how can you? These are the times to look to a query. What is a query? Queries retrieve raw data and present it in a list format. It is a grouping tool, not a reporting tool. A query can also be used in dashboards, exports, searches, global changes; basically in every module of The Financial Edge. I have a five step method for creating the perfect query. These questions help resolve most issues surrounding queries. So what are these magical steps? I am glad you asked!

What kind of records do I need to group together?
This will determine the query TYPE. For example, you can choose Vendor, Invoice, Transaction, or Employee.
Do I want the list to be updated?
This is the format selection between STATIC and DYNAMIC. More information can be found in the Static vs. Dynamic post.
What do my desired records have in common?
This selection is made on the Criteria Tab. Are they Vendors that are all marked 1099? Are they transactions entered on the same date?
What do I want to know about the results?
The answers can be used to determine the fields on the Output Tab. Do I need to see the Vendor name or ID? Do I need to only see the post date or the transaction date?
What order do I need to see the result (or what order do the columns need to be in?)
The answers can be used to determine the fields on the Sort Tab. Do I need Employee Name before Hire date?

Use these five steps as a guide to make the perfect query every time!

Static vs. Dynamic: What’s the Difference?

Kellie Wingfield — Mon, 22 Jun 2009 12:16:00 GMT

One of the most common query questions we get has to do with the static and dynamic format. Which should you choose? Let me tell you a story that will explain the difference.

You have two file clerks in the office: Static and Dynamic. You ask them both to find accounts that use account code 5555. Static is the more meticulous of the two, bringing with her a handful of paperclips. She locates the accounts and places a paperclip on each of them. Dynamic just likes the adventure of the task and dives right in. He does not mark each account, but locates them just the same. Both return the same list of accounts. You thank the clerks and they return the files to the file cabinets.

A month goes by and you've added a few more accounts with account code 5555. You ask your willing file clerks to again locate all the accounts with this account code. Static remembers the accounts she pulled earlier and only retrieves the accounts with the paperclips. Dynamic dives right in as usual, but he notices there are more accounts with the account code 5555 than before. This time you get different lists. Dynamic gives you a list of all accounts where Static gives you the same list as before.

The reason? Dynamic will refresh his list with each request so it always includes up-to-date information. On the other hand, Static is more like a snapshot she will always give you the original list. Dynamic queries are the most commonly used type. I would recommend a static query if you always want to capture the same set of accounts, employees, transactions, or vendors.

Cleaning House? Delete Unused Queries

Jennifer Maroney — Mon, 08 Jun 2009 19:07:00 GMT

As the years go by, the number of saved queries can grow very quickly. The more queries you have the more difficult it is to find the one you need. So what should you do? Well, you have a few options: organize your queries, add links to them on your Home page, delete the queries you no longer use, or all three! Since we've covered organizing your queries and adding links to your Homepage in previous posts, I will focus on deleting queries.

When it comes to deleting queries there are two options: delete them one at a time or multiple queries at once. To delete one query at a time, open the query and select File, Delete from the menu bar.

If you have several queries to delete, I recommend deleting multiple queries at the same time. The process is just as easy as deleting them one at a time and saves you from having to open each query. Simply select Tools, Delete Multiple Queries from the menu bar in Query. You have the option to filter by query type and format. To only display your queries, mark the Only show my queries checkbox. Mark the Delete? checkbox for each query to delete from your database and click Delete Now. Please keep in mind that you cannot recover deleted records, so make sure you mark the correct ones .

Have fun cleanin' up. Talk to you soon!

Meet Ms. Queue

Jennifer Maroney — Fri, 29 May 2009 12:30:00 GMT

Do you run the same reports and queries everyday or every week? Would you like to hire an assistant to just run these reports so you can focus on more important things? FE has your assistant waiting. Please meet Ms. Accounting Queue:

Ms. Queue is proficient in running reports, queries, exports, and posts automatically. The best part is, she works at any hour, does not require vacation, breaks, and bonuses, and no supervision is required. I recommend using Ms. Queue during non-working hours so you can be more productive by utilizing all workstations and printers during working hours.

For more information, check out the Accounting Queue Guide. If you would like to hire Ms. Accounting Queue to work for you, contact your account manager.

Talk to you soon!

Advanced Query & Export or.....State Tax Reports for 1099 Vendors.....

Daniel Dalnekoff — Thu, 02 Apr 2009 18:38:00 GMT

Was I able to get your attention from this title? Hopefully so...

At the 2008 Conference, I taught an FE Query & Export session. Most of the attendees really enjoyed some, or maybe a little, of the content so I want to build on it here. Recently a client needed to file a state form each time a vendor's total 1099 payments for the year exceeded a certain dollar amount. So what we need to do is find who has gone over the limit in the current tax year and have a record of the form being sent in or not.

Let's pretend it's January 1st and the state needs to be notified when the payments exceed $500.00. Begin by adding a Vendor attribute such as State 1099 submitted and make it a yes/no field. Globally add this to all vendors with a value of "No", indicating that nobody has had their form submitted.

Build a vendor query. On the Criteria tab, add 1099 activity greater than or equal to 500 and set the date filter to this calendar year AND add Vendor Attribute State 1099 submitted equal to No. On the Output tab, add some output fields for your reference and to spot check the results. Now, decide how often to run the query, for example monthly or every Friday afternoon.

There are two options from here:

Run the query, and print the results. Use the list to fill out your required forms. Then use global change to change the records in the query to change the attribute to Yes.

Open Mail, Labels, Cards, and Envelopes, and create mailing labels. On the Filters tab use the query to filter the vendors to only list the vendors who need the form submitted. On the Processing tab, select to change the State 1099 submitted attribute for all records included from No to Yes. Print the labels. Now you have your vendors listed to create the forms for and a mailing label to send a nice letter to let the vendor know the form was submitted.

So, there you have it. Now, I challenge you to submit how you use Query & Export or, even better, how you would like to use these tools.

Conference '08 session on Query and Export

Daniel Dalnekoff — Tue, 23 Sep 2008 17:07:00 GMT

Hello loyal readers,

For those of you attending Blackbaud's upcoming Conference for Nonprofits this November, this is specifically for you. While everyone here is enjoying the November weather, I will be presenting the session (as the title of my post suggests) "The Power of Querying and Exporting in The Financial Edge". I am excited about presenting this, and I want to make sure anyone attending gets as much out of this as possible. In order to do so, I would like to get YOUR feedback on what you would like to see covered in this session.

I cannot promise to cover anything or everything that you suggest, but I will try to cover what you are interested in the most. What I had in mind was a few query basics, as well as some useful queries for General Ledger and Accounts Payable. I also wanted to review how a query can be a great tool for use as a report filter. Export is also something I plan to review, but generally the basics along the lines of setting up and applying some filters to an export.

Please feel free to let me know if there is something else you would like to see covered, or if this is just perfect, or if you see something here you are not interested in at all....

Daniel

Seeing Double in a Query?

Jennifer Maroney — Fri, 15 Aug 2008 16:02:00 GMT

Duplicate query results is extremely frustrating, but are they really duplicates ? When a record appears more than once, it could be that the record meets the query criteria multiple times or the output includes one or more one-to-many fields. The good new is, when you use the query as a filter in reports, export, or wherever, each record is included only once.

To minimize records appearing multiple times in a query:

Limit the number of output fields, especially one-to-many fields such as phone number.
Increase the number of filters to limit the results. For example, when filtering on phone numbers also filter on the phone type.
Suppress duplicate rows:
1. Select Tools, Query Options from the menu bar.
2. Select the Record Processing tab.
3. Mark the Suppress Duplicate Rows checkbox.
  Note: This checkbox prevents exact duplicates. If the query output includes fields with one-to-many relationships multiple rows may still appear.

If you are creating the query for a list of records, in other words it's not for a filter; you can export to Excel and hide or delete the duplicates.

Organize Your Queries

Jennifer Maroney — Wed, 16 Jul 2008 13:14:00 GMT

Trying to locate the one query amongst dozens or hundreds can feel like finding a needle in a hay stack. If you hate searching through all of your queries, the answer is in the upcoming release of The Financial Edge version 7.77, due out in Quarter 4.

After updating, you can create categories, or directories, to organize and group your queries. The categories can be by user or query purpose, such as reconciliation. Instead of scrolling through the entire list of queries, you'll be able to open the category folder and find it in a snap.

To create and organize query categories, select Organize Categories from the menu in Query. On the Organize Categories screen, you can create, rename, and delete query categories. You can also adjust the order in which the categories appear on the Query page.

Stay on the look out for more 7.77 new features. Talk to you soon!