Unsubscribe   Open in Browser  

Vineyardsoft Corporation   800.850.8055   info@vineyardsoft.com    www.vineyardsoft.com 

KnowledgeSync Educational Bulletin
Leading the Way in Business Activity Monitoring

The "Unique" Column of a Query

Welcome to the KnowledgeSync Educational Bulletin for Business Partners and clients who are using KnowledgeSync.

This week's question:

We are trying to create an event that sends an Order Confirmation to a client when they place an order. The KnowledgeSync manual says that only one column in a query can be unique, so we chose the "order number" field as our unique. But when the event runs, the order confirmation email shows only the first line item of each order - what are we doing wrong?


The first part of the answer is that the "order number" field is not unique for every line item - in fact, every line item in a given order has the same order number. So, once KnowledgeSync processes the first line item of an order, it remembers that line's unique value (the order number) and then - when it sees the other line items for that order (with the same order number), it skips over them, thinking that it has already processed those records.

Although the order number is not unique for each line item, the combination of the order number AND the line number is unique.

But hold on there . . . didn't we say that "only one column in a query can be unique"?

Well yes - but you can combine the values from multiple fields (such as from the order number and line item fields) into a single column and then make that column the unique for the query.

And that is the answer. Although a query can have only a single unique column, there is nothing to prevent you from concatenating the values from multiple database fields into a single query column. Just remember this - you can concatenate fields only if their format is "character" (i.e., you cannot concatenate numeric fields), so if it turns out that you do need to concatenate a non-character field, be sure to convert that field into a character format before doing so.

(And the KnowledgeSync "Calculated Column Editor" shows how to do this, so you don't have to know any programming or SQL syntax to do so.)

If you have any questions on the use of the "unique" field within a query, please contact Don Farber at farber@vineyardsoft.com.

Vineyardsoft Corporation

Video     Data Sheet     Documentation     Download Software    

Educational Bulletins     Become a Business Partner