https://netsuiteprofessionals.com logo
Join Slack
Powered by
Can someone help me how to solve the error here pl...
# general
k
Can someone help me how to solve the error here please? I am trying to find the quantity on hand for 3 location
b
You used parenthesis (function parameters) without a function
for the last 3 columns
I don't really know what the first formula is trying to do , but it looks like valid sql
to be honest, I wouldn't know what your columns are trying to do if you didn't state it upfront
k
Thank you @battk the last 3 ones with the case when are just standby I am trying to make the 1st column work and I am still struggling 😞
b
the normal way to do this is to just do an inventory item search
set the filters to filter to your 3 inventory locations
for your column, just add the inventory location quantity column and a internal id column
group by internal id and sum the quantity column
k
I am trying to looking the quantity on hand for 3 location where each item is in 1 line (not duplicated) like so site 1 site 2 sites 3 items A 10 0 8 items B 2 55 0
I want to accomplish it with the Case When statements
b
you are still going to want to do what i said
k
Thanks agian
b
netsuite will have 1 row for each item and inventory location combination
you would need to combine those rows using a summary
k
I am trying to follow your method but I got lost here"for your column, just add the inventory location quantity column and a internal id column"
b
If you want, the first step is to make a search that has a filter for your 3 inventory locations
k
ok that's under Criteria tab right?
b
yes
k
Yep ok done
whenever I have location selected as the Criteria it shows 0 basically nothing.. doesn't seem it likes it
b
You want the one literally \named inventory location
for your columns you want internal id, inventory location, location available, location back ordered, and location committed
you probably also want name too
actually, before i go further, did you want quantity on hand, quantity committed, or quantity available
there are all different things
k
the criteria works now.. its good the result is the next am on
I want to have quantity on hand
b
i believe there is no column for quantity on hand
so you would need to calculate it
will there be backorders?
k
actually under result when I pick the field location on hand it give me the quantity I want which is good!
b
oh
great
do you have multiple rows for each item
k
the only issue now is how to get the other location's quantities
and there is that multiple row for the same item .. I need it to be 1 row
b
that would be what the summary is for
it groups rows
if you want, you can try it now
on the result columns there should be a row for summary type
k
ok so that will be under the internal ID/name correct?
b
you would set the summary type to group for the column you wish to group by
i recommend internal id
then you set the summary type to sum to show the sum of the rows that are grouped
you would want to use this on your on hand column
when run, the search should be grouped should that each row is 1 item and your quantity column is the sum of all 3 locations
k
ok but I don't want the quantity to be sum for all 3 location
b
correct, now is when you want to use the formula columns
k
This is how I want it to be: site 1 site 2 sites 3 items A 10 0 8 items B 2 55 0
b
whats the internal id of one of your locations
k
its 2
you said that's when I want to use the formula column .. what do you mean?
b
you want to write a formula so that the column shows the location quantity on hand when the inventory location is 2, otherwise 0
that is basically a case statement, which is what you had earlier
case {inventorylocation} when 2 then {locationquantityonhand} else 0
The 2 may or may not require quotes, I tend to forget that detail
k
no problem .. ok and now that formula goes in the results correct?
b
yes
k
should it be case when .... ?
b
yes
wait
no
there are 2 ways to use case
k
case when ... when..?
b
I believe its as i wrote
k
ok its says invalid expression 😞
b
case when {inventorylocation} = 2 then {locationquantityonhand} else 0 end
or
case {inventorylocation} when 2 then {locationquantityonhand} else 0 end
i believe those are the same
k
but then the 2 is the internal ID for 1 of the location no? what about the other 2 locations?
b
you wanted 1 column for each location
so you need 3 columns
with very similar formula
the only difference is which internal id you set in the formula
k
ok I will do that now
Also I have the internal ID summary type Grouped and the On Hand summary type Sum is that correct?
b
sure
you will want your formulas summed as well
k
ok the formula works .. I don't see the "invalid expression" but it shows 0 though all 3 locations have inventory
b
click one of the internal ids on the results
it should show the grouped rows for the item
does the location on hand column have a value?
k
Yes it has value the location on hand after I clicked on the internal ID
b
is your formula there
k
Yes it is
it looks like the formula can't call the location to bring up its quantity
I made a fix to your formula and it brought up the quantity!!
Here: case when {inventorylocation.internalid} = '2' then {locationquantityonhand} else 0 end
now the only issue is that they are 3 different row for the same item
b
summary
group by internal id or name
k
Sorry, What do you mean?
b
the summary on the result columns should make it so there are not multiple rows per item
k
I am on the Result tab and all my formulas have summary
or rather say Sum
is that what you mean?
b
internal id or name should be group
k
Ok perfect! its coming up slowly but surely I have them grouped now but the Name
I just need to have all 3 columns now totaled up formula 1 + 2 + 3
Ok its DONE!! 🙂
I think its working
Thank you very much for your help and guidance!
we brainstormed on it 🙂
5 Views
Open in Slack
PreviousNext
Powered by