tonystremain Green Ribbon

I'm not sure if we have the advanced reporting module. I've checked our enabled features and couldn't see any reference to that. We also don't have the commission module. For now, I've created multiple columns with a formula currency, so with the example above: 1. case when sum({netamountnotax}-{shippingamount}) < 1000…

Adding SUM to the calculations has done the trick. It loads much faster than the one I had originally created - up to 50% quicker! Thanks. I'm also trying to implement a custom transaction body field with the calculation as you suggested and I get ERROR: Invalid Expression. I think the change in SQL is enough for now,…

As soon as the total goes over 12000 then the calculation doesn't work. It's only calculating 5% on the total and nothing else. I.e. Sales total = 22804.89. Expect commission is 1293.34. Calculated is 1140.24.

Thank you for your suggestion. I like the idea of using a calculated transaction body field to help shorten the SQL. So I guess the calculation would simply be {netamountnotax}-{shippingamount} and I would replace that custom field with those references in the SQL? I also tried your suggestion for cumulative percentages…

Thank you for your input. Will do.

Hi Evan, Yes the search is marked as public. I currently have 'set by' as an available filter. Regards, Tony.

That worked perfectly. Thank you.

Hi Michelle, I have since tried count, and this ignores how many customers there actually are, and just counts 1. So if there are multiple clients with same transaction in same month, then the number is only 1. Do you see any issues with my previous formula? Thanks, Tony.

@mlucentales 1. I have tried both. But currently using Customer. 2. Results Tab. 3. (Standard) Date of First Sale : is on or after 6 months ago, Main Line = True, Transaction Type : any invoice & credit memo. (Summary) Formula - numeric sum({transaction.amount}) greater than 0 4. Lead Source Grouped, Formula - Numeric sum…

SOLVED - i was using the formula in standard filter, but when i used it in the summary filter it worked fine. This makes me very happy :) Thank you for your help.

i have also tried this now using summary type as count: case when to_char({firstsaledate}, 'MM') = to_char({today}, 'MM') then {entityid} end This seems to work for every month apart from the current month as above. If i put -1 etc... the other months are fine. For current month i get ERROR: Invalid Expression.

You almost had it :) ... had to tweak it ... but this now works ... sum(case when {trandate} > add_months({today},-3) then ({netamountnotax}-{shippingamount})/3 else 0 end)/NULLIF(sum(case when {trandate} < add_months({today},-3) then ({netamountnotax}-{shippingamount})/12 else 0 end),0) Thank you so much for your help.…

Hi Michelle, Thank you. I tried what you suggested but it still counted a client x times if they had multiple invoices in the same month that they placed their first order, where I just want to count them once. I have since got the following to work: count( distinct(case when to_char({firstsaledate}, 'MM') =…

@mon I tried this, but it didn't seem to change anything.

Thanks Mike - that worked perfectly. Perhaps you can help me with my next challenge i'm facing since this!! So I have a one formula column doing the following calculation to show me the average spent for the previous 3 months: case when {trandate} > add_months({today},-3) then ({netamountnotax}-{shippingamount})/3 else 0…

Hi Michelle, You got it :) Thank you so much! Tony.

Hi Carlo, What we are trying to do is get a list of customers that have purchased within the last 6 weeks, but show 6 months worth of sales for them. We seem to be able to get one or the other - is it possible to get a saved transaction search to filter on two separate date ranges? It's like an IF statement i.e. if ordered…

We have a web site which has content available such as videos for users of our software to learn more. Currently each time a video is watched, an email is sent to us, with the user and video watched, which we use to compile relevant metrics in Excel. I was looking to POST this information into a custom record online form…

I have since found out from NS Support that posting to a custom record online form is not supported due to security reasons to prevent spam. Back to the thinking board ...

I wish I could but that's a little over my head ... I have managed to get the site to now create the custom record entry, but my final stumbling point is the redirection after form submission. I need this to be dynamic based on content submitted on the record ... could this be done on the Save Record Function with custom…

Just to close of this thread, I was able to get my solution to work with the following script: function saveRecord() { var seminar = nlapiGetFieldValue('custrecord_msu_seminar'); self.parent.location="http://www.msuniversity.co.uk/" + seminar + ".asp"; return true; } However it only worked with the default online form -…

Hi Kirk, Thank you for your reply. This is a form outside of NS that I am using to POST to a standard NS form so I don't think the tag is necessary - right? I believe the NLFORM tag is only used when defining your own custom form template. Regards, Tony.

I didn't like the format that the email was sent, so this way I was able to personalise the message.

its a user event script at the moment with afterSubmit function. here is the code if you can shed any light it would be appreciated: function afterSubmit(request, response){ var customer = nlapiGetFieldValue('entity'); var email = nlapiLookupField('customer', customer, 'email'); var emailchoice =…

we faced similar issues entering sales orders. the process was very slow and manual. we now use a third party application called celigo, to which we created a spreadsheet that we use to process the order and this gets instantly uploaded into netsuite - works brilliantly for us!

I also got this script to work with the help of khultquist - works great! thank you so much!

has anyone been able to get this script to work with payment receipts? i have tried and come across errors!

Hi. Thank you for responding. Do you have the exact script that I would need to create?

Hi Brett, Thank you for your input, and sorry for the delay in coming back to you. It’s been hectic here since September, and I’m only just returning to previous projects. We currently host our external site with 123-reg.co.uk and they seem to offer an e-commerce solution. Could you explain a little more about the order…

Hi Brett, Does the new Reference Checkout 2.01.0 offer any new functionality/options to explore too? Thanks, Tony.