Javascript question

[Matt_Hirst]

I need to query a large database to return pricelist information, there could be upto three pricelists, each pricelist contains approx 1500 to 2000 items, some items are unique to each pricelist but majority are not.

When the upto three pricelists information is returned I need to:

* Populate a dropdown with a unique list of products from the three pricelists.
* When a product is selected from the dropdown I need to return from the pricelist related information such as price, this needs to come from the first list that holds the product e.g. product t could be on pricelist two and three but I just need to return the price from pricelist two.
* During the quotation process the pricelist order can change, so pricelist three can become 1 or 2.

My question is what is the best way to store this information to do the above or do I just run it as separate queries all the time, there could be upto 150 users simultaneously using this so performance is paramount.

Also any advice on the above would be appreciated.

Matt

 

[GravyMonster]

With up to 2000 items in a list, that's going to get unwieldy for the user very quickly. Is it possible you could allow people to search for the items they want instead of select them from a list? Pagination would also help a lot.

The javascript side of this is a doddle, I would say the issue is more a data modelling/retrieval one.

 

[kaku]

As above the javascript side of things is actually very small. It sounds like the most refinement will be needed with the database service, since that's where you're going to have the performance hit.

I'll be honest from your description I can't really picture clearly what the aim is. When you say only some of the items are unique - how many roughly? Even a hundred items in a dropdown list seems extremely messy.
How often do the price lists and products change? You could save queries by pre-calculating some things (list of products, which price lists they appear in, etc.) at set intervals.

 

[mid_gen]

Well, assuming the price list data is static, and at only a few thousand records, it's not big, then you obviously don't want to be pulling it from the database every time you get a user request, cache it on the server and run the queries against it in an in memory store. A simple node.js service would lend itself well to this in terms of performance.

From a UX point of view, you don't want a 1000+ drop down list, in fact I wouldn't use one with much more than 20....use some text input autocomplete client stuff to make the input cleaner.

 

[Matt_Hirst]

Hi,

Thanks for the responses, I am new to all this javascript and HTML so fighting my way through it so please forgive me if I ask stupid questions.

The dropdown needs to hold all available items (My task is to recreate an existing silverlight control but in HTML), the users would typically type the first few letters of the name of the product and select from the filtered drop down.

There may be around 2000 items in the final list though these will have three different prices so I agree it is a datastore / retrievel problem I have that I am trying to solve and I don't have a clue how to do this in Javascript.

My initial thoughts are to query the database on the server, put the results of the query into a object / array?

Price List Name
Product
Price

I would then filter and sort this object for a unique list of products to populate the drop down.

When an item is selected from the dropdown this object / array is queried on the client side and the relevant price pulled dependant upon the order of the pricelists (the order of the price lists can change).

How I do this or get started with this I don't have a clue, so any help would be appreciated.

regards,

Matt

 

[Matt_Hirst]

I have written an odata query to return the products from each pricelist and their related prices, etc.

The JSON results are, well lets just say beyond me, they seem to be table within a table for example I get the length of the results and it gives me 3 (when there are 3 pricelists), within each pricelist table is the products related to the pricelist.

My question is can I query these results to give me a unique list of all the products contained across the three pricelists or not? Also how can I reference a specific product s price is it like in an array or?

Here are the first couple of lines returned as results:

{
"d" : {
"results": [
{
"__metadata": {
"uri": "a_server/PriceLevelSet(guid'52569831-9d44-e211-80e9-02bf0a86f1e1')", "type": "Microsoft.Crm.Sdk.Data.Services.PriceLevel"
}, "StateCode": {
"__metadata": {
"type": "Microsoft.Crm.Sdk.Data.Services.OptionSetValue"
}, "Value": 0
}, "Name": "Distributor", "price_level_product_price_levels": {
"results": [
{
"__metadata": {
"uri": "a_server/ProductPriceLevelSet(guid'761a8edb-06e0-e211-97a3-02bf0a86f1e1')", "type": "Microsoft.Crm.Sdk.Data.Services.ProductPriceLevel"
}, "Amount": {
"__metadata": {
"type": "Microsoft.Crm.Sdk.Data.Services.Money"
}, "Value": "102.2600"
}
}, {

regards,

Matt

 

[iKettles]

It's impossible to tell what you can actually do with the data returned without at least seeing the full format of the JSON. You may find indexing the price list information as the ID of the product is easier so you can simply do productList[productId] to pull the related for that product.

You can use jQuery UI autocomplete for further filtering your data.

I think a better explanation of what you want will help us point you in the right direction

 

[Matt_Hirst]

I have now worked out how to get to the products contained within each pricelist:

var res = data.d.results;
alert(res[0].price_level_product_price_levels.results[0].UoMId.Name);

This returns the first products UoMId.Name for the first pricelist, to get to the second one I can change the results[0] to results[1].

So now the question is how can I query the inner results of the pricelists, I have found JSLinq, to return the unique Products from all three lists and populate a dropdown with this info?

Is this possible with JSLINQ.


regards

Matt

 

[visibleman]

Here are the first couple of lines returned as results:

Can you not filter the output in anyway (employ server-side scripting perhaps? Query the DB, filter and output JSON)? As there appears to be a huge amount of excess data for no reason and you don't want to be grabbing MB's of data every time.

...the users would typically type the first few letters of the name of the product and select from the filtered drop down.

An 'Autocomplete Dropdown' is what you're after and there's plenty of JQuery plugins around that'll do this.

 

[Matt_Hirst]

The data is filtered to the minimum by being down to products on a pricelist, majority of the time there will only be a max of two pricelists but sometimes we offer all of our products so require a catch all pricelist as the third.

Also the majority of items should be duplicated across the pricelists so in theory there will be around 1500 items in the drop down. I am limited to mimicking the exact functionality contained within an existing quote module hence the reason to include all this - I suppose I could include a prefiltered section / drop down, prior to the product drop down, which would be a parent product group but this could still give me upto 150 items in the product dropdown.

Thanks for the tip 'Autocomplete dropdown' will do a search.

I still need to work out how to query the JSON results using JSLINQ to return firstly the unique parent product groups and then the unique products - anyone got an idea on this?

regards,

Matt

 

[visibleman]

The data is filtered to the minimum..

The JSON data that your 'backend' (Microsoft Dynamics is it?*) is outputting is pretty large considering all you need is product name/ID and product price.

Plus it's a big ol' no-no to expose your 'backend'/CRM/DB client-side (ie - querying from Javascript), especially if it's going on a public site, which is why it might be better to have middle-ware that sits between your DB/CRM/'backend' and the website that handles (and filters/formats) the data (or JSON that JS/JQuery will use).

Tbh, it might be best to explain exactly what services you're using and what you exact aim is as i'm, no doubt like others, still none the wiser to what you're actually attempting to achieve.

* I could be wrong (never really had much hands-on experience with Dynamics) but i don't believe you can expose the odata endpoint for web access; rather you're suppose to use SOAP.