×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

formula size

formula size

formula size

(OP)
  There r too many conditions to be checked to filter the
records from the database. When i tried to add some more conditions in record selection formula it gives an error message as formula too long.

RE: formula size

I have never seen this, so I would like to know how many characters you have in your selection formula?    Could you post it here? It must be a monster.

You can take part of the formula, put it into a separate formula field or SQL Expressiion field (which will be a boolean), and then simply use this field as part of the selection formula.  This makes the selection formula shorter.   I would start with the parts of the formula that use functions.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: formula size

Ken,
I have run into this before when trying to get a record selection formula to pass the SQL to the database, I discovered there were limitations oddly enough on using more than one formula that referenced other formulas in the record selection. (If I did this It wouldn't pass the SQL.) So I tried simply copying the formula code into the record selection multiple times. This worked fine and passed the SQL great until I got the same error that Sapnay mentioned. I didn't get a total count of charactors but also discovered that if you eliminate all spaces (like at the end of the lines of code) you get a lot more formula for your money.

BTW: Printed out with default wordwrapping (no spaces at the end of the lines) it was over 11 pages long. I wasn't that interested in Counting. Wish I had now though for the trivy.

Paul Ellsworth
Sr. Crystal Reports Developer/Trainer
Pleasanton, Ca

RE: formula size

That is why I suggested pulling out anything with function, because that won't go to SQL anyway.  I would like to see an 11 page selection formula.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: formula size

I used to think that functions couldn't be used to Pass SQL because thats what I've allways been told by Seagate(I haven't asked Crystal Decisions Yet) here is one of the formulas that I use for record selection against a SQL Server Datebase that passes SQL 100% and you'll notice there are several of those functions we've all been told won't but they do.

Paul Ellsworth

if IsDatetime({?EndDate}) then
    DatetimeValue({?EndDate})+1
else
        if uppercase({?EndDate}) = 'TODAY' then (currentdate+1) else
        if uppercase({?EndDate}) = 'YESTERDAY' then (currentdate+1) - 1 else
        if uppercase({?EndDate}) = 'TOMORROW' then (currentdate+1) + 1 else
        if uppercase({?EndDate}) = 'FIRSTOFMONTH' then minimum(MonthToDate)+1 else
        if uppercase({?EndDate}) = 'ENDOFMONTH' then DateAdd ("m", 1,minimum(MonthToDate)) else
        if uppercase({?EndDate}) = 'FIRSTOFLASTMONTH' then minimum(LastFullMonth)+1 else
        if uppercase({?EndDate}) = 'ENDOFLASTMONTH' then maximum(LastFullMonth)+1 else
        if uppercase({?EndDate}) = 'FIRSTOFYEAR' then minimum(YearToDate)+1 else
        if uppercase({?EndDate}) = 'ENDOFYEAR' then DateAdd ("yyyy", 1,minimum(YearToDate)) else
        if uppercase({?EndDate}) = 'FIRSTOFLASTYEAR' then minimum(LastYearYTD)+1 else
        if uppercase({?EndDate}) = 'ENDOFLASTYEAR' then minimum(YearToDate) else                                                                                
        if uppercase({?EndDate}) = 'SUNDAY' then
            if DayOfWeek(currentdate+1) = 1 then (minimum(WeekToDateFromSun)-6) else minimum(WeekToDateFromSun)+1 else
        if uppercase({?EndDate}) = 'MONDAY' then
            if DayOfWeek(currentdate+1) <=   2 then (minimum(WeekToDateFromSun)-5) else minimum(WeekToDateFromSun)+ 2 else
        if uppercase({?EndDate}) = 'TUESDAY' then
            if DayOfWeek(currentdate+1) <=   3 then (minimum(WeekToDateFromSun)-4) else minimum(WeekToDateFromSun)+ 3 else
        if uppercase({?EndDate}) = 'WEDNESDAY' then
            if DayOfWeek(currentdate+1) <=   4 then (minimum(WeekToDateFromSun)-3) else minimum(WeekToDateFromSun)+ 4 else
        if uppercase({?EndDate}) = 'THURSDAY' then
            if DayOfWeek(currentdate+1)<=   5 then (minimum(WeekToDateFromSun)-2) else minimum(WeekToDateFromSun)+ 5 else
        if uppercase({?EndDate}) = 'FRIDAY' then
            if DayOfWeek(currentdate+1)<=  6 then (minimum(WeekToDateFromSun)-1) else minimum(WeekToDateFromSun)+ 6 else
        if uppercase({?EndDate}) = 'SATURDAY' then
            if DayOfWeek(currentdate+1)<=   7 then (minimum(WeekToDateFromSun)) else minimum(WeekToDateFromSun)+ 7 else
         if uppercase(left({?EndDate},5)) = 'TODAY' then
                if length({?EndDate})  > 6 and NumericText(right({?EndDate},length({?EndDate})-6)) then
                    if mid({?EndDate},6,1) = '+' then
                       (currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-6))
                    else
                        if mid({?EndDate},6,1) ='-' then
                            (currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-6))
                        else
                            (currentdate+1)
                else
                    (currentdate+1)
             else
            if uppercase(left({?EndDate},8)) = 'TOMORROW' then
                if length({?EndDate})  > 9 and NumericText(right({?EndDate},length({?EndDate})-9)) then
                    if mid({?EndDate},9,1) = '+' then
                        (currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-9)) + 1
                    else
                        if mid({?EndDate},9,1) ='-' then
                            (currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-9)) + 1
                        else
                            (currentdate+1)
                else
                    (currentdate+1)
             else
            if uppercase(left({?EndDate},9)) = 'YESTERDAY' then
                if length({?EndDate})  > 10 and NumericText(right({?EndDate},length({?EndDate})-10)) then
                    if mid({?EndDate},10,1) = '+' then
                        (currentdate+1) + tonumber(right({?EndDate},length({?EndDate})-10)) - 1
                    else
                        if mid({?EndDate},10,1) ='-' then
                           (currentdate+1) - tonumber(right({?EndDate},length({?EndDate})-10)) - 1
                        else
                           (currentdate+1)
                else
                    (currentdate+1)
             else
                 (currentdate+1)
      



RE: formula size

I guess to be more precise I would have to say any functions applied to data fields.  You aren't applying the functions to any data fields.  

You are using the functions simply against your own input parameters and system function values.  Since this can all happen without the database even being involved, (ie "before reading records") you aren't affected.

However, it is still true that if there is a rule within the selection formula that uses a function to process a field, this rule would not convert to SQL  but would be processed at the client.   

The only exception I have seen in some cases is the IsNull function, which I have been surprised to see sneak into the SQL statement.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: formula size

(OP)
  I found in a document that the limitation of the formula size is 64KB. My formula size is already 54 KB and if i add some more code to it, it gives that error message. Yes we can add add functions to the input paramters but not with the database fields. Is there any solution for this.

RE: formula size

There is no way to raise the limit. The only answer is to create a formula for some of the  criteria code, and then use the formula field in the selection formula instead of the  code itself.

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close