https://netsuiteprofessionals.com logo
Join Slack
Powered by
So I am stuck on a Formula in a saved search, we h...
# general
t
So I am stuck on a Formula in a saved search, we have a saved search that pulls the inventory on hand for each of our warehouse, that gets sent to our trading partners via our edi connection. We have 1 column for each of our 3 warehouses. I have been asked to add something in to zero out an item in the other 2 warehouse if we have qty in 1 specific warehouse. Example Item XX, we have stock in W1 and W3, but since we have stock in W3 which we are trying to deplete we want W1 to go to 0. I need to figure out a way to look at W3 and if there stock, W1 needs to be 0, I have tried a case statement mixed with Decode and it doesn't seem to work. Any suggestions or ideas.
g
1. Locate the formula column for Warehouse 1 and edit its formula. 2. Replace the existing formula with the following:
Copy code
sql

CASE WHEN {warehouse3quantity} > 0 THEN 0 ELSE {warehouse1quantity} END
In this formula, we are checking if the quantity in Warehouse 3 (
{warehouse3quantity}
) is greater than zero. If it is, we set the quantity in Warehouse 1 to 0. Otherwise, we keep the original quantity in Warehouse 1. Make sure to adjust the field names 
{warehouse3quantity}
and 
{warehouse1quantity}
according to the actual field names in your saved search. By using the CASE statement with the logical condition, you can conditionally update the quantity value based on the inventory in Warehouse 3. Repeat the same process for Warehouse 3 column formula if needed. Save your changes to the saved search and test it to see if the quantities are being zeroed out correctly based on the inventory in the specified warehouses. Please note that the field names and formula syntax may vary depending on your specific NetSuite setup and field naming conventions. Adjust the formula accordingly based on the actual field names in your saved search.
t
Chat GPT answer ^ lol
g
yup
its how you fix it btw
t
It sounds like you are asking your search to do an automatic Inventory adjustment
Or Inventory transfer.
t
That will not work for us, we do not have fields for warehouse1qty, warehouse3qty, the search is currently using a Decode function to pull the location qty if the warehouse if a certain location, "DECODE({inventorylocation}, 'Mequon', {locationquantityavailable}) " but what I need to do is have it insert 0 instead of the locationquantityavailable if another location has qty. Maybe this will make what I am trying to do clearer "Case when DECODE({inventorylocation}, 'W1', {locationquantityavailable}=0) then DECODE({inventorylocation}, 'W3', {locationquantityavailable}) else end"
Not trying to do a inventory adjust or transfer, just trying to zero out one warehouse if stock in another.
t
I think you are on the right path.
t
But I get Error:Invalid Experssion,
t
Is this Formula(text)?
t
The first example works, pulls the locationquantityavailable total, formulas are numeric as it pulls a number
t
Maybe? CASE WHEN DECODE({inventorylocation}, 'W1', {locationquantityavailable}, 'W3', {locationquantityavailable}) = 0 THEN NULL ELSE {locationquantityavailable} END
s
Is this a summary search by chance? If so I have something that is working for me.
t
It is a summary saved search, and @Tim did not work, same error
s
I'm only playing with two locations, but this is working for showing W2's qty as 0 if W1 has more than 1.
Copy code
case when {inventorylocation} = 'W1' and {locationquantityavailable} > 0 then 0 else case when {inventorylocation} = 'W2' then {locationquantityavailable} end end
t
@Stephanie Hughes yours worked but also did not work, in my screen shot, 2 of them worked but one did not, that is weird, the second column should all be 0
message has been deleted
Actually it did not work, now that I look at it closer, still showing qtys in the second warehouse
d
I think I know how to do this, you would use a summary type MIN for 'W1 qty available' and when you're on a line with location = W3, if there's qty available for W3, the W1 field should be zero, otherwise it should be null. I'll put together an example
potentially instead of sending the search results directly via EDI, you should just process the search results in script and then sending it via EDI
okay, so given your example, formulas for 'W2 available', 'W3 available' etc, all stay the same (I'm presuming they're one of summary type "Sum" "Minimum" or "Maximum") For the 'W1 available' column, use Formula(Numeric) with summary type "Minimum" with this formula:
Copy code
CASE {inventorylocation}
  WHEN 'W1' THEN {locationquantityavailable}
  WHEN 'W3' THEN DECODE(SIGN({locationquantityavaiable}),1,0) 
END
Can also be written entirely with DECODEs:
Copy code
DECODE({inventorylocation},
  'W1', {locationquantityavailable},
  'W3', DECODE(SIGN({locationquantityavailable}),1,0)
)
Or more verbosely as:
Copy code
CASE 
  WHEN {inventorylocation} = 'W1' THEN {locationquantityavailable}
  WHEN {inventorylocation} = 'W3' THEN 
    CASE WHEN {locationquantityavailable} > 0 THEN 0 /* else null */ END 
END
explainer: when there's stock avail. on the (non-summarised) location = W3 row, the value of the W1 column in that row will be zero. whereas when the location = W3 has 0 quantity available, the value of the W1 column will be null. This works because nulls are ignored for summary type Min/Max/Average (not so for Group FYI)
t
@David B so the decode example did not give me an error, but it is only putting 0's in the W3 column if there is a qty in W1 but W3 was already at 0, it is not placing a 0 if W1 has qty and W3 has qty. In the attached screen shot, column 1 is W1 and column 2 is W3, the 2 0's in blue are fine, there was not qty in W3 only W1, but the 60 that is highlighted in green should be also 0, since there is also qty in W1 of 25.
I think i got it, I forgot to change the summary, once I changed it, it seems to be working.
👍 1
d
Hey, your original example had W1 and W3 the other way around. You were trying to deplete W3 The formula I posted was for the columns of locations you weren't depleting, which was W1 in you orig example.
2 Views
Open in Slack
PreviousNext
Powered by