InfoPath Dev

Hi:

I have a number of dropdown controls pulling their lists from a SQL database. I have created separate form data connections for each dropdown so that I can add an order by clause and have my list sorted in ascending order. This works very well for my main table. I am unable to get the order by clause to work for my child tables, however. Here is an example of the edited SQL statement for a field from the main table. This successfully orders the data:
select "Id","SorterFamily","ScanRate" from "dbo"."SystemConfigs" as "SystemConfigs" order by "ScanRate"

Here is an example of the edited SQL statement for a field from a child table. This does not sort the data by the order by clause, instead, it sorts it by "SystemID":
shape
{select "Id", "SorterFamily" from "dbo"."SystemConfigs" as "SystemConfigs"} as "SystemConfigs"
append
({select "ID","LightingSystems","SystemID" from "dbo"."LightingSystems" as "LightingSystems" order by "LightingSystems"}
relate "Id" TO "SystemID") as "LightingSystems"

In both cases, the only thing I have added is the order by clause. The rest is the SQL generated by the data connections wizard. Anyone have any ideas of how I can sort a data field from a child table?

Thanks so much!


Hilary

I'm not great at SQL and that looks like a rather complex statement, but try moving the order by clause outside of the brackets and putting it at the end of the line.

If that doesn't work, leave the first one where it is and add an additional order by clause at the end.

Hope this helps, just an educated guess on my part.

---------------
Matt Faus
InfoPath Dev Texas

Thanks for the suggestions. I tried this:
shape
{select "Id", "SorterFamily" from "dbo"."SystemConfigs" as "SystemConfigs"} as "SystemConfigs"
append
({select "ID","LightingSystems","SystemID" from "dbo"."LightingSystems" as "LightingSystems"} order by "LightingSystems"
relate "Id" TO "SystemID") as "LightingSystems"
And I got an error. I also tried this:
shape
{select "Id", "SorterFamily" from "dbo"."SystemConfigs" as "SystemConfigs"} as "SystemConfigs"
append
({select "ID","LightingSystems","SystemID" from "dbo"."LightingSystems" as "LightingSystems" order by "LightingSystems"}
relate "Id" TO "SystemID") as "LightingSystems" order by "LightingSystems"
And got an error.
I even tried a different data connection, connecting to the child table first and then relating the parent and adding the order by clause. My list items continued to be ordered by system ID. It is as if the relationship overrides the order by clause.


Hilary

Okay--I made a very silly error--I made changes to one view and was previewing a different one! in fact, the different data connection, where I connected to the child table first and then related the parent table works--my dropdown items are now pulling in ascending order! Hooray!

Hilary