InfoPath 2003, Shape, Order by, two tables, can't get sort to work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darryn Lavery - 15 Nov 2006 22:11 GMT

Re-posting from:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908652&SiteID=1

InfoPath 2003

I have an Infopath form to edit the values in the NotifyMembers table. I
want to list the email addresses alphabetically when the query returns data
to the form. Unfortunately, I use EmailAddressID as an int in the table, so I
need to join? the NotifyMember table with the EMailAddress table, which has a
column EMail which is the address. Both have EmailAddressID as common fields.

I can't get the Shape command (edit SQL in the Datasource) to be happy with
any syntax that I have tried.

For (non-working) example:

select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as
"NotifyMembers" Join "dbo"."NotifyMembers"."EmailAddressID" On
"dbo"."EmailAddress"."EmailAddressID" =
"dbo"."NotifyMembers"."EmailAddressID" order by "dbo"."EmailAddress"."Email"

Any help appreciated..

Reply to this Message

S.Y.M. Wong-A-Ton - 16 Nov 2006 05:23 GMT

Instead of typing in everything yourself, try using InfoPath's interface to
add the two tables when setting up your data connection, and then add a
relationship between the two tables. When you click the "Edit SQL" button,
you will see that the SHAPE command does not have any "join" clauses. You
need to use "append" to be able to use more than one table in a query and
then "relate" to relate the two tables to each other.

An easy way to sort is by selecting the first table you added and then
clicking on the "Modify Table" button to select the sort order.
---
S.Y.M. Wong-A-Ton

> Re-posting from:
>
[quoted text clipped - 21 lines]
>
>

Reply to this Message

RickH - 20 Nov 2006 07:30 GMT

I tried that first. I used Infopath to add the emailaddress table and linked
the two with the emailaddressid, and to sort on name.
However, I get no sort whatsoever, it displays as they were entered, when I
need to sort on the the Email address associated with the emailaddressid.
I tried several variations on this code, which gives me no sort at all on
Email:

shape
    {select "GroupCode","EmailAddressID","DelBit" from "dbo"."NotifyMembers" as
"NotifyMembers" order by "EmailAddressID"} as "NotifyMembers"
    append
        ({select
"EmailAddressID","FacilityID","FacilityCode","LocationID","VendorRefId","Name","Email","Outdated","DelBit"
from "dbo"."EmailAddress" as "EmailAddress" order by "Email"}
            relate "EmailAddressID" TO "EmailAddressID","DelBit" TO
"DelBit","EmailAddressID" TO "EmailAddressID") as "EmailAddress"

Reply to this Message

S.Y.M. Wong-A-Ton - 23 Nov 2006 09:14 GMT

This might sound like a dump question, but I just wanted to make sure: Do you
have primary keys defined on the tables?
---
S.Y.M. Wong-A-Ton

> I tried that first. I used Infopath to add the emailaddress table and linked
> the two with the emailaddressid, and to sort on name.
[quoted text clipped - 12 lines]
> relate "EmailAddressID" TO "EmailAddressID","DelBit" TO
> "DelBit","EmailAddressID" TO "EmailAddressID") as "EmailAddress"

Reply to this Message

RickH - 23 Nov 2006 16:57 GMT

Yes primary keys are there.
I wonder if this is a bug?

> This might sound like a dump question, but I just wanted to make sure: Do you
> have primary keys defined on the tables?
> ---
> S.Y.M. Wong-A-Ton

Reply to this Message