I have a report that has a field that is made up of 2 letter codes. I also have a CSV file that breaks these codes out into descriptions. For example, LHDZ would mean "Left Hand (LH)", "Double reinforced (DZ)". So, I want to break these fields into their two letter components, match them to their descriptions, and then print out the descriptions. There are about 300 of these things. Initially I had envisioned a monstrous formula that would use a gigantic case statement that had all 300 possibilities and played "Is this it? no. Is this it? No..." and extract the descriptions, but I had this file so I am trying to see how I can incorporate this into the formula. The table is added to the report, but not linked to anything. Is it possible to extract the description field from within a formula on an unlinked table?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Is it possible to link to a table with a formula?
- Thread starter barc0001
- Start date
basil3legs
Programmer
Whenever I have had to do this, I have had to create a subreport. In this case you would need 2 linked by the first 2 letters of the code and then the second 2. If the lookup table is not too big, this shouldn't be too slow.
If you remove the borders, make the text the same etc. this appears fine on the report. I also usually set this section of the report to "Underlay Following Sections" and create a similar sized section with a blank text field overlaying the subreport to prevent people trying to drill-down on it (If there is a better way of doing this please let us know!).
If you could put the lookup list and the original table in an SQL database, this would be much easier as you could do the lookup through an SQL statement.
If you remove the borders, make the text the same etc. this appears fine on the report. I also usually set this section of the report to "Underlay Following Sections" and create a similar sized section with a blank text field overlaying the subreport to prevent people trying to drill-down on it (If there is a better way of doing this please let us know!).
If you could put the lookup list and the original table in an SQL database, this would be much easier as you could do the lookup through an SQL statement.
How many different 2 letter codes are there? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #4
If there are 340 different 2-letter codes, and any one can be front or back, that would be quite a case statement. Close to 100K combinations.
You don't mean that there are 25-30 2-letter codes that can be assembled into 340 combinations?
If you have 340 codes in a table, with 340 descriptions you would need to split your 4-digit code into 2 parts and do 2 linked subreports to get the 2 descriptions. This would be very slow.
Or, you could create a master table with all the 4-digit combinations, and there descriptions. This could be linked to the field normally, and would be faster, but would involve the extra step of creating the table.
To create the master table you could create a report with 2 instances of your CSV, link the list to itself using a 'not equal to' join, and then export all the combinations to anohter file. Include formulas that appened the 2 codes, and 2 descriptions together. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
You don't mean that there are 25-30 2-letter codes that can be assembled into 340 combinations?
If you have 340 codes in a table, with 340 descriptions you would need to split your 4-digit code into 2 parts and do 2 linked subreports to get the 2 descriptions. This would be very slow.
Or, you could create a master table with all the 4-digit combinations, and there descriptions. This could be linked to the field normally, and would be faster, but would involve the extra step of creating the table.
To create the master table you could create a report with 2 instances of your CSV, link the list to itself using a 'not equal to' join, and then export all the combinations to anohter file. Include formulas that appened the 2 codes, and 2 descriptions together. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #6
No, there are just 340 matches, ergo the case statement will be 340 cases long. There are no combinations to be had, basically I have a table with 2 columns, the 2 letter code, and the description.
Since they can appear in any combination, what I am instead doing is taking the combined codes apart into the 2 letter codes, storing those in variables, and then was planning on running the case statement against each individual variable.
This way, it is "only" 340 items in the case, not one item for each possible combination of 340x340, but I have to figure a way of looping through the case statement, or having it multiple times in the formula.
I'm not that experienced with all the nuances of Crystal, but there's no way to create a subroutine inside a formula, is there? If there is, I could create the case statement as a subroutine and then just break the code down into one or more variables, and send each variable to the subroutine one at a time.
As a further complication, there are now some codes I am discovering in the data that have up to 4 two letter codes in them, so the table approach might not be feasible either, since it would have to be 340 ^ 4 entries long....
Since they can appear in any combination, what I am instead doing is taking the combined codes apart into the 2 letter codes, storing those in variables, and then was planning on running the case statement against each individual variable.
This way, it is "only" 340 items in the case, not one item for each possible combination of 340x340, but I have to figure a way of looping through the case statement, or having it multiple times in the formula.
I'm not that experienced with all the nuances of Crystal, but there's no way to create a subroutine inside a formula, is there? If there is, I could create the case statement as a subroutine and then just break the code down into one or more variables, and send each variable to the subroutine one at a time.
As a further complication, there are now some codes I am discovering in the data that have up to 4 two letter codes in them, so the table approach might not be feasible either, since it would have to be 340 ^ 4 entries long....
A really easy way, if blinding speed isn't an issue, would be to throw the 340 into an Access database file, or equivalent, and include that table in your linking. Then just use the "IF...THEN" structure to retrieve the description. Obviously this isn't a solution for a high-volume environment.
So the field can have a variable number of 2-character codes? That makes it messy.
Why not several formulas like this:
StringVar Pos56:= {field} [ 5 to 6 ]
If length ({field}) < 5 then "" else
..do your long case here..
You would use 4 formulas, one for each pair position.
I think this would be faster than a subreports for each position but it would be interesting to benchmark it. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Why not several formulas like this:
StringVar Pos56:= {field} [ 5 to 6 ]
If length ({field}) < 5 then "" else
..do your long case here..
You would use 4 formulas, one for each pair position.
I think this would be faster than a subreports for each position but it would be interesting to benchmark it. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question