extract or split a field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19053
Printed Date: 05 May 2024 at 4:10pm
Topic: extract or split a field
Posted By: Laguna
Subject: extract or split a field
Date Posted: 06 Feb 2013 at 10:46am
|
Hello, I have a SQL command concatenating two fields to be displayed as... {field1} - {field2}. For example materialname - materialtype. The length of both fields vary. I need to extract out field1 and use it in my select statement to filter data. Likewise with field2.
How do I pull these fields out?
Thanks
|
Replies:
Posted By: DBlank
Date Posted: 06 Feb 2013 at 10:57am
| why not use a like or instr() in your select statment instead. |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:05am
|
The report parameters are dynamic, and our policy here is not to use 'like' in our code. The admins would reject the report due to strain on the database.
I'm new, i'll look into using instr()
|
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:10am
| I use InStr()... InStr ({?PSampleType}, " - ") and I get an error saying A string is required here {?PSampleType} where my parameter is. |
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:13am
|
how bout left({fullname},instr({fullname}," - ")-1)
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:15am
| InStr() will return an integer. Use that integer to find the length of the material name* |
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:18am
| Can you give us some example data? what is the {?PSampleType} prompting? a drop down, a field the user types in? |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:18am
|
I hope I understand what you're telling me. If I state... left({?PSampleType},instr({?PSampleType}," - ")-1)
I still get an error stating a string is required instr({?PSampleType} here
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:22am
|
{?PSampleType} is a prompt from the user correct? are you wanting someone to be able to search for "Wood - Plywood" by typing "Wood" or are you wanting to generate a drop down that has "Wood" and "steel" from a table that has "Wood - Plywood" and "Steel - Stainless" as records?
|
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:32am
|
{?PSampleType} is a dynamic parameter. The list of values it returns is created by concatenating materialname with materialtype. These sample types are displayed in the list as
Wood - Plywood
Steel - Stainless.
Now I need to define the selected materialnames to show in my report, like Wood and Steel, but not Plastic. As well as materialtypes.
more...
so my parameter {?PSampleType} has Wood - Plywood for a certain record. Now I need to pull out Wood and Plywood separately from {?PSampleType} so I can restricted certain materialnames and materialtypes to show.
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:40am
| left(totext({?PSampleType}),instr(totext({?PSampleType})," - ")-1) |
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:44am
|
Curious when you are creating the new Parameter what does it say the "type" is between the Name and Dynamic fields at the top?
http://imageshack.us/photo/my-images/33/parametersc.png/"> 
|
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:48am
|
It says String |
Posted By: DBlank
Date Posted: 06 Feb 2013 at 11:49am
|
what is sample data you are comparing the paramter to?
Is you paramter se to allow fo rmultiple selections?
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 11:53am
|
Originally posted by DBlank what is sample data you are comparing the paramter to? Is you paramter se to allow fo rmultiple selections? 
I Fail.  lol, Didn't think of it being an array.
|
Posted By: Laguna
Date Posted: 06 Feb 2013 at 11:53am
|
I haven't compared the sample data yet. I need to separate materialnames and materialtypes from {?PSampleType} so I can do the comparison.
Yes the parameter is set to allow for multiple selections.
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:00pm
|
then it is creating an array, essentially
{?PSampleType} = ["Wood - Plywood","Steel - Stainless"] meaning if you want to find all records where (material type = wood and name = plywood) or (material type = steel and name = stainless" then you are going to have to break the array into sub parts. that would be very heavy on the database, the other option is if you are only looking for Wood - Plywood and not any kind of wood, then you could just splice the strings together just as the sql does for the list i.e. local stringvar FullName := {table.materialtype} & " - " & {table.materialname}; fullname in {?PSampleType} |
Posted By: DBlank
Date Posted: 06 Feb 2013 at 12:04pm
|
I am not sure how to split something that is already an array ans use it in the select statement and you cannot use LIKE or instr() on it. Are you stuck with the concantenated field or can you create two different params to choose the fields as seperate options? or can you just create one static list that allows you to select each type seperately? |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 12:04pm
|
I'm still new to crystal so please pardon my inexperience... this looks like it is headed in the right direction...
local stringvar FullName := {table.materialtype} & " - " & {table.materialname};
fullname in {?PSampleType} Would this go into a formula? Or directly in the select statement?
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:06pm
| select statement |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 12:08pm
|
We'll probably have to create two different parameters for this. We did this concatenation to make it easier for users, but now it's difficult for us to figure out how to split.
We had static lists but need to go to dynamic lists.
Thanks for your time! Much appreciated!
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:19pm
|
Originally posted by DBlank
I am not sure how to split something that is already an array ans use it in the select statement and you cannot use LIKE or instr() on it. Are you stuck with the concantenated field or can you create two different params to choose the fields as seperate options? or can you just create one static list that allows you to select each type seperately? Simply food for thought DBlank, what about something like this used in a formula, then have the select formula match this. local numbervar x; local booleanvar match:=false; for x :=1 to UBound({?PSample}) ( if {table.Material}=Left({?psampletype}[x],instr({?psampletype}[x])-1) and {table.type}=right({?psampletype}[x],len({?psampletype}[x])-instr({?psampletype}[x])+3) then match := true ); match |
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:24pm
|
Originally posted by Laguna
We'll probably have to create two different parameters for this. We did this concatenation to make it easier for users, but now it's difficult for us to figure out how to split. We had static lists but need to go to dynamic lists.
Thanks for your time! Much appreciated! well while i do like for sake o know-how to find ways to work it, and i think we could, the path we are headed down is going to strain your DB quite a bit, testing every possible material against how ever many are in selected. Though, as a note, i do not think you can have multiple selections of different types of material if you have them as 2 parameters. |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 12:32pm
|
i'll have to look into our selection more, but our database admin confirmed options like this in our list are correct...
MATERIALNAME1 - MATERIALTYPE1
MATERIALNAME1 - MATERIALTYPE2
MATERIALNAME2 - MATERIALTYPE3
MATERIALNAME2 - MATERIALTYPE4 MATERIALNAME3 - MATERIALTYPE5
MATERIALNAME4 - MATERIALTYPE5 thanks
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:41pm
|
did using something like
local stringvar FullName := {table.materialname} & " - " & {table.materialtype}; fullname in {?PSampleType} not work? I tested it on my side and it should work ok. What error were you getting? *edit* In my first post i had the name and type backwards if you didn't correct the order then it would not have found it. |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 12:52pm
|
honestly i'm still trying to figure out how to put this in my select statement. It's asking for a boolean here...
local stringvar FullName := {NAI_SAMPLES.MATERIAL_NAME} & " - " & {NAI_SAMPLES.MATERIAL_TYPE}
|
Posted By: Schugs
Date Posted: 06 Feb 2013 at 12:56pm
|
Originally posted by Laguna
honestly i'm still trying to figure out how to put this in my select statement. It's asking for a boolean here... local stringvar FullName := {NAI_SAMPLES.MATERIAL_NAME} & " - " & {NAI_SAMPLES.MATERIAL_TYPE}
you need to be sure to put the ";" at the end of that line and have fullname in {?PSampleType} on the second line another option would be to make a formula named "Fullname" and in it have the {NAI_SAMPLES.MATERIAL_NAME} & " - " & {NAI_SAMPLES.MATERIAL_TYPE} then in the select formula you can have {@Fullname} in {?Psampletype} that will accomplish the same thing. |
Posted By: Laguna
Date Posted: 06 Feb 2013 at 1:08pm
|
yes it's there. Also noticed that the table i'm pulling from is using the synonym name of another table so having trouble adding the correct field from the correct table.
i'll have to continue in the morning.
Thank you so much
|
Posted By: Laguna
Date Posted: 12 Feb 2013 at 4:47am
|
Hello again, i'm still working on this issue. Hopefully someone can continue to help me.
Overview: I concatenated 2 fields MaterialName with MaterialType and called it SampleType (MaterialName - MaterialType), to help users choose in the dynamic parameter.
Now I need to extract the MaterialName and MaterialType to use in the select statement.
I am able to split and extract out the MaterialName and MaterialType from the concatenated SampleType by using split({?PSampleType}[1]," - ")[1].
split({?PSampleType}[1]," - ")[1] gives me the MaterialName from the first SampleType
split({?PSampleType}[1]," - ")[2] gives me the MaterialType from the first SampleType
split({?PSampleType}[2]," - ")[1] gives me the MaterialName from the second SampleType
split({?PSampleType}[2]," - ")[2] gives me the MaterialType from the second SampleType
Could someone help me figure out how to work the array for multiple (>400) SampleTypes instead of hard coding? Some users choose one SampleType, some users will choose many (>400) SampleTypes.
Thanks |