Query a specific Enterprise folder 1

[LV]

CE 10. Is there any way to query a specific folder in the InfoStore (using CE Query language) by folder's name, not ID? For example, there is a folder called "Client Reports", I need to list all reports in it. Thanks!

 

[mdwyer]

What you are asking, essentially, is if the CE query language will let you do a relational join. Not to my knowledge. You would execute one query to obtain the folder ID from the folder name and a follow-up query to obtain the (child) objects/reports from the (parent) folder ID.

 

[LV]

Thanks for the reply. I can live with two separate quieries, the problem is that i can't find a way to query the infostore to get a specific folder info (such as its ID) by folder name. The help file is not much of a help.

 

[mdwyer]

I'll have to cut it out on Monday if no one else gets to it sooner.

 

[synapsevampire]

Check out the tools from APOS:

http://www.apos.on.ca/sections/07-SoftSol-InfoSched.shtml

http://www.apos.on.ca/sections/07-SoftSol-InstMngr.shtml

And lots of other interesting products.

They have demo downloads, but the products aren't cheap.

-k

 

[mdwyer]

The first query, to retrieve the ID, is:

SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_NAME = 'Client Reports'

This will return a number, for example 344. The second query retrieves the objects in the folder:

SELECT SI_PROGID, SI_NAME, SI_ID FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = 344

SI_PROGID should return a value of CrystalEnterprise.Report. However, if the folder contains subfolders, the SI_PROGID will be CrystalEnterprise.Folder. (It could also be CrystalEnterprise.FavoritesFolder.) You can use these progid's in your WHERE clause, which also supports "IN" as in:

SELECT SI_ID FROM CI_INFOOBJECTS WHERE SI_PARENT_FOLDER = " & MyParent & " AND SI_PROGID In ('CrystalEnterprise.Folder', 'CrystalEnterprise.FavoritesFolder'

The InfoStore does return the query results into an object collection, for which you may need to use a loop. The object properties for name and id are Title and ID.

 

[LV]

Thanks mdwyer!