"Best Fit" Selection 1

[MikeCopeland]

Using CR XI...
I need to do something with a multi-table database that isn't possible via SQL logic, and I'm wondering if it's possible at all. The requirement is to select the "best fit" of several records in a table and use that to acquire data. Here's an example:

Table1 Table2
---------------------------------------------
ID: 12345 L1: 12345 D1: 02/04/2008 etc.
ID: 34567 L1: 12345 D2: 03/22/2008 "
L1: 12345 D3: 04/17/2008 "
L1: 12345 D4: 05/07/2008 "
L2: 34567 D2: 03/27/2008 "

Here I want to find/link via the record in Table2 that is _closest_ to a date parameter I'm using. In this case, the date is 3/15/2008, so I want to find L1.D2 and L2.D1 (there is only 1 Table2.L2 record).
My "guess" is that I might use a Group formula (am I close?), but I don't know how to do such a thing in CR. Any thoughts? TIA

 

[lbass]

Link the ID to the L field, insert a group on {table2.L} and then create a formula to place in the detail section {@diff}:

abs(datediff("d",{?date},{table2.D}))

Then go to report->selection formula->GROUP and enter:

{@diff} = minimum({@diff},{table2.L})

-LB

 

[MikeCopeland]

Thank you, LB. Now it develops that "Best Fit" isn't quite right - I must find the record link that's _less_than_and_closest_to_ the date parameter. Thus, the "abs" clause might yield a link that's _greater_than_ the parameter date but also "closer" to the date I want to use. Looking at your function and Group usage, I'm unsure how to modify (other than removing the "abs" clause i.e., does "minimum" get confused with negative values?). TIA

 

[lbass]

Add a record selection formula (report->selection formula->RECORD (leave the group selection as is):

{table.date} <= {?Date}

Then change the formula to:

datediff("d",{table2.D},{?date})

-LB

 

[MikeCopeland]

LB, I tried your fix, and I'm getting an error that I don't understand: "There must be a group that matches this field". Here is my formula:
// @ bestDate
datediff("d",{?StartDate}, {ClientPacket.Packet_Date})

and here's the code I'm using in the Group:
{@bestDate} = minimum({@bestDate}, {ClientPacket.Packet_Date})

The error highlights everything starting with "minimum", thought the end of the statement. I added a Group on the ClientPacket.Packet_Date, so I don't know what to do to fix the error I'm getting. TIA

 

[lbass]

Your group field belongs in the formula like this:

{@diff} = minimum({@diff},{table.groupfield})

Your group should NOT be on the datefield--it should be on {table2.L}, your ID field in the second table.

-LB

 

[MikeCopeland]

Ahhh, yes, I see. Made the change, and it works. Thanks again!
BTW, my responses are slow because this work I'm doing is a volunteer effort at which I spend only a few hours a day - so by the time I get your answer it's the next day (or the next week)... 8<{{