stephen.gray Green Ribbon

savethepennies what limitations have you found on how queries can be written in excel? is it join limitations in MS query?

Hi @al3xicon Its not really anything to do with the size of the business. Its the amount of data you need to import thats important. A small manufacturing firm could have many more transactions in its database than a large professional services company. I've used it with small and large firms and just filtered and rolled…

Excel (via ms query) is a great tool for running queries and using it to filter your data then report in excel. I've used it to run P&L's, balance sheets, transaction lists etc. You can setup the parameters in your excel sheet e.g. the date range and then refresh the query each time you update these. Works great and saves…

I've done this via excel (using MS query) many times. You'll be prompted after you run the query to choose the cell with the value in it. Message me if you want to chat more.

In microsoft query in the criteria field you can use the square brackets [ ] instead of entering a value. When you then run the query it'll prompt you to put in a cell reference with the value you want to use e.g. the month. You can then update the month in that cell refresh the query and it'll return the new months data.…

cool thanks for the tips!!

Hi Guys Just found that this script runs when i try and add a line item in a different tab i.e. the sales team tab when i add a sales rep to an opportunity. Its only supposed to run when i add an item for sale. Its strange as the only place where there is a field called custcol1 is on the items tab. any advice? thanks…

I recopied the old code into the production and its now working so i mustn't have copied it properly. I also tried your code in the sandbox and it works well and its obviously much cleaner - thanks for your help again!!

thanks Guys that worked. I have another strange one i'm hoping you can help with First of all my guess is that there is a much easier way to write the below code which checks on save of an opportunity if the expected close date is later than the earliest date on the line items. This is to alert the user if they've edited…

Hi The custom date field is definitely a "Date" type field. Not sure about the expected close date as its a standard NetSuite field - any way for me to check the type of standard fields? Anyway the nlapiStringToDate worked a treat - very helpful!! I had actually tried using that function before but there must've been an…

I'm sorry you see it as spamming. I'm certainly not a spammer. I'd define spamming as more indiscriminate advertising. I'm trying to help people with specific reporting issues solve them in a specific way. ODBC is an excellent tool and I've had many people contact me from this forum who are very pleased to find someone who…

Hi There I work for a consulting company that has a lot of experience with NetSuite and its reporting and helps a lot of clients in this area. I've been using Excel ODBC (ms query) for while now with various clients and its an excellent solution for creating complex query's and reports. I've created some templates such as…

Hi I work for a consulting company and we help many clients with NetSuite reporting especially via the suiteanalytics ODBC connection which is an excellent tool. There may be something we can do to help you with this query. Send me a note on…

Hi There I work for a consulting company that has a lot of experience with NetSuite and its reporting and helps a lot of clients in this area. I've been using Excel ODBC (ms query) for while now with various clients and its an excellent solution for creating complex querys and reports. I've created some templates such as…

ODBC is great for reporting. Its gives access to the table structure and allows you to create very flexible models. We've setup many models - P&Ls, balance sheets etc through the direct ODBC link with excel. Contact me on stephen.gray@businessadvisorysolutions.co.uk if you want to discuss more.

Hi Joanna I work for a consulting company that has a lot of experience with NetSuite and its reporting and helps a lot of clients in this area. I've been using NetSuite suiteanalytics ODBC connection to pull data into excel via ms query (which you'll already have as part of your Microsoft Excel subscription) for while now…

Hi There I have a lot of experience writing excel queries through SuiteAnalytics ODBC and I can help with your question. The transactions table consolidates all transactions but there are also other tables that split out certain types of transactions into other tables such as opportunities. Send me an email to…

Hi there I have a lot of experience writing excel queries through SuiteAnalytics ODBC and I can help with your question. Send me an email to stephen.gray@businessadvisorysolutions.co.uk and i'll come back to you. thanks Stephen

here is the SQL I have used: Its a very simple query but for some reason its not working TRANSFORM Avg(CONSOLIDATED_EXCHANGE_RATES.AVERAGE_RATE) SELECT CONSOLIDATED_EXCHANGE_RATES.FROM_SUBSIDIARY_ID FROM Administrator.CONSOLIDATED_EXCHANGE_RATES WHERE CONSOLIDATED_EXCHANGE_RATES.TO_SUBSIDIARY_ID=1 AND…

Hi I work for a consulting company that has a lot of experience with netsuite and its reporting and helps a lot of clients in this area. I've been using excel ODBC (ms query) for while now with various clients and its an excellent solution for creating complex querys and reports. I've created some templates such as P&L's,…