Nick Horowitz Green Ribbon

NetSuite added Created By and Date Created as directly pickable on the Criteria tab. So you should be able to set these directly. I would not fiddle with a formula. Maybe you have to use "Use Expressions" and construct your criteria using "And", "Or" and parenthesis? Otherwise NS treats all the criteria rows as "ANDs". If…

Mondray, yes your formula works with the hard-coded date values of '2010/08/25' just fine. I got this far, too. But as soon as I replace that customer a bracketed variable for example {customtextfield} I get Invalid Expression. It's the field security that Evan mentions. When I do Evan's suggestion with the case statement,…

Evan. Bingo! Thanks!! It worked like a charm. I hate that field security always bites me in the butt!!

Evan, I am having a similiar problem with this to_date. Just cannot get it to work. I think it's the to_date security problem. I tried using a case but that didn't work either! Can you help? Trying to concatenate a string of the week# from a string that contains yyyy/mm/dd (string type field not date type). Should give for…

Doesn't matter. The NS ODBC driver is agnostic. As long as the report writer and access ODBC (which Crystal can) and issue SQL calls you are good.

The mainline is line0 in the transaction rows. It stores some header information about the transaction. There is nothing there because it isn't linked to anything. Only the tran lines 1+ are actually linked to something. So that's why you need to exclude the mainline (mainline=F)

They also let you report on income. How does income differ from sales? Income and Sales are synonyms.

Sales have been billed (either Cash Sale or Invoice). Whereas Sales Orders are just orders that have been placed. Remember the sequence of transactions are: Opportunity > Estimate > Sales Order > Item Fullfillment > Invoice/Cash Sale

Well, the only place to see a batch type report in NS, is on the ACH Status screen. You use the drop-down at the bottom of the screen to select the batch#. You could possibly automate a screen scrape using the DOM. But try calling Deryk and see if he can create some type of structured file for you instead of an email. He…

Can you possibly modify the report to group by type. Then you could see phone call, emails, etc. as separate groups with counts.

No. But if you ask Derek at Coastal, he can auto email you a report of each batch that shows the customer, amount, date, and batch total. Very helpful for recon.

Pixel, NS uses Oracle 10g, so there are many Oracle 10g functions that work inside a Function statement in a NS saved search, but aren't documented by NS. I recently had a problem where I had to get a value from the previous row in a search. The conceptual issue is called interrow comparisons. Oracle 10g has a function…

I think there is a Days Late field, which you would set to > 0, or it may be called Age (as in the Aging schedule)

Change your preferences to "Report by Date" then run your report by date range. HOWEVER, be aware that you may be slightly different numbers if you have any transactons where the date on the transaction is different than the period on the transaction.

I don't think you can get the matrix like you want. But if you sort by date ascending, and then include a count({InternalID}) formula, it would give you a count next to each transaction which you then could export that data to Excel and run a pivot table in Excel: the customer for the row, and count for the columns, with…

Malin, this is a really frustrating design problem with NS that you have to manually update the ODBC views every time a new field is added. (Same problem with WS, CSV Import, and Saved Searches). Can't you redesign the platform such that the entire schema is automatically exposed? That would seem a much more efficient way…

Yes, you can use most Oracle 10i functions in the formula fields. I think the function is TO_CHAR but look up a Oracle 10i reference on Google.

LIKE with the percent signs are the wildcard syntax for Decode

Oh sorry I missed that you rewrote it as a Case stmt using LIKE Decode is really designed for menu selections so it is intended for exact matches only. Use the CASE stmt with LIKE for partial matches & wildcards. That being said, I am pretty sure the % is the general wildcard in Oracle SQL so the % should work in Decode,…

I think I remember this problem from ODBC...that the tax amount field in the mainline is empty. I opened an enhancement request long time ago but then forgot about it. I found several inconsistencies between the tax, shipping, & total fields in the mainline vs those same fields on the line items.

Nope that is a crosstab query. You would need the ODBC module, and do the query in MSAccess or Crystal Reports.

O.K. Steph, I have given your problem a lot of thought over the past few days. I went back and looked at your original screenshot from Post #5. Basically, you need the Amount minus tax minus freight, and you use that number to calculate a 30% commission. Is that correct? Do you need all the columns you indicated in your…

Steph, you are going to have to use ODBC if you want to do this in 1 query. Otherwise your 2 saved search approach and exporting to Excel is the only way to achieve this without ODBC. In the future, you may want to add some custom fields to the transaction to hold these numbers and then you can pull them out of the custom…

See revised post #3 in this thread.

amelia, did Paul's search work for you? If not there are some more sophisticated searches we can do with formula's. For example if your phone numbers are not formatted consistently so that the area code may not always be the first 3 digits.

Fixed length is not possible in NS. You can get what you are calling a flat file (variable len fields, tilde delimiter), but it will have commas as the separators (and no way to change the delimiter), so that is why it called CSV. You may be able to load the CSV into Excel, and then I think in Excel you can create an…

You may be able to cut-and-paste the list of InternalID's into the pick box instead of picking them one-at-a-time. Not sure, but try fiddling with it. I.E., you have one tall column of InternalID's in Excel. Highlight the first and last cell in this column, and CTRL-C for copy to clipboard. You should now have a vertical…

Steph, part of the problem here is that you are using Other Charge items for your freight, instead of the official Shipping Items. The official Shipping Items total, Tax Total, and Invoice Total are stored in the Mainline (Mainline = T) so you could have picked it up there if you were using the official Shipping Items. But…

You can do this in ODBC because you can do complicated Joins. But I think in the native UI you cannot do it. Sometimes you have to run 2 different queries, export them to Excel and then simulate the Join in Excel. But Akp, what is the larger problem you are trying to solve here, i.e., why do you need this report? Maybe we…

Any inactive items that have financial value will continue to appear in reports. The inactive flag only deactivates them from being able to pick them or showing in static (non-financial) lists.