https://netsuiteprofessionals.com logo
Join Slack
Powered by
How can I export an Analytics workbook as a query ...
# suitescript
m
How can I export an Analytics workbook as a query script?
b
probably not as easy as you would do searches
queries have conditions (similar to filters), columns (similar to columns) and sorts (kinda a part of columns)
m
Yes, I have a chrome extension that will export any saved search as suitescript. Would be awesome to have the same thing for an analytics workbook.
b
probably because its harder, queries also have components, which can have deeper relationships
your current best best is to load the query in suitescript and reconstruct it by looking at its properties
m
Is there an easy way to get the query out of the workbook?
b
if you dont use joins, its probably easy
m
ah, but the joins are where all the power is
b
if you use joins, its represented by multiple sub objects
and you would need to recreate each sub object in script
ask me the question again after the feature has been release for a release cycle (so in 2020.1) and ill have a better answer
right now im just too disappointed in the actual ui to play with it too much
m
okay. Analytics is already using query underneath, so I figure there must be a way to extract it.
Really? I love the UI compared to saved searches. So much more intuitive
👍 1
d
one thing that helped me when translating to script manually, was you can hover over the column to show additional info. mainly the directional joins etc.
b
im not impressed by things that have errors in the console when loading up
or that has errors when opening previous workbooks (though this one might be the ad blocker)
🛑 1
d
one I do not like though, I wish the API was a bit more unified with N/search as far as the final Object output. It would be nice if the results looked more or less exactly the same so that could just swap out search with query when necessary
m
Yes, would be nice.
j
What I did to recreate the workbook in script is to just load it and look at the object. The joins are a little difficult to follow at first, but once you see how the object is set up, it wasn't that bad. @makon
m
@jacksonp what do you mean by load it? Like in the console? With a script? Just in the browser? And what object are you talking about?
j
What I did was create a workbook in the ui and then do a query.load in the script debugger
m
oh, ive never used the script debugger
j
I can give you an example of a script that goes from Kit > Members then on those member item's to a custom record sublist
Copy code
var kitQuery = query.create({
    		type: query.Type.ITEM
    	});
    	
    	var memberJoin = kitQuery.autoJoin({
    		fieldId: 'member'
    	});
    	
    	var targetItem = memberJoin.joinTo({
    		fieldId: 'item',
    		target: 'item'
    	});
    	
    	var vendorSOCost = targetItem.joinFrom({
    		fieldId: 'custrecord_ss_vscitem',
    		source: 'customrecord_ss_vendorsocost'
    	});
    	
        var firstCondition = kitQuery.createCondition({ // KIT ID
            fieldId: 'id',
            operator: query.Operator.ANY_OF, 
            values: item
        });
        
        var secondCondition = vendorSOCost.createCondition({ // VENDOR SO COST VENDOR or EMPTY
            fieldId: 'custrecord_ss_vscvendor',
            operator: query.Operator.ANY_OF, 
            values: [null, costObj.vendorField]
        });
        
        kitQuery.condition = kitQuery.and(firstCondition, secondCondition);
        
        kitQuery.columns = [
                            kitQuery.createColumn({
                            	fieldId: 'id'
                            }),
                            targetItem.createColumn({
                            	fieldId: 'id'
                            }),
                            memberJoin.createColumn({
                            	fieldId: 'quantity'
                            }),
                            vendorSOCost.createColumn({
                            	fieldId: 'custrecord_ss_vscvendor'
                            }),
                            vendorSOCost.createColumn({
                            	fieldId: 'custrecord_ss_vscsocost'
                            }),
                            targetItem.createColumn({
                            	fieldId: costObj.costField
                            }),
                            targetItem.createColumn({
                            	fieldId: costObj.netField
                            })                                
                            ];
        
        kitQuery.sort = [
                            kitQuery.createSort({
                                column: kitQuery.columns[1]
                            }),
                        ];
        
        var resultSet = kitQuery.run();
        var results = resultSet.results;
m
okay, that's a very clear example. I see how that corresponds to the interface
j
Glad to help, I like the analytics workbook so far. It's very powerful.
m
Do you have an example of how you load a current workbook in the script debugger?
j
I can make one real quick
m
nevermind, i found it in the documentation
j
Copy code
/**
 * @NApiVersion 2.x
 * @NScriptType Suitelet
 * @NModuleScope SameAccount
 */
require(['N/query'],
		
function(query) {
onRequest();

        function onRequest(context) {

        	var updateVSCQuery = query.load({
        		id: 'custworkbook10'
        	});
        	
        	var results = updateVSCQuery.run().results;
        	
        	for (var i = 0; i < results.length; i++) {
    			var values = results[i].values;
        	}
        	
        }         
        
        return {
            onRequest: onRequest
        };
   
   
});
With what ever your workbook internal id is
m
Okay, i ran that in the debugger, but i'm not seeing where the output is.
j
At the bottom theres a local variables tab
message has been deleted
m
Ah! I didn't have breakpoints set. That's handy. Wish it didn't log you out of the main window though. Thanks so much.
j
No problem!
Yeah logging in and out is a pain, but you can access everything is the debugger server that you can in the main.
d
for any module that can be accessed clientSide, I would recommend using Chrome browser console/Snippets instead of server-side debugger. has been very helpful in such scenarios when I just need a sandbox to play around in
m
Yeah, that's what I normally use
👍 1
@dbarnett could you give an example of how you'd run the same script above in chrome console?
i got it.
Copy code
require(['N/query']);
const query = require('N/query');
var updateVSCQuery = query.load({
    id: 'custworkbook34'
});
var results = updateVSCQuery.run().results;
for (var i = 0; i < results.length; i++) {
    var values = results[i].values;
}
n
@battk I've had issues with previous workbooks and adblock as well.
3 Views
Open in Slack
PreviousNext
Powered by