How to display customers who have not received delivery

[hopkinsc]

Hi, i am trying to display customers who have not received a delivery.

I select a REGIONAL MANAGER and a SUPLLIER NAME which then displays the customers relating to that REGIONAL MANAGER who have received an order from the selected supplier. But i want a quick way of finding out the customers for that regional manager which have NOT received an order from the selected supplier.

Does anyone know how i can do this?

I was hoping to have a list box with an expression  or something similar.

My fields are:

BDA Name (Regional Manager)

Cust Name (Customer)

Thanks

[#2]

I am guessing you have some sort of order number field you use for this? should be able to build an expression to give you the customer name when there is no order present for them, such as

=if(isnull([order number]),[Customer Name])

so just have the supplier name in your dimension and that as the expression should give you the customers names who have no order for that supplier

hope that helps

thanks

Joe

[hopkinsc]

Hi, thanks for your reply.

There is a DeliveryNoteNumber, but that wouldn't work as if the customer received no delivery then there would be no entry for that customer from the supplier. (if that makes sense).

I tried :

=if(isnull(DeliveryNoteNumber)[Cust Name])

but that didn't bring back any results.

I have attached a sample..

If you select the SUPPLIER 'Allied Bakeries' and the REGIONAL MANAGER 'Own Store Andrew Goulding' you will see that 12 customers have recieved an order. 2 of his stores are missing (Coity Rd and Llanharry)

I want to be able to quickly see the missing stores...

Thanks

[hopkinsc]

Hi, I have sort of got that example working :

=if(isnull(DeliveryNoteNumber)[Cust Name])

It works if i don't add a dimension, instead using the LIST BOX, i select Expression from the drop down list of fields.

This then displays all customers who have not had an order from the selected supplier, but it displays ALL customers without an order, i only want it to show customers from the selected REGIONAL MANAGER and hide the rest.

Does anyone know how i can achieve this?

[#5]

Hi,

yea I got it working to that points also, you can select a region manager and see which customers sitting under that have no orders, however you won't be able to see which suppliers that customer relates to. This is due to your supplier link going through you order table to get to the customer, so what this means is there is only a link between the customers a supplier when there is an order for it.

If you changed your table design it should be possible to link the customer and supplier tables together and so not have the link through the order table like that.

hope this makes sense

thanks

Joe

[pho3nix90]

Check the attached exmaple, is that what you were looking for? See all suppliers, but indicate wich has been delivered by

[#7]

Yea that's basically the same as what i've done your not able to see which supplier hasn't had an order your only able to go down to customer/regional/store, as I said I think a rework of the design is needed to go down further

[pho3nix90]

agree, the subset ratio is terrible.

[suniljain]

If(Len(Order_Number) > 1 and Len(Delivery_Number)<1, Customer)

Pls make dimention of above expression which gives you list of customer who placed order but do not receive goods.