General

Re: Edit SQL of Data Connection to Sort Dropdown

Anonymous — Wed, 04 Jan 2006 17:17:04 GMT

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

Re: Edit SQL of Data Connection to Sort Dropdown

Anonymous — Wed, 04 Jan 2006 16:26:05 GMT

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

Re: Edit SQL of Data Connection to Sort Dropdown

Matt Faus — Wed, 04 Jan 2006 01:36:46 GMT

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

Edit SQL of Data Connection to Sort Dropdown

Anonymous — Wed, 04 Jan 2006 00:32:44 GMT

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