Link to home
Create AccountLog in
Avatar of ROTRTechTeam
ROTRTechTeam

asked on

Grouping and sorting from VB 6 app

I have a report in a VB 6 app that I want to dynamically select the grouping and then sort the details within the group on another field that is in the report.  Thus, I've created a formula field that is called @Grouping and another called SortFormula.  I have the following code in VB:

    If optSort(0).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
    ElseIf optSort(1).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.TouchType}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.TouchType}"
    ElseIf optSort(2).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
    Else
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.touchdate}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.touchdate}"
    End If
   
    If optDirection(0).Value Then
        Report.RecordSortFields(1).SortDirection = crAscendingOrder
    Else
        Report.RecordSortFields(1).SortDirection = crDescendingOrder
    End If
 
This works to enable the dynamic select of the groupings.  Thus, if I pick "sorty by Entered By" it will group by EnteredBy field and if I select "sort by Restaurant Name" it will group by the RestaurantName field.  However, what I really want is this:

If the sort is by anything other than EnteredBy, I want to group by the field being sorted and then sort the details by EnteredBy.  If the sort is EnteredBy, I want to group by EnteredBy and then sort the details by RestaurantName field.  

So, I changed the above code as follows:

    If optSort(0).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
    ElseIf optSort(1).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.TouchType}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
    ElseIf optSort(2).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
    Else
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.touchdate}"
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
    End If
   
    If optDirection(0).Value Then
        Report.RecordSortFields(1).SortDirection = crAscendingOrder
    Else
        Report.RecordSortFields(1).SortDirection = crDescendingOrder
    End If

What happens is that the grouping is no longer dynamic.  It is always by EnteredBy.  What am I doing wrong?

Thanks!

Mike
Avatar of Mike McCracken
Mike McCracken

Avatar of ROTRTechTeam

ASKER

I used that example when I created my code.  I added the lines that were missing as follows:

    If optSort(0).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
        Report.FormulaFields(1).Check x, y
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(3).Check x, y
    ElseIf optSort(1).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.TouchType}"
        Report.FormulaFields(1).Check x, y
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(3).Check x, y
    ElseIf optSort(2).Value Then
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(1).Check x, y
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
        Report.FormulaFields(3).Check x, y
    Else
        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.touchdate}"
        Report.FormulaFields(1).Check x, y
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(3).Check x, y
    End If
   
    If optDirection(0).Value Then
        Report.RecordSortFields(1).SortDirection = crAscendingOrder
    Else
        Report.RecordSortFields(1).SortDirection = crDescendingOrder
    End If

With this code, when I select to sort by EnteredBy (optSort(2).VAlue = True) then it executes this code:

        Report.FormulaFields(1).Text = "{rep_RestaurantTouchReport;1.EnteredBy}"
        Report.FormulaFields(1).Check x, y
        Report.FormulaFields(3).Text = "{rep_RestaurantTouchReport;1.RestaurantName}"
        Report.FormulaFields(3).Check x, y

Report.FormulaFields(1) is the @Grouping formula field and thus, should be controlling the grouping.  However, upon executing this code, it groups by RestaurantName and not EnteredBy.

What am I doing wrong?

Mike
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Well, when I set the sorting to the same value as the grouping, it changes correction, but I'll give it a shot your way and see what happens.  I will let you know.  Thanks!

Mike
OK using your method worked great!  However, just for the benefit of others, it seems that Crystal adds some stuff to the name of the formula fields.  Thus, my fields named @Grouping comes up as {@@Grouping} in the VB code and the field SortFormula comes up {@SortFormula}.  However, using mlmcc's method, I was able to get my report to do exactly what I wanted.  Thanks!

Mike
Glad i could help

mlmcc