Hello! I would like to first say I am pretty new to FocalPoint and this is my first post . I am a pretty novice user of App Studio, as my organization converted to IBI last year. I am only starting to understand a little of the code.

I work at an financial institution and I am looking at transaction data. I have one report that takes several minutes when looking at 30 days of transaction. I believe the report is taking such a long time because I have a DEFINE converting TRANS_NARR_1 to uppercase. The report gives the end user the functionality to search for a merchant name which is located in TRANS_NARR_1.

I have to convert TRANS_NARR_1 to uppercase because there are some instance where the data comes in as both lower, upper, or mixed case (Amazon, amazon, AMAZON). Without the DEFINE it will only return the results depended on how the end user enters the name into the prompt.

I tried using the SET command to format TRANS_NARR_1 with no luck. I am not sure if this was user error or if SET was the wrong direction. I would be grateful for any suggestions or help in how I can faster response times.

Here is the code:

DEFINE FILE A_TRANS_DLY
UPPER_TRANS_NARR_1/A256=UPCASE(256, A_TRANS_DLY.TRANSACTION_DAILY.TRANS_NARR_1, 'A256');
END

TABLE FILE A_TRANS_DLY
BY LOWEST A_TRANS_DLY.TRANSACTION_DAILY.TRANS_NARR_1 NOPRINT
BY A_TRANS_DLY.TRANSACTION_DAILY.UPPER_TRANS_NARR_1
BY A_TRANS_DLY.TRANSACTION_DAILY.TRANS_AMT
BY A_TRANS_DLY.DATE_POSTED.DTE
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ST
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ZIP_CD
BY A_TRANS_DLY.THIRD_PARTY.MC_CD
BY A_TRANS_DLY.TRANSACTION_DAILY.RE_NUM
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ID
BY A_TRANS_DLY.ACCOUNT.ACCT_NUM
BY A_TRANS_DLY.PASSPORT_THIRD.PASSPORT_PROG_START_DTE
BY A_TRANS_DLY.PASSPORT_THIRD.PASSPORT_PROG_END_DTE
WHERE ( A_TRANS_DLY.DATE_POSTED.DTE GE '&Start_Date_MMDDYYYY' ) AND ( A_TRANS_DLY.DATE_POSTED.DTE LE '&End_Date_MMDDYYYY' ) AND ( A_TRANS_DLY.TRANSACTION_TYPE.TRANS_SRC EQ 'TMG' ) AND ( A_TRANS_DLY.TRANSACTION_DAILY.RE_NUM OMITS 'XFR' ) AND (( A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ID CONTAINS '&USER_TEXT.UPPER CASE Merchant Name or Merchant ID.' ) OR ( A_TRANS_DLY.TRANSACTION_DAILY.UPPER_TRANS_NARR_1 CONTAINS '&USER_TEXT.EVAL' ));
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END



Thanks,
Jessica

•Product Release: 8.1
•Service Pack: 0.5
•Version: branch8105m
•Application Server: Run with Apache Tomcat/8.0.21

This message has been edited. Last edited by: FP Mod Chuck,

Hi Jessica and welcome,

quote:

A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ID CONTAINS '&USER_TEXT.UPPER CASE Merchant Name or Merchant ID.'

First, I don't know where the above is coming from since it's not a valid WF syntax.

Second, if possible and since your query do always include/exclude according to below filter, try to create an HOLD file (or another table that can be created once a day) where only records that meet below criteria will be included. That way you will then have less records to filter against the CONTAINS and DATE filter. In the same time, perform the DEFINE. So when performing the filter (CONTAINS) it will be done against the read data and not from a DEFINEd field.

-* To be performed once a day if possible
-* --------------------------------------
SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE A_TRANS_DLY
UPPER_TRANS_NARR_1/A256=UPCASE(256, A_TRANS_DLY.TRANSACTION_DAILY.TRANS_NARR_1, 'A256');
END

TABLE FILE A_TRANS_DLY
BY A_TRANS_DLY.TRANSACTION_DAILY.UPPER_TRANS_NARR_1
BY A_TRANS_DLY.TRANSACTION_DAILY.TRANS_AMT
BY A_TRANS_DLY.DATE_POSTED.DTE
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ST
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ZIP_CD
BY A_TRANS_DLY.THIRD_PARTY.MC_CD
BY A_TRANS_DLY.TRANSACTION_DAILY.RE_NUM
BY A_TRANS_DLY.CARD_ACCEPTOR.THIRD_PARTY_ID
BY A_TRANS_DLY.ACCOUNT.ACCT_NUM
BY A_TRANS_DLY.PASSPORT_THIRD.PASSPORT_PROG_START_DTE
BY A_TRANS_DLY.PASSPORT_THIRD.PASSPORT_PROG_END_DTE
WHERE A_TRANS_DLY.TRANSACTION_TYPE.TRANS_SRC EQ 'TMG';
WHERE A_TRANS_DLY.TRANSACTION_DAILY.RE_NUM OMITS 'XFR';
ON TABLE HOLD AS TEMPTBL
END
-RUN

Third, you will then be able to let the user perform against the HOLD file such as this:

-SET &USER_TEXT_UP = UPCASE (256, &USER_TEXT, 'A256');

TABLE FILE TEMPTBL
BY LOWEST UPPER_TRANS_NARR_1
BY TRANS_AMT
BY DTE
BY THIRD_PARTY_ST
BY THIRD_PARTY_ZIP_CD
BY THIRD_PARTY.MC_CD
BY TRANSACTION_DAILY.RE_NUM
BY THIRD_PARTY_ID
BY ACCT_NUM
BY PASSPORT_PROG_START_DTE
BY PASSPORT_PROG_END_DTE
WHERE DTE GE '&Start_Date_MMDDYYYY';
WHERE DTE LE '&End_Date_MMDDYYYY';
WHERE THIRD_PARTY_ID CONTAINS '&USER_TEXT_UP.EVAL' OR UPPER_TRANS_NARR_1 CONTAINS '&USER_TEXT_UP.EVAL';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END