Another lookup failure

[Oyseka]

Hi, I have table with packing list data that looks up the transport cost from the transport table. The relationship from the packing list to the transport costs are:

Transporter

Transport Centre

Packaging Type

The first matching record in the Transport costs table shows 1.93

but the transport cost brought in to the packing list shows 1.86

There is a 1.86 shown in the transport costs but that is the third matching record not the first. How am I managing to knack this

The screenshots show

1). the packing list with the looked up transport cost(Tr_cost)

2) the transport cost list

Thanks in advance

Chris

[bcooney]

It looks to me that using only 3 predicates in the relationship is not enough to return just one matching record. It's returning the first record it matches (probably the first one created in the table unless the relationship is sorted (current sort order is not affecting which record is returned).

[Oyseka]

There is a sort order in the relationship which is

Company

Tr.Centre

Timestamp

[bcooney]

Why do you need Company and Tr. Center? You're matching on Company and Tr. Center in the relationship, right? Can you show a png with the timestamp field?

And why do you have multiple rates for the same Company, Tr. Center, Packaging Type anyway?

[Oyseka]

The Transport PNG shows the timestamp at the far right.

The reason for having different rates is that the rates change with incredible rapidity, just about every time the fuel price changes, so with each new transport rate given by the transport company a new price is given to a pallet price. The old prices are kept for analysis over time

[bcooney]

I don't see a timestamp, I see a date. The relationship should sort by timestamp desc (and I assume that this is a record creation timestamp).

Edited December 2, 2008 by Guest

[Oyseka]

It is a Timestamp field with date and time and its a modification timestamp with the field sorted descending

[Oyseka]

WHOOOA, hold that thought, I had the sort order ascending, Thank you very much for your help, that resolves it

Chris