LIST_TABLE - How to sort

bpolak

Is there a way to sort a LIST_TABLE? The table prints out like I want but I need it sorted by a particular field.

Can I insert an ORDER BY somewhere? Or is there another way to do this?

Thanks,
Brian

tford

From the User Guide:

A list may be specified as an attribute or as a query string.

Have you tried a query string?

bpolak

Thanks Tom.

I had something in the syntax messed up. Here's what I ended up using in case anyone else needs help:

<<LIST_TABLE('FIND Item WHERE (Item IN Order.Items) ORDER BY Item.SortNumber','Name','Description')>>

Worked great and sorted my list perfectly!

Thanks!

Bryan

How would the query string look if say I was trying to evaluate a list of values. I cannot seem to get this to work.

<<LIST_TABLE('FIND Item WHERE (Item IN Order.Items) AND (Item.Status IN 'Opened', 'Pending', 'Active') ORDER BY Item.SortNumber','Name','Description')>>

It seems that the additional ' in the query string is causing an issue for the import of the document.

Alternatively is there a way to specify the usage of an existing query if so I could use it. Unfortunately my attempts at using a query have yet to return results.

Bryan

-bump-

Does anyone have a clue how to complete this?

bpolak

Bryan,

Sorry for the delayed response. What does that LIST_TABLE function return? Is it just blank or does it return an error of some kind?

Bryan

I have not gotten far enough to tell see any results on a query with this type of evaluation in it. I have found that if I specify a query that does not have text options such as 'Yes' or 'No' in the query it works with out issue.

It is on the import of that the issue occurs. If the tag embedded query contains the about type of evaluation it will not allow you to save the document.

tford

<<LIST_TABLE('FIND Item WHERE (Item IN Order.Items) AND (Item.Status IN 'Opened', 'Pending', 'Active') ORDER BY Item.SortNumber','Name','Description')>>

It seems that the additional ' in the query string is causing an issue for the import of the document.

Alternatively is there a way to specify the usage of an existing query if so I could use it. Unfortunately my attempts at using a query have yet to return results.

You can:
1) Make a query, let's call it FindItems:

FIND Item WHERE (Item IN Order.Items) AND (Item.Status IN 'Opened', 'Pending', 'Active') ORDER BY Item.SortNumber

2) Put FindItems query in the LIST_TABLE:

<<LIST_TABLE('FindItems','Name','Description')>>

Bryan

I will give that a try. I tried using a named query first and was not able to return results.

tford

I usually use a "mini-BSV" to try out a concept .... it minimizes the possible issues.

In this case, you can create a Auery & Word doc that uses RegularUser as the test BO.

Bryan

Alright thanks to everyone's help I was able to get a query that currently existed in the system to export to csv. There are however issues.

Major issue:
The big issue I am having is when a value is empty. If for example a shortcut value does not have any data. Exporting the data in this manor does not account for this issue and causes the data to be moved to the left one column in the csv when the situation occurs. As you can see this is making my export unreliable.

Minor issue:
One of the attributes is a number formatted as such #,##0 which as you can guess causes issues in a csv file because it has a comma in the value. There is obviously the hard way to resolve this by setting another attribute up to hold the value in a different format but that seems like a waste when the data is already there. Any tips on a better way to handle this issue.

Bryan

I have come the conclusion that is nearly impossible without numerous rules and processes and even then you are not guaranteed constistancy to get a query to export as it does with the 'Export query results to a CSV file'. The reason is that when you use the LIST option to export a csv file from a query the results of the csv file are off or missing columns if values have not been assigned to the attribute yet. This in turn makes your csv file output less than useful because the data is in the wrong columns.

Support,
Would it be possible to get the 'Export query results to a CSV file' functionality exposed to be consumed via a process or rule through the configuration tool? Something like EXPORT QUERY that would allow you to specify the query name similar to a current export document function.

RLJB

Bryan - Did you find a better/good way to do this? I would also find this very useful.

I have been using the generate the document AS XLS option but it is quite time consuming since you need to build a report and in XLS you must ensure all your fields are perfectly lined up or else Excel will insert multiple rows/columns with merged cells.

A rule that could 'Export query results to a CSV file' would be very useful.

Bryan

Unfortunately we have yet to come up with a graceful solution to this issue. Although I have not looked at this in a while.

As a fill in we are generating reports using the report builder.