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
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
Mike McCracken

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
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ROTRTechTeam

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ROTRTechTeam

ASKER
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
Mike McCracken

Glad i could help

mlmcc