| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
  
Topic: Number/Text Field Combined
Posted: 02 Nov 2010 at 4:48am |
I am importing data from an Excel spreadsheet which has numbers and letters in a particular column. For some reason, the numbers show up (e.g. 1), but not the letters. How can I resolve this?
Also, is there a way to highlight every other number and/or letter in a silver color?
e.g. 1-highlight, 3-highlight, 5-highlight, 7-highlight.
Thanks for your help. Edited by jgarner - 02 Nov 2010 at 4:50am
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Nov 2010 at 6:59am |
not sure abou the import as I have had problems getting crystal to identify spreadsheet cells as the data type that I specify in the spreadshhet.
For the every other highlight use a formula field in the detail section color tab...
If ({#BGcolor} Mod 2 = 0) Then crSilver Else crNocolor;
you can use the recordnumber instead of the RT I have in this formula (BGcolor) but i prefer using a running total to have better control on group changes or highlighting like rows togther
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 03 Nov 2010 at 4:33am |
After taking a look at the formula that you suggested, I used this:
If ({'2010_2011_'.LD} Mod 2 = 0) Then crSilver Else crNocolor;
This worked well, and highlighted ever other number in the LD field.
However, I then tried to troubleshoot the LD field in the Excel spreadsheet, since I was having trouble with the letters showing up, as I could see only the numbers in the CR. Once I formatted the Excel column as text instead of numbers, both the letters and numbers showed up in the CR report.
Once this was corrected, I am now getting an error message with the BGColor formula:
If ({'2010_2011_'.LD} Mod 2 = 0) Then crSilver Else crNocolor;
Saying a number or currency amount is required here, while it highlights the {'2010_2011_'.LD}.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Nov 2010 at 4:40am |
from crystal help
x Mod y
Divides x by y and returns a remainder that is a whole number. The Number values x and y can be positive or negative, and they can be fractional. If x or y is fractional, it will be first rounded before the modulus is taken.
so X has to be numeric which is why I use a running total to get a value. The RT allows me to reset the value at any group level or to keep groupings of like values in details section highlighted togther by using different types of RTs (e.g. a distinctcount instead of a count).
If you are just using a straight row process you can try
If (RecordNumber Mod 2 = 0) Then crSilver Else crNocolor;
Otherwise create a Running Total (or variable formula if you prefer those) and insert that into the formula.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Nov 2010 at 4:43am |
also I personally do not like the silver color as the background as it is too dark. YOu can use the color() function to choose a wider range of clors.
i prefer using a much lighter background as indicated below.
If (RecordNumber Mod 2 = 0) Then Color (236,242,242) Else crNocolor; Edited by DBlank - 03 Nov 2010 at 4:44am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 03 Nov 2010 at 4:53am |
I also prefer the lighter color, and I did see your first reply regarding the 'RecordNumber'.
The problem I have with that is although it does work by highlighting every other record, I was hoping to have the same highlighted color on all # 1,3,5,7, etc.
As it is with the RecordNumber, the first #1 is highlighted, the second #1 is not, the third #1 is highlighted, etc.
I'm wondering if I should change the Excel column back to numbers instead of text and use the original forumla of:
If ({'2010_2011_'.LD} Mod 2 = 0) Then crSilver Else crNocolor;
Which worked well. I would then probable have to remove the letters in the number field of the excel spreadsheet.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 03 Nov 2010 at 4:55am |
Another question is when adding the formula, do I keep the rest of the default formula in place:
// This conditional formatting formula must return one of the following Color Constants:
//
// Color (red, green, blue)
// crBlack
// crMaroon
// crGreen
// crOlive
// crNavy
// crPurple
// crTeal
// crSilver
// crRed
// crLime
// crYellow
// crBlue
// crFuchsia
// crAqua
// crWhite
// crNoColor
//
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Nov 2010 at 5:01am |
This is exactly why I use the Running Totals
right click on Running TOtal and select New
Name= BGcolor... (Back Ground Color)
Field to summarize={'2010_2011_'.LD}
Type of summary=DiscinctCount
Evaluate = for each record
reset= never (if you do not use any groups)
now use BGcolor in your formula
If ({#BGcolor} Mod 2 = 0) Then Color (236,242,242) Else crNocolor;
the "default formula" is just a notation to you to let you know what values can be used.
any time you see the
//
at the start of a line it is a notation in a formula and has not impact at all. It turns the line green to show you it is just a note
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 03 Nov 2010 at 5:19am |
Thanks for the CR lesson. I'm catching on but am not able to use CR at work as much as I'd like to so that I can become more familiar with it. Using the 'Running Totals' as you suggested worked perfect.
Thanks for your help.
|
| IP Logged |
|
|
|
