Difficult formula help please

[TheStone]

Here's what I'm looking for. All of our reports are based on an incident number. The incident number has 10 numbers in it. For example:

2000301001

2000 = Year
301 = Today's Julian date
001 = first incident of the day, 002 for the next incident of that day and so on.

What I'd like to do is create a formula that would allow our employees a shortcut. Currently, to inquire an Incident number, you have to enter in all 10 numbers. About 95% of all inquires are for the same day. So, what I'd like to do is create a statement stating, if 3 numbers are entered (the last three), then use the Year (2000), the Julian Date (that day's current Julian), and then the three numbers that were just entered by the employee. Now, if the employee enters all 10 numbers, then this statement would be ignored. The hard part is the Julian date part of the formula

Any ideas? Thanks

 

[kenhamady]

To get the Julian date calculate the different between CurrentDate (or any other date field) and The first day of the current year and add one.

Currentdate - Date (Year(Currentdate),1,1) + 1

This will give you the "day of the year". I think you need the plus one on the end to be precise, but you can drop that if needed.

To convert a number to text without commas and decimals use

Totext( {field} , 0 , "&quot

Let me know where else you get stuck Ken Hamady

http://www.kenhamady.com/

 

[ziggs]

Nice! Only one problem so far. When this formula generates the julian date, today would equal 301 and 1-1-2000 would return a value of 1. Based on my incident number, 1 would need to look like 001 instead.

Same problem with 1-11-2000. That value would be 11 and needs to look like 011.

Thanks for the quick response!!!

 

[apelecanus]

You check the value of the returned julian date and if it is < 100 string on the leading zeros couldn't you?

 

[MalcolmW]

ToText({field} , &quot;000&quot
will show leading zeros, for a maximum string length of up to 3 characters. It is also rounded off to the nearest integer.

 

[ziggs]

Thanks MalcomW, that worked out. Now, what I need to do is come up with the proper parameter and I've tried all of my tricks and can't get it to work.

So, I trying to set it up so that

If the {?parameter} is < 999 then use the formula created above and add the {?parameter} entered by the user, else just enter the {?parameter}. In other words, if more than three digits are enter, use the parameter without the formula.

Any ideas would be appreciated, thanks

 

[ziggs]

I just found out part of answer and need one last bit. The following parameter setting will let me enter the last three numbers when the parameter pops up and do what I want:

{INCIDENT_LOC_EVENTS_VW.INCD_EVENT_NUM} = (totext(year(currentdate))+ (totext (currentdate - Date (Year(Currentdate),1,1) + 1) ) + {?event })

Now, all I need to figure out is the if then part in a parameter.

In other words, if value is > than 999 then {INCIDENT_LOC_EVENTS_VW.INCD_EVENT_NUM} = {?parameter} else <<<the long formula above here>>>


For some reason, I can't get it to work. The program just hangs.

 

[kenhamady]

Create a new formula field called &quot;Selection&quot;

if Length ({?parameter}) > 3
then {?parameter} else
..long formula ..


Then write a selection formula that says:

{@Selection} = {INCIDENT_LOC_EVENTS_VW.INCD_EVENT_NUM}


Ken Hamady

http://www.kenhamady.com/

 

[ziggs]

Darn, I almost had it, but need a bit more help. The following lines work fine when I create a Microsoft Excel database and test it, but my production site is in Oracle and there is some type of problem

if Length ({?event}) > 4 then
({?event})
Else
(totext({@YearJulian3}[1 to 7] + totext({?event}) ))

Now, the {@YearJulain3}[1 to 7] is the problem in Oracle, it doesn't like it. I replaced ({@YearJulain3}[1 to 7] with &quot;2000304&quot; and the formula works, thus, I know Oracle doesn't like something here. I've banged my head against the wall several times and can't figure it out. Any ideas?

 

[kenhamady]

Are you getting a specific error message?
(Care to share it with us? )
When does the error or failure occur.
Possibly a subscript error?


YearJulian3 should always be 7 characters so why the substring? BTW, Oracle isn't processing this formula, Crystal is, so it may have to do with the data that is coming back from Oracle. Ken Hamady

http://www.kenhamady.com/

 

[ziggs]

No error message, the program locks up and I have to cntl-alt-delete and end the program. At one time, I waited for 20 minutes just to see if it was just processing. No luck.

The problem occurs when you refresh. The first line of the selection parameter that you suggested for me the other day states if length is > 4 then use all 10 numbers. Even when I use all 10 numbers, the program locks. You would think that it would ignore the rest of the selection parameter since the condition is met in the first line, but it doesn't.

About the [1 to 7], I didn't want to make my example to confusing so that it would be easier to answer, but my selection parameter gives 3 possibilites, enter 10 digits, 4 digits or 3 digits. If you enter 4 digits it takes [1 to 6]. In this example I gave for everyone to look at, I forgot to take out the [1 to 7] . It's not a subscript problem because I've tried almost everything. And again, this does work in MS access but not against the Oracle database. I used copy and paste and triple checked the formulas against what's working in Access and no luck.

So, to clarify...

if Length ({?event}) > 3 then
({?event})
Else
(totext({@YearJulian3} + totext({?event}) ))

locks it up also. I created

If ({event}) > 1 then (totext({@YearJulian3} +totext ({?event})))

as the only part of the selection paramater and still the same problem when I enter the three digits. With testing, I isolated the problem to the (totext({@formula}). I can use any formula and it doesn't like it for some reason.

Thanks for your time!!!!!!!!

 

[kenhamady]

The Totext() function can't get converted to SQL, so this one rule has to be processed at the client level. Check the SQL expression WHERE clause (Database - Show SQL Expression) and see what criteria are being passed to the database.

If this is your only criteria, then the result set returned to the client is going to be your entire Oracle Database, which may choke the server. Try adding another selection criteria that limits the result set to a small sample, and see if that allows the report to run.

The solution may be to replace the ToText() part of the formula with a SQL expression which can be processed by the database. SQL expressions are written just like formulas but use the separate editor which only includes the appropriate SQL functions supported by your particular ODBC driver. Oracle will have different functions than MS Access in this case. Ken Hamady

http://www.kenhamady.com/

 

[ziggs]

Ken, you were right about the result being returned was the whole DB. As a test, I created a new report with just two fields and the whole database returned (Only took 50 seconds, but my original report would probably take a day with all the fields it grabs). Thanks!

So, Here's the problem.

When my Selection formula equals (I shortened it for isolation):

IF Length ({?event}) = 3 then
((&quot;2000&quot+(totext(currentdate - Date (Year(Currentdate),1,1) + 1) + totext({?event})))

The SQL Query shows a Where statement as:
WHERE
&quot;INCIDENT_LOC_EVENTS_VW&quot;.&quot;INCD_EVENT_NUM&quot; = '2000305011'

and everything works properly (But when the julian date is 1 through 99, this won't work).


So the key problem is when I try to change:

(totext(currentdate - Date (Year(Currentdate),1,1) + 1 of the Selection formula

and change it to:

totext{@yearjulian}

When I do this, I lose the Where statement in the SQL Query. I must use a formula because I have to force the Julian date to three numbers (001 instead of 1 for January 1st). As suggested by someone the formula to force this is:

Totext( {@julian} , &quot;000&quot with @julian being:

(currentdate - Date (Year(Currentdate),1,1) + 1)

Now, when I add {@yearjulian} (this is a combination of the year and julian), I get an error message of (The remaining text does not appear to be a part of the formula) if I don't add totext in front of it.

Any ideas on how to get the proper julian in the formula and to have it recognized? At the problem is narrowed down and I thank you a bunch Ken.

 

[ziggs]

THANKS TO ALL OF YOU!!! I finally got it to work with everyone's help. Also, a special thanks to Ken!!!

The problem was the SQL Query and having a formula within a formula. Thus, not showing up on the SQL Query.

For those that are curious, the final parameter looks like this:

if Length ({?event}) > 4 then
({?event})
Else
IF Length ({?event}) = 4 then
(totext(year(currentdate)))+(totext(Totext( currentdate - Date (Year(Currentdate),1,1) + 1 , &quot;000&quot)[1 to 2] + totext({?event}))
Else
(totext(year(currentdate)))+(totext(Totext( currentdate - Date (Year(Currentdate),1,1) + 1 , &quot;000&quot) + totext({?event}))

 

[ziggs]

Oops, one last problem! It works fine if you have Crystal Reports on your machine, but it's not working on our Crystal Reports Web Server run by CR8. Any ideas what's being missed here?

 

[ziggs]

This should be last response. I narrowed down problem to server having the setting of numbers with decimal value of 1.00. My program has 1. So, that should hopefully take care of problem. Thanks again