How do you make a custom database query?

[joshock]

I have a web app with a custom field for "Job Title." How do I create a page with all the "Regional Managers"?

I know it's possible with a web app search form. I don't want a form. I just want the page with the results.

I know it's possble using Site Settings "categories." I don't want that, I want to use custom fields.

I don't want to download the entire database and have jQuery sort it on the client machines. It could fail if the database has thousands of records.

[Liam Dilley]

You can insert the module for a specifc list item if you want or if you want a page for a specific Regional Manager you just use the detail page of that web app item. The url of which is right under the item name in the admin.

[BriceShatzer]

I've had pretty good luck with loading all the web app item entries into a container (I generally use a script tag with the type set as something innocuous like "text/data" or "text/string") and then parse the contents out as JSON and do whatever sort of filtering or sorting is needed, and then building the items on the page from that collection.

One of the issues you have to contend with using this approach is that a list of web app items is limited to 500 entries per page. If you're database is smaller than that, it's not an issue. Otherwise, you have to write something that looks for the pagination, and loads the additional data from those other pages.

This site Marriott Theatre relies almost exclusively on this technique for the majority of it's data. Take a look at the "Children's Theatre" & "Mainstage" pages as they are doing something similar to what I believe you are trying to accomplish.

[joshock]

Brice, thanks for your answer. That puts the burden of converting to JSON, filtering, and sorting on the client machine, right? I was kinda hoping for a server-side solution that wouldn't involve a lot of extra scripting 😞

[BriceShatzer]

That is correct, everything is happening client-side.

Simple sorts and filters generally aren't that taxing & anything that is more complex is easily handled by underscore or lo-dash. Completely anecdotal, but I've never really noticed any sort of slowdowns or hiccups with this sort of approach.

Adding BC's category tags to each item probably isn't feasible, but I think it's the closest you're going to be able to get when it comes to a truly server-side solution.

That being said, I think the web app search functionality might be something that is worth playing around with. Rather than having a full blown search form on your page, I believe you might be able to rig something up in JS that uses the web app search functionality to give you back what you're looking for in JSON and then inject that where you need it without a page reload. Maybe something in jQuery like:

$.ajax({
    url: "/Default.aspx?CCID=12345&FID=567890&PageID={module_oid}&JSON=1",
    data: { CAT_jobTitle: 'Regional Manager'}
})
.done( function( results ){  doThingsWithTheResults });

...not 100% sure on the specifics of getting that to work, but I think the general principle is sound. 

[Liam Dilley]

Apart from BC does not return Jason for that sort of thing.

BC only returns JSON data on a post request to BC web forms, not anything else at this time. It returns the HTML response and to fetch that you would either have the search results on a different page to ajax in OR if using the PageID with that module account for partial refreshing of a web page.

[joshock]

I am confused. Does BC return JSON formatted data from a POST request?

[Liam Dilley]

ONLY on custom web forms when you add the parameter, not on anything else at the moment.