Pull records based on a query?

[Bennie47250]

Using Crystal CD Developer version 14.0

Hopefully I can explain what I wanting to do clearly.

Our distributors are assigned sales territories and they are allowed to have other distributors in the territory. Let’s call the distributor assigned to the territory the Master Distributor (MD) and the other distributors in the territory Sub Distributors (SB)

Where we measure the performance of the MD they get credit for everything sold in the territory not to just their customer number.
Therefore I cannot just pull records based on the MD customer number as that would not include the sales to the SD’s

1) So what I think needs to happen is
2) Query the customer master to find the MD and their assigned territories
3) Use this list to get the territories numbers
4) Pull records based on territories numbers

The customer list is dynamic so I’m trying to advice hard coding the list

Would like to do all this inside the Crystal report if possible.

 

[SkipVought]

HI,

What happens in vagueness stays in vagueness.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bennie47250]

In the event Skip is not the only one who finds this vague.

To create the final record selection list of territories to pull records for I would like to

Query our customer master to find the Master Distributors (This query currently returned 16 customers and 16 unique territory numbers)
When the report runs, want it to use the territory numbers returned in the above step

If I were to hard Code the territories the selection expert it would look like: {sales_information.territory} in [805, 806, 810,.......]
I would like to dynamically populate the values for the list.

Hope this does more good that harm

 

[kray4660]

So do you want to use the first query to populate possible values for a prompt? i.e., List of Values (LOV). If you are using a command, you can create a second command (not linked to the first command) to supply the LOV to a prompt (I searched internet on how to do this). Otherwise I am thinking that you may need a sub-report.

 

[hilfy]

If you're going to work with commands, please see my blog post here for info about best practices: [URL unfurl="true"]http://scn.sap.com/community/crystal-reports/blog/2015/04/01/best-practices-when-using-commands-with-crystal-reports[/url]

Also, if you post more info about the table structures and what data you need, I might be able to help you write the query.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com