Here's the background info:

My main report references data from 4 tables that are all linked.  I need to pull in data from a 5th table, but there are no fields that I can directly link to.  One of the tables on the main report does contain at text field which sometimes contains one or more IDs that do relate to the 5th table.  I would like to extract all reference ids from this text field, but for simplicity, I am only extracting the first one until I get the sub report to work.

 

I created a formula in the main report that will extract the first refence id into a stringvar (ID is always a number, but is stored as a 7 character string in the 5th table).

 

I created a sub report that uses the reference ID extracted by my formula and is linked to the field in the 5th table that should be the same ID.  When I run my report, the sub report is always blank.  I suspect that it has to do with the IDs not matching up, but have tried everything I can think of.

 

Here are my formulas:

 

Select formula from Main Report:

{MESSAGE.TRN_DATE} >= DateTime (2009, 07, 2, 00, 00, 00) and
{MESSAGE.STOP_INTERCEPT} in ["O", "S"] and
(({MESSAGE_HIST.QUE_LINE_ID} in ["STOP_ADM_LOG", "STOP_PAY_LOG"]) or
({MESSAGE_HIST.QUE_LINE_ID} = "*SYS_MEMO" and
(InStr ({MESSAGE_HIST.DETAILS}, "FLD")>0 or InStr ({MESSAGE_HIST.DETAILS}, "MATCH REF:")>0)))

 

Formulat to extract reference ID on Main Report:

Local NumberVar SI := 0;
Local NumberVar LPI := 0;
Local NumberVar SISP := 1;
Local NumberVar LPISP := 1;
Local NumberVar OFAC_ID_Len := 0;
Local NumberVar Counter := 1;
Local StringVar OFAC_ID := "0";
Local StringVar OFAC_IDs := "";

if ({MESSAGE_HIST.QUE_LINE_ID} = "*SYS_MEMO" and
   InStr ({MESSAGE_HIST.DETAILS}, "MATCH REF:")>0) then
(
   SI := InStr (SISP, {MESSAGE_HIST.DETAILS}, "/");
   if SI > 0 then
   (
      LPI := InStr (LPISP,{MESSAGE_HIST.DETAILS}, "(");
      LPISP := LPI + 1;
      OFAC_ID_Len := LPI - 1 - SI;
      OFAC_ID := Mid ({MESSAGE_HIST.DETAILS}, SI+1, OFAC_ID_Len);
      while OFAC_ID_Len < 7 do
      (
         OFAC_ID := "0" + OFAC_ID;
         OFAC_ID_Len := Length (OFAC_ID);
      );

      OFAC_IDs := OFAC_IDs + OFAC_ID;
   );
   OFAC_IDs
)

 

Select statement for linked subreport:

Val ({STOP.STOP_NUM}) = Val ({?Pm-@OFAC ID})

 

 

Any assistance would be greatly appreciated!!!