Crystal reports select critera with comma delimited

Question

I have a crystal report I need to change the select criteria on. Currently criteria compare's a database field to the parameter I created in the report.

{MaterialCR.MaterialId} = {?MaterialId}

I am now have a field that has comma delimited data in it I need to make sure the parameter includes any of the other ids in the new field.

Materialused has this data in it. "MA0161 ,MA0167" (No double quotes) . This doesn't work

{MaterialCR. MaterialUsed} = {?MaterialId}

I have tried to create a function to compare the two but it does not seem to work. It does not see the parameter as a string array.

My material match function that does not work

Function MaterialMatch (MaterialUsed as string,v1 ()  As String)
    dim MyArray() as string
   MyArray = Split (MaterialUsed,"," )

    dim Match as boolean
    Match = false
    dim x as number
    For x = 1 To count(v1) Step 1 
        IF "ALL" in v1 then
            Match = true
            x = count(MyArray)
        end if   
        if MyArray(x) in v1 then
            Match = true
            x = count(MyArray)
        end if   
     Next x

    MaterialMatch = Match
End Function

This is what the data I am looking at looks like. We have many materials with a Material ID in it. We also have associated time that we need to select. It does not have a material id as it is a many to one situation. I need to retrieve all the records associated with the material including the time. Getting the material with ids is not the issue. I need to get the Time records also. I modified the view this report uses to include the material that overlaps the time. This is where I am stuck.

This is what my select expert formula looks like now. I do know the material used part is wrong.

(
{JobTimeMaterialCR.MaterialId} = {?MaterialId} 
or 
(
    {JobTimeMaterialCR.Type} = "Time"   
    and 
    {JobTimeMaterialCR.MaterialUsed} = {?MaterialId} 

))

Answer 1

I was able to write a formula that worked for me using the logic I described in my comment. Use this formula as your Record Selection Formula. In Formula Workshop these are found in Selection Formulas > Record Selection.

Local StringVar array values := Split({?Search Values},",");
Local NumberVar indexCount := Count(values);
Local BooleanVar found := false;
Local NumberVar counter;

For counter := 1 to indexCount Step 1 Do
(
    If InStr({ARINVT.DESCRIP}, values[counter]) > 0 Then
        found := true
);
found;

It's rough, but a good start. The search is case sensitive, so you may need to tweak it with some Lower() functions if you want case insensitive searches. Also, if there is a space between the delim character and the search string in your CSV string, then the space is included in the search. A Replace() function can help you get around this, but that would prevent you from using spaces in the search strings. If you need to use spaces in searches, then just take care when building your CSV String that there are no spaces before or after the comma that is your delim character.

If you need any help understanding the syntax of my formula feel free to comment and I will answer any questions.

I used a parameter field called {?Search Values} to simulate the CSV string data. {ARINVT.DESCRIP} is a field name from my test database I used to search thousands of records for key words I typed into my parameter field. You will want to replace these field names in the formula with your field names and you should be able to get this working without much trouble.