text box that look up value from another table than the forms record source

[cheekybuddha]

OK, try something like this first:

SELECT
  a.[Entry No_],
  a.[Item No_],
  MAX(IIf(a.[Source Type] = 37, a.[Source ID], NULL)) AS SalesNo,
  MAX(IIf(a.[Source Type] = 5406, a.[Source ID], NULL)) AS ProductionNo
FROM [dbo_KonfAir DRIFTSReservation Entry] a
WHERE a.[Source Type] IN (37, 5406)
GROUP BY
  a.[Entry No_],
  a.[Item No_]
;

If that works we can add in the ItemDescription, but you will need to let us know whether it is unique per [Entry No_]/[Item No_] combination.

 

[zulu100]

Se attached image for result.
Grouping by Item No_ or Description should not be necessary because they will always be the same. They represent a positive and negative. For example we sell two items a need is created which corresponds to "0" value (One record). Now a production order is created to equal the need which represents a "1" in the table (One record) with same Entry No_ value. So the Item and description will always be the same where we have similar values in "Entry No_"
However.. we have sales orders where the same Item number is sold on different sales lines due to the fact that the product is labelled different. It is the same product but it is to be used by the costumer in 2 different facilities therefore different labelling to make it easy for the costumer to see where they use it.
Microsoft Dynamic threats this as two individual product orders and it Will show up twice in the Reservation entry table. I would like to avoid that so grouping by "ProductionNo" also should solve that.
Hope it makes sense

 

[zulu100]

Maybe I also should have mentioned that the same item number and the corresponding description can be represented many times in the Reservation entry table.

 

[cheekybuddha]

I'm flying a bit blind here!

However, from your description I think you can try:

SELECT
  a.[Entry No_],
  b.SalesNo,
  a.ProductionNo
FROM (
  SELECT
    [Entry No_],
    [Source ID] AS ProductionNo
  FROM [dbo_KonfAir DRIFTSReservation Entry]
  WHERE [Source Type] IN (37, 5406)
    AND Positive = 1
) a
INNER JOIN (
  SELECT
    [Entry No_],
    [Source ID] AS SalesNo
  FROM [dbo_KonfAir DRIFTSReservation Entry]
  WHERE [Source Type] IN (37, 5406)
    AND Positive = 0
) b
        ON a.[Entry No_] = b.[Entry No_]
;

 

[zulu100]

Hi
If grouped distinct by Entry No_ and ProductionNo and the Description an Item No_ fields is added I think it is perfect
As you can see in the attached image, production order no 570259 is shown twice. As explained in previous post this is caused by a sales order containing the same item number on two different sales lines. So ProductionNo has to be distinct as well as Entry No_

 

[Vulpeccula]

Shouldn't that be

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]='" & Forms!frm_ctn_List!No_ & "'")

Teach a man to"phish" and he'll never have to work again! Quote from Tim Smith - last seen in Bangkok; whereabouts unknown...

 

[cheekybuddha]

As explained in previous post this is caused by a sales order containing the same item number on two different sales lines. So ProductionNo has to be distinct as well as Entry No_

So which [Entry No_] do you want to see for this ProductionNo ?

 

[zulu100]

It doesnt matter cause SalesNo, Desceiption and ItemNo will be identical for the two Entry No_

 

[cheekybuddha]

OK, so build on that - change the SQL to:

SELECT
  a.EntryNo,
  b.SalesNo,
  a.ProductionNo
FROM (
  SELECT
    MIN([Entry No_]) AS EntryNo,
    [Source ID] AS ProductionNo
  FROM [dbo_KonfAir DRIFTSReservation Entry]
  WHERE [Source Type] IN (37, 5406)
    AND Positive = 1
  GROUP BY 
    [Source ID]
) a
INNER JOIN (
  SELECT
    [Entry No_],
    [Source ID] AS SalesNo
  FROM [dbo_KonfAir DRIFTSReservation Entry]
  WHERE [Source Type] IN (37, 5406)
    AND Positive = 0
) b
        ON a.EntryNo = b.[Entry No_]

If that gives the result you expect then you can join queryReservationEntry to another instance of [dbo_KonfAir DRIFTSReservation Entry] to add the Description and [Item No_] fields

 

[zulu100]

When I run this query I get prompted to enter Parameter Value
Query1.Entry No_

 

[zulu100]

This is what I have been using so far. Since my SQL skills i next to nothing, it has been made in Design view. It pretty much gives me what I need except from the duplicate [Source ID] which is a no go
This is almost certainly not the right way to do it, so I try to get by with the knowledge I have and what I can find online.
It would be nice to have a well written SQL to do the job but it isn't a must for me. Just need to get a result that I can use.
I really appreciated all the help that I have got...

SELECT [dbo_KonfAir DRIFT$Reservation Entry].[Entry No_], [dbo_KonfAir DRIFT$Sales Header].[Bill-to Name], [dbo_KonfAir DRIFT$Reservation Entry].[Item No_], [dbo_KonfAir DRIFT$Reservation Entry].[Reservation Status], [dbo_KonfAir DRIFT$Reservation Entry].[Source ID], [dbo_KonfAir DRIFT$Reservation Entry_1].[Source ID], [dbo_KonfAir DRIFT$Reservation Entry].Description, [dbo_KonfAir DRIFT$Reservation Entry].Positive, [dbo_KonfAir DRIFT$Reservation Entry_1].[Source Type], [dbo_KonfAir DRIFT$Reservation Entry].[Source Type]
FROM [dbo_KonfAir DRIFT$Sales Header] INNER JOIN ([dbo_KonfAir DRIFT$Reservation Entry] INNER JOIN [dbo_KonfAir DRIFT$Reservation Entry] AS [dbo_KonfAir DRIFT$Reservation Entry_1] ON [dbo_KonfAir DRIFT$Reservation Entry].[Entry No_] = [dbo_KonfAir DRIFT$Reservation Entry_1].[Entry No_]) ON [dbo_KonfAir DRIFT$Sales Header].No_ = [dbo_KonfAir DRIFT$Reservation Entry].[Source ID]
WHERE ((([dbo_KonfAir DRIFT$Reservation Entry_1].[Source Type])=5406) AND (([dbo_KonfAir DRIFT$Reservation Entry].[Source Type])=37));

 

[cheekybuddha]

Hi,

Sorry, I've not had a chance to get back here for a while.

Did you make any progress?

 

[zulu100]

hmmmmm... Yes and no
Tried to buy my way to a solution with limited success thougt
Ended up with 3 sub queries combined in one result query.

SELECT [dbo_KonfAir DRIFT$Reservation Entry].[Entry No_], [dbo_KonfAir DRIFT$Reservation Entry].[Item No_], [dbo_KonfAir DRIFT$Reservation Entry].[Description], [dbo_KonfAir DRIFT$Reservation Entry].[Source Type]
FROM [dbo_KonfAir DRIFT$Reservation Entry]
WHERE [dbo_KonfAir DRIFT$Reservation Entry].[Source Type] In (37,5406);


SELECT [dbo_KonfAir DRIFT$Reservation Entry].[Entry No_], [dbo_KonfAir DRIFT$Reservation Entry].[Source ID]
FROM [dbo_KonfAir DRIFT$Reservation Entry]
WHERE ((([dbo_KonfAir DRIFT$Reservation Entry].[Source Type]) In (5406)));


SELECT [dbo_KonfAir DRIFT$Reservation Entry].[Entry No_], [dbo_KonfAir DRIFT$Reservation Entry].[Source ID]
FROM [dbo_KonfAir DRIFT$Reservation Entry]
WHERE ((([dbo_KonfAir DRIFT$Reservation Entry].[Source Type]) In (37)));


SELECT [Reservationsposter - hovedquery].[Entry No_], [Reservationsposter - hovedquery].[Item No_], [Reservationsposter - hovedquery].Description, [Reservationsposter - produktionsordre].[Source ID], [Reservationsposter - salgsordre].[Source ID]
FROM [Reservationsposter - salgsordre] RIGHT JOIN ([Reservationsposter - hovedquery] LEFT JOIN [Reservationsposter - produktionsordre] ON [Reservationsposter - hovedquery].[Entry No_] = [Reservationsposter - produktionsordre].[Entry No_]) ON [Reservationsposter - salgsordre].[Entry No_] = [Reservationsposter - hovedquery].[Entry No_]
GROUP BY [Reservationsposter - hovedquery].[Entry No_], [Reservationsposter - hovedquery].[Item No_], [Reservationsposter - hovedquery].Description, [Reservationsposter - produktionsordre].[Source ID], [Reservationsposter - salgsordre].[Source ID];

I still get duplicate production order values but I think the problem lies in the source Reservation Entry table. When a production order is planned, Microsoft NAV makes way to many records in the reservation entry table to distinctly filter out what I need. I can not point out consistent filter rules to make sure I get the correct uniform result every time.
But thank you all for the big effort to help