Joined: 27 Oct 2010
Location: United States
Online Status: Offline
Posts: 4
Topic: Delimited Field Posted: 27 Oct 2010 at 1:41am
Ok I have an interesting and difficult problem to overcome. I have a table with a delimited field that holds salesperson's ids. I need to group this table by individual salesperson ids, showing each record associated to the salesperson id. Getting the individual salesperson ids out of the field isn't the issue. The maximum amount of ids in the field is five. I'll set up an example below:
Id List --- ---------- 1 AJ~DH 2 AJ 3 DH~HK
So I'd like to then take that record set and display in a grouped fashion
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 27 Oct 2010 at 3:32am
I can't think of any way to do this using just tables in Crystal. How are your SQL skills?
Basically you'll need to write a set of queries and "Union" them together. The first query will get the first salesperson ID from the field and all of the rest of your data, the second will get the second salesperson ID and all of the data for those records that have a second salesperson ID, and so on. So, you'll have 5 queries with a union between each of them to provide the data for your report.
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 27 Oct 2010 at 6:52am
But you can write SQL in Crystal! What type of database are you using? When you go to your connection in the Database Explorer, do you have an option to "Add Command"? If so, that's where you build your SQL.
Joined: 27 Oct 2010
Location: United States
Online Status: Offline
Posts: 4
Posted: 28 Oct 2010 at 3:48am
As an FYI...I ended up doing a full join to the Sales Person Table. Obviously, this brought in a lot more data than wanted. I then filtered the data through the Record Selection formula, making sure I only had sales person records for sales persons listed on the invoice. Afterwords, everything else was easy.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
