3948522 Green Ribbon

SO are you just trying to get the Average selling price over a period of time? If so try using a saved search and making a customer formula something like: Sum({amount})/Sum({Quantity}). Then depending on your date selection the formula would automatically update. that formula is not a working formula... just something I…

I'd be interested to know the answer to this one as well. Maybe they have fixed one of the issues I've been fighting with for some time now. Item returns put the inventory back into the system with a cost = $0 value.

I have found that sometimes using the 'Greater then 0' filter doesn't always work correctly as sometimes the system see's the 0 as NULL and will return the results. I like to use ' greater than or equal to 1', I have found this filter works better on searches like this.

We actually solved this issue by using Celigo's Excel SmartClient. What we do is pull the PO's we are receiving against into on tab. Then on a second tab setup what is called an "Add Record" and we can select the items on the referenced PO's from tab 1 and then just hit a button and the corresponding item receipts are…

Jim, Try this work around. 1) do a formula to set all 'Null' fields to 1 2) highligh lines = 1 SO your highlight formula would look like this: https://docs.google.com/document/d/1QrWE6YBEfoS4y4Dgrtq7Errb1kbPm8Tlnjntkz5yUWU/edit

How much detail are you looking for? We created a search and looked at the last time it was received and based on that determined if there was not activity since that date. Here is a link to the settings for that search: https://docs.google.com/document/d/1...hl=en_US&pli=1 __________________ -- Kelly Izer |…

Also here is another post that was done in reference to removing the colon: https://usergroup.netsuite.com/users/showthread.php?t=23259&highlight=remove+colon

OK I see what you're doing. Then do this. group as you stated but remove the location field from the rows. Then in your columns what you will do is CASE WHEN {binnumber.location} = 'AFOSA CENTRAL' THEN {binnumber.QUANTITY ON Hand} END Then do the same for the on Orders CASE WHEN {binnumber.location} = 'AFOSA CENTRAL' THEN…

try something like this CASE WHEN {binnumber.location} = 'AFOSA CENTRAL' THEN {binnumber.location} END

in your results don't group by location. The location is now being displayed in the individual columns. Try that.

try making a formula field: Formula (Numeric) = nvl({QuantityAvailable},0) and see if that gives you a 0 for all null or 0 values.

Here is another search that I created to look at Open PO's that we have in our system: https://docs.google.com/document/d/1D9NBTJ3XgtJQmkWy6cZpPGlWlB5uhkh6kq_3758VMgk/edit?hl=en_US -- Kelly Izer | Islanders/IslandSurf.com K.Izer@IslandSurf.com

for your PO's Try something like this: NOTE: you will probably have to change it around some to fit your needs but maybe this will give you something to go off of: case when {transaction.location} = {inventorylocation} and {transaction.type} = 'Purchase Order' and {transaction.custbody_ship_date} BETWEEN {today} AND…

No Problem!! Sorry I didn't get back to you on the earlier response but I've been out of the office and just got back in earlier this week so I have to admit I haven't been keeping up with the user group lately. I have to say I'm glad I've been able to give back some of the knowledge I've pulled out of the user group. A…

You can do all kinds of things with the dates: Here is one I built that looks at the YEAR: https://docs.google.com/document/d/1H5doHoZgP6NSQQ1oQfnQbSaX-7QLHbPuBmGWnHn1HDg/edit?hl=en_US and here is one that looks at months: https://docs.google.com/document/d/1UMbofEyk4C85wG3Wm6Ll-pcMArcd8huoFzm0Bl8gqpE/edit?hl=en_US…

Mayur, 1) The zero does not count in the sum of the total 2) I'm not sure why the date formula is not working for your date ranges. Everything looks correct from what I can tell and that formula works for me in regards to date filtering. what do you have in your criteria? 3) The reason the credit is not being subtracted…

OK well using an Inventory Adjustment still counts as a "transaction". So that being said here is the concept behind the search I built. 1) it looks for any item that has the transaction types listed in the criteria: -Transaction : Type -----is any of Cash Refund, Cash Sale, Credit Memo, Item receipt, Purchase Order,…

After you created the items... how did you get the inventory into the system? if you received them or Adjusted them in.... remember that those are considered transactions. Soooo starting on an Item search. Add a criteria for: Filter-----------------------Description Transaction : Type --------- is any of Cash Refund, Cash…

also here are the answers to your other questions: I am still learning Netsuite, so what do you mean how did I get the inventory into the system? -- Are you selling inventory items? If so after you created the items you had to populate the Qty on hand some how. You either did this by placing a purchase order and receiving…

Hey JMU, have you tried putting a formula field in and then referencing the needed field? {Applied To Transaction : Quantity} I know it "should" be the same results as just pulling the field directly but I've seen Netsuite do some odd things and it wouldn't surprise me if you did it this way and it wouldn't show 2 lines.…

I'm pulling this off the top of my head but try this: You may have to play with the grouping setting a little bit as I know that AVG of a percent can give you some odd results. Formula (Percent) AVG SUM((case when INSTR({entitystatus}, 'Won') > 0 then 1 else 0 end)) / SUM(count({internalid})) [COLOR=Black]OR try this…

one way to do this is that you could write a CASE WHEN statement. Formula(Text) : CASE WHEN {ID} = 1 THEN {Value1} || ',' || {value2} || ',' || {Value3} WHEN {ID} = 2 THEN {Value1} || ',' || {value2} || ',' || {Value3} else NULL END How many ID's do you have? If this list is growing and changing I think there is a dynamic…

Well, here's a big challenge for you formula gurus! :) I have a Summary Transaction Search filtered to Invoices, Credit Memos and Journals. I have one complex formula that works well and behaves properly when you click on a Grouped Field to drill down to the details for that Group: ROUND(SUM(NVL({amount},0) - 1.15*(…

OK well then we need some more info 1) what type of search are you starting from? (transaction or customer?) 2) are you ONLY trying to sum the TOTAL amount of the invoices 3) what record is the custom field on? (the invoices or the customer record?) so basically the calculation would be ......... the sum of all a customers…

OK Mr. Oz...... I think I have an idea. first lets outline what you are tying to obtain 1) look at all sales orders 2) highlight the sales orders that can not ship complete SO with that being said what you want to do is 1) take a sales order and sum the total Qty ordered 2) sum the total Qty committed if this balances…

CASE WHEN ({customer.custentity_is_serv_cust} = 'T') THEN {costestimaterate} WHEN ({customer.custentity_is_sales_cust}='T') THEN {rate} ELSE NULL END also, I have found that when pulling fields in Netsuite it's been adventitious for me to always wrap the returning fields in an NVL statement. CASE WHEN…

By 2) are you ONLY trying to sum the TOTAL amount of the invoices I meant that you want to sum the total amount of an invoice not the amount due or that amount paid. So if you had 3 invoices for a customer you want the formula to be the sum of all invoices - custom field OR $30-x. Correct? 1) Amount = 10 Amount paid = 8…

Or try something like this: ( nvl({quantityonhand},'0') + nvl({quantityonorder},'0') - nvl({quantitybackordered},'0') ) / nullif(nvl({preferredstocklevel},0),0)

Also you could try adding a nullif 0 statement on the denominator. (case when {type} = 'Credit Memo' then nvl(({amount}*-1),'0')else 0 end) / nullif((case when {type} = 'Invoice' then nvl({amount},'0')else 0 end),0) [COLOR=Black]Another thing is that if you want to always have a positive just wrap the field in an absolute…

There you go! Evan's the pro! Glad you got it working