cut up a string that is in a calculated field on a report

[kingcarol]

I have a calculated field on a report that displays a text string that shows all of the developer's sql query stuff AND all of the filters created by the user as they search a grid (thanks to Selwyn). Please see the screenshot attached.

The filters created by the developer all seem to be within the first set of parentheses. I would like to have a second calculated field on the report that strips off the stuff in the first set of parenthesis, including the parenthesis. This is so the user will only see the search filter they applied. They don't need to see the stuff from the sql query for the report that the developer did (makes a big mess). BTW, on the report, there is another calculated field under the first one that is showing what the report is ordered by... the AP_VENDOR_CODE field... ignore that.

I know I need to use some embedded string functions that find the index position of the first closing parenthesis and then one that slices it and everything before it. But after much searching in Alpha help, etc., I just can't find what I need.

After being to W3 Schools page, I would think it would be something like this:

var str = I'm being brain dead... field name that has the text is sqlfilter... hmmm, how do I refer to a field on a report in Alpha?

str.slice(0,str.IndexOf(")"))

Any assistance will be appreciated.

[Garry Flanigan]

Re: cut up a string that is in a calculated field on a report

Given how complicated the string is, maybe best to go to the wiki and see "stringscanner". It is very powerful but also see other "character comparison functions" that are generally easier to learn.

[Peter.Greulich]

Re: cut up a string that is in a calculated field on a report

In addition to what Garry said, take a look at extract_string() and substr() and the character extraction functions.

[kingcarol]

Re: cut up a string that is in a calculated field on a report

Thank you, Garry and Peter. You pointed me in the right direction and I've had wonderful luck with:

atc() to find position of the first )parenthesis
substr() to remove string before that position
STRITRAN() to replace words in the remaining string

[kingcarol]

Re: cut up a string that is in a calculated field on a report

FYI, Here is the xy code for that calculated filter display field in the report header and it works perfectly. If the user applies search filters in the grid that calls a report like the one described by Selwyn in video 38 (http://news.alphasoftware.com/V10Preview/videos.htm) that reacts to those search filters, then the a5_getReportSQLFilter will capture both the developer and user filters. The developer sql filters are at the first of the string and are enclosed in parenthesis. Following the end parenthesis of the developer filters is .and. So the code below finds the position of ").and" and cuts it and everything before it out of the string to produce the sqlfilterUser calculated field. And then the rest of the code slices up the string based on other stuff to clean up in the user filter string. That would change for every report. But the first 3 lines below might work on every report. more testing to do...

See screenshot of the report now and compare to the one I posted when I started the thread. BTW, I should mention that I am using Query By Example (qbe) for the user to filter the rows. Gets rid of having "like" in the filter string and uses simple operand like = or >=, which looks better to user.

This code is done in the xy box for calculated fields that are used in the report header.

sqlfilter = a5_getReportSQLFilter(.t.)
sqlfilterByUserTest = atc(").and.",calc->sqlfilter)
sqlfilterUser = If(calc->sqlfilterByUserTest=0,"None.",substr(calc->sqlfilter,atc(").and.",calc->sqlfilter,1)+1))
inlist = ".and. TblGLAllEntries.,TblGLAllEntries.,.and, 12:00:00 00 am,AP_VENDOR_CODE,JOB_CODE,GL_DATE,GL_DOCUMENT,GL_DESCRIPTION,GL_MAIN_ACCT_NUM,DEPT_ID,JOB_SUB_CODE,PAID_STATUS,PAID_DATE,PAID_CHECK_NUM,PAID_CASH_ACCT,PO_NUM,. Round((QUANTITY * AMOUNT),2)"
outlist = "|,|,"","",Vendor,Job,GL Date,Inv Num,Description,GL Num,Dept,Cost Code,Status,Paid Date,Check Num,Cash Acct,PO Num,|Amount,:"
inlistrows = comma_to_crlf(calc->inlist)
outlistrows = comma_to_crlf(calc->outlist)
sqlfilterresult = stritran_multi(calc->sqlfilterUser,calc->inlistrows,calc->outlistrows)
sqlorder = a5_getReportSQLOrder()
sqlorderresult = stritran_multi(calc->sqlorder,calc->inlistrows,calc->outlistrows)

[kingcarol]

Re: cut up a string that is in a calculated field on a report

UPDATE: Apparently something has been changed in the function a5_getReportSQLFilter. Selwyn says he hasn't changed anything, but for some reason, I no longer have to remove the search (or filter) criteria done by the developer from the resulting string, and so now can remove 2 lines of the XY code for the report as shown below.

sqlfilter = a5_getReportSQLFilter(.t.)
inlist = ".and. TblGLAllEntries.,TblGLAllEntries.,.and, 12:00:00 00 am, 11:59:59 00 pm,AP_VENDOR_CODE,JOB_CODE,GL_DATE,GL_DOCUMENT,GL_DESCRIPTION,GL_MAIN_ACCT_NUM,DEPT_ID,JOB_SUB_CODE,PAID_STATUS,PAID_DATE,PAID_CHECK_NUM,PAID_CASH_ACCT,PO_NUM,. Round((QUANTITY * AMOUNT),2)"
outlist = "|,|,"","","",Vendor,Job,GL Date,Inv Num,Description,GL Num,Dept,Cost Code,Status,Paid Date,Check Num,Cash Acct,PO Num,|Amount,:"
inlistrows = comma_to_crlf(calc->inlist)
outlistrows = comma_to_crlf(calc->outlist)
sqlfilterresult = stritran_multi(calc->sqlfilter,calc->inlistrows,calc->outlistrows)
sqlorder = a5_getReportSQLOrder()
sqlorderresult = stritran_multi(calc->sqlorder,calc->inlistrows,calc->outlistrows)