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

[Darryn Lavery]

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..

 

[S.Y.M. Wong-A-Ton]

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.

 

[RickH]

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"

 

[S.Y.M. Wong-A-Ton]

This might sound like a dump question, but I just wanted to make sure: Do you
have primary keys defined on the tables?

 

[RickH]

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