If Then Else statement help plz 2

[markajem]

I think I am having one of those moments where your brain just does not function correctly.

I would like to combine the below two if then's into one statement and just for the life of me can't get it right.

A little assitance would be greatly appreciated.

Thanks

[tt]
#1
if isnull({VIEW_GP_OnHand.QTYONHND}) then {VIEW_Phy_count_NEW.phy_Total_Pcs}
else {VIEW_Phy_count_NEW.phy_Total_Pcs} - {VIEW_GP_OnHand.QTYONHND}
[/tt]
[tt]
#2
if isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then {VIEW_GP_OnHand.QTYONHND}
else {VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs}
[/tt]

 

[muffntuf]

Try this:

(if isnull({VIEW_GP_OnHand.QTYONHND}) then {VIEW_Phy_count_NEW.phy_Total_Pcs}
else {VIEW_Phy_count_NEW.phy_Total_Pcs} - {VIEW_GP_OnHand.QTYONHND}) else if

(isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then {VIEW_GP_OnHand.QTYONHND}
else {VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs})

Thanks,
muffntuf

 

[markajem]

Thank you muffntuf, just what I needed. I guess my brain is still on Weekend time.

Thanks

 

[markajem]

Oups the formula checker does not like the code in red.

(if isnull({VIEW_GP_OnHand.QTYONHND}) then {VIEW_Phy_count_NEW.phy_Total_Pcs}
else {VIEW_Phy_count_NEW.phy_Total_Pcs} - {VIEW_GP_OnHand.QTYONHND}) 

[COLOR=red]else if[/color]

( isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then {VIEW_GP_OnHand.QTYONHND}
else {VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs})

 

[lbass]

Try:

if isnull({VIEW_GP_OnHand.QTYONHND}) and
isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then
0 else

if isnull({VIEW_GP_OnHand.QTYONHND}) then
{VIEW_Phy_count_NEW.phy_Total_Pcs} else

if isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then
{VIEW_GP_OnHand.QTYONHND} else

{VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs}

-LB

 

[muffntuf]

Thanks for the amendment LB

 

[markajem]

Muffntuf and LB thanks for your help. Although the revised formula checks okay it is not quite doing what I need it to do. After looking at this even closer now I think I should have given you an example of what I was trying to accomplish. Very sorry if I wasted your time in not doing so.

This is our setup, we have an accounting system and WMS. What items are in the accounting system should be in the WMS logically but it is possible that one item is in one but not the other but never an item would not be in both, it just can't happen. My main table that I drive off of is determined by either of the two master item tables, accounting or wms. Master_item being my drive key and book_item being that of accounting and phy_item of the WMS.

[tt]
master_item book_item phy_item
00125 null 00125
00201 00201 00201
00276 00276 null
[/tt]
So therefor an item would have to be in at least one of the two systems. So this part of the formula can be eleminated

if isnull({VIEW_GP_OnHand.QTYONHND}) and
isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then
0

because it just can never happen. That's why I did not compensate for it originally. But because of my lack of an example you did not realize it, sorry.

Keep in mind now that this is for a variance on a physical inventory count. {VIEW_Phy_count_NEW.phy_Total_Pcs} is the physical inventory count, {VIEW_GP_OnHand.QTYONHND} is the book count. Therefore if the phy count is less than the book the variance should be a negative, if the book count is higher than the physical count then the variance should be a plus.

Here are my desired results:
[tt]
Item Book Phy Var
00125 Null 100 100
00201 100 50 - 50
00276 200 Null -200
---- ---- ----
300 150 -150
[/tt]

Now I believe my original formula did this

for book:

#1
if isnull({VIEW_GP_OnHand.QTYONHND}) then {VIEW_Phy_count_NEW.phy_Total_Pcs}
else {VIEW_Phy_count_NEW.phy_Total_Pcs} - {VIEW_GP_OnHand.QTYONHND}

for phy:

#2
if isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then {VIEW_GP_OnHand.QTYONHND}
else {VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs}

My problem is not being able to combine them to work together.

Again sorry for the lack of an explanation and again thank you for your time.

 

[lbass]

I think you must have your example wrong--shouldn't the -200 in the variance column be +200? Are you saying the following does not work? I think it should-- and so should my original formula--if a criterion isn't met, it is ignored.

if isnull({VIEW_GP_OnHand.QTYONHND}) then
{VIEW_Phy_count_NEW.phy_Total_Pcs} else

if isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then
{VIEW_GP_OnHand.QTYONHND} else

{VIEW_GP_OnHand.QTYONHND} - {VIEW_Phy_count_NEW.phy_Total_Pcs}

-LB

 

[markajem]

Okay LB that did it with a little change.

Added = Blue
Switched around = Teal

if isnull({VIEW_GP_OnHand.QTYONHND}) then
{VIEW_Phy_count_NEW.phy_Total_Pcs} else 

if isnull({VIEW_Phy_count_NEW.phy_Total_Pcs}) then
({VIEW_GP_OnHand.QTYONHND} [COLOR=blue]* -1[/color]) else

  [COLOR=teal]{VIEW_Phy_count_NEW.phy_Total_Pcs} - {VIEW_GP_OnHand.QTYONHND}[/color]

Your formula was correct at first but my totals were not coming out correctly so I thought it was in the formula. Once I switched the last "else" and added the *-1 it worked perfectly.

-200; I think maybe you are right.

Thanks again for your help. Now I can stop tearing the hair out of my head and sleep well tonight .