Challenge to create a back-order App

[#1]

Hi community,

I was wondering if somebody  can help me out.

I've been trying to create a BackOrder report without any luck.

For those how are not familiar with the term BO, Place an order for (a product) that is temporarily out of stock

Background, I have 3 different tables in the system with the following information

1) Sales Lines (Sales detail by Item, Customer, Sales Order, Sales amount, sales Quantity, Line No(Invoice Line Number), etc)

2) Sales Header (Sales Order Number)

3) Item master (Brand and Item number)

These 3 tables Joins naturally, very straight forward.

Since there is not a field for invoiced status I created one with the help of the following IF:

   IF( [Invoiced $]=0,'BackOrder', if(Quantity-[Original quantity] >0, 'BackOrder', 'Invoiced')) AS "Invoiced Status"

With the previous IF statement I got 3 different scenarios:

A)

For the SOR013762 there is only one BO for Line No 50000, PO Orig $ = $1,963.50 (minus) Invoiced $= $0, Dif$ (Backorder) -$1,963.50

B)

SOR013782 BO for line 20000, PO Orig $ = $5,625.90 (minus) Invoiced $= $2,768.30, Dif$ (Backorder) -$2,857.60

THE CHALLENGE....

C)

The last SOR shows a TOTAL BO of $12,022.79 but that amount is not actually the correct BO amount, if we look closely the SOR013847 it is related to 3 different Invoices,  what happened is that when the SOR lines were invoiced, some of the lines were missing and not invoiced on the first or the second invoice, but the third. So the IF statement duplicate, triplicate the BO amount or showed a BO that does not exist.

1) Number 1 Is showing two BO's for a total of $1,535.52 line 10000 , but line 10000 was already invoiced so there is $0  BO

2) Number 2 Is showing 3 BO's  for a total of $1,230.96 line 60000 but just only one is correct $410.32

Attached is very short version of my model with an example of the app an a DB in excel.

Thank you very much!

Have a great week!

[#2]

Thank u again for all your help, advice and for sharing your knowledge, I think that I will skip the invoice number for now and stayed with your version 3.

[swuehl]

Yes, I think that one is the best, too.

Just a note for future postings, it's common practice here in the forum that one's setting the status of the original correct / helpful answer to 'correct' or 'helpful', not your own.

[#4]

My mistake

Already tagged yours as correct.

Thanks