Splitting into row based on route

I have a table called ROUTING which has 3 fields:

prefix
country
route

My data in ROUTING Table as follows:

Prefix  Country  Route
001      USA     2,3,1
0091    India     4,2
0034    Spain    10
00346  Spain-GSM  2,1

Route above tells priority means first entry with top priority.

I want my above data to display in CR 2008 as follows:

Prefix  Country  Route  Priority  Type
001      USA       2         0           0
001      USA       3         1           0
001      USA       1         2           4
....
...

As you can see that my route 2,3,1 above splitted into rows and two new commupted fields (formulas) in CR required, there is Priority (0 assigning to first choice, then 1 to 2nd choice and so on). Similarly Type Formula field remain 0 for all choices except for last choice where should appdear as 4.

Let me show 2nd example:

In database I have:

Prefix  Country  Route
0091    India     4,2

In CR it should appear:

Prefix  Country  Route  Priority  Type
0091      India    4         0           0
0091      India    2         0           4

Hope I am able to explain this well.

Thanks in advance for your help and happy new year.
NickHowardAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
This will be quite difficult to do in a way which produces good looking output.

Essentially you have use a formula field to build up a multi-line string based on the single record.


Here is some sample code for such a formula field

stringvar prefix;
stringvar country;
stringvar route;
stringvar Type;
stringvar output;
// for testing: real report would assign fields
prefix:="0001";
country:="USA" ;
route:= "3,1,2";
type:="4";

numbervar x;
numbervar ub;
stringvar array Routes;
routes:=split(route,",");
ub:= ubound(routes);
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & type & chr(13);
output

0
 
peter57rCommented:
You must format the formula field to CanGrow to see all the rows produced by the procedure.
0
 
NickHowardAuthor Commented:
Thanks Peter57r for your quick solution.

I am however missing Proirity field.

CR Output should:

Prefix  Country  Route  Priority  Type
0091      India    4         0           0
0091      India    2         1           4

In database I had:

0091 India 4,2

Can you add this? Priority 0 is is for the 1st choice, Prioirty 2 is for 2nd choice and so on (+1).




0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
peter57rCommented:
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(ub-1,0,"") & "  " & type & chr(13);
output

0
 
NickHowardAuthor Commented:
Thanks for very prompt reply. As you said it is complicated and it is happening.

By adopting above change, I get output as:

0001  USA  3  2  4
0001  USA  1  2  4
0001  USA  2  2  4

While it should be:

0001  USA  3  0  0
0001  USA  1  1  0
0001  USA  2  2  4

You can see above that 4th colum is assigning priorities start with 0 and then adding +1 . I my example, since route is 3,1,2 then priority for 3= 0, 1 = 1 and 2 = 2. Similarly the 5th column, it will remain 0 but for last row it will be 4.

I think 2 more formulas to be created, one for Priority (0, 1, 2, 3, ......) and other for Type (0 or 4) and combined them in main output.

Any chance to incorporate above these?

Sorry for the pain.
0
 
mlmccConnect With a Mentor Commented:
Try it this way

for x:=1 to ub-1 do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(ub-1,0,"") & "  0"  & chr(13);

output:= output & Prefix & "  " & country & "  " & routes[ub] & "  " & totext(ub,0,"") & "  4"  & chr(13);

output

mlmcc
0
 
peter57rCommented:
sorry, wrong variable..
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(x-1,0,"") & "  " & type & chr(13);
output
0
 
NickHowardAuthor Commented:
Thanks Peter and Mlmcc.

It is solved by using:

for x:=1 to ub-1 do
output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(x-1,0,"") & "  0"  & chr(13);
output:= output & Prefix & "  " & country & "  " & routes[ub] & "  " & totext(x-1,0,"") & "  4"  & chr(13);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.