Complicated joining of 3 DB fields 1

[neo86]

Hi all,

I need some help with CR 2011 formula

Need to do something like this




DBfield1 DBFieldTest1 DBFieldTest2


flight null null


cycle 2 null


boat null 3


train 1 2


car,car,car abc;def;hijk null


bike, bike, car 1;2;3 abc;def;hijk


null null null






In Report Display one field combining the data from those three DB fields as




1) flight


2) cycle Test1:2


3) boat Test2:3


4) train Test1:1 Test2:2


5) car Test1:abc, car Test1:def, car Test1:hijk


6) bike Test1:1 Test2:abc, bike Test1:2 Test2:def, car Test1:3 Test2:hijk


7) null


Thank you.

 

[neo86]

Giving a clearer view of what I want as the post above did not come up clearly .


DBfield1 DBFieldTest1 DBFieldTest2

flight null null


cycle 2 null


boat null 3


train 1 2


car,car,car abc;def;hijk null


bike, bike, car 1;2;3 abc;def;hijk


null null null


In Report Display one field combining the data from those three DB fields as




1) flight


2) cycle Test1:2


3) boat Test2:3


4) train Test1:1 Test2:2


5) car Test1:abc, car Test1:def, car Test1:hijk


6) bike Test1:1 Test2:abc, bike Test1:2 Test2:def, car Test1:3 Test2:hijk


7) null


Thank you.

 

[Madawc]

Nulls are noted using IsNull, which must come first otherwise the formula will stop.

You can string fields together using &. You can also created formula fields for one function and include them in another formula field.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[neo86]

Madawc,

Thanks for the response. Can you tell me how to handle that Null value case in the formula. Thats the problem I am having now.

 

[CoSpringsGuy]

ok i saw this post this morning and started playing with it and got confused then got terribly engrossed. I am certain there will be an easier way to handle this but here is what I did. And you better appreciate it! Just kidding... It was fun

Create a formula with the following and make sure you change the drop down for nulls to Default values for null


local numbervar i;
local stringvar output;
local stringvar array piece1;
local numbervar p1 := ubound(split({dbfield1},","));
for i := 1 to p1 do
(Redim preserve piece1;
piece1 := split({dbfield1},","))+ " ";

local stringvar array piece2;
local numbervar p2 := ubound(split({DBFieldTest1},";"));
for i := 1 to p2 do
(Redim preserve piece2;
piece2 := "Test1:" & split({DBFieldTest1},";"));

local stringvar array piece3;
local numbervar p3 := ubound(split({DBFieldTest2},";"));
for i := 1 to p3 do
(Redim preserve piece3;
piece3 := "Test2:" & split({DBFieldTest2},";"));
local numbervar array max := [p1,p2,p2];
if p1+p2+p3 = 0
then
(output := "Null"
else
(
redim preserve piece1[maximum(max)];
redim preserve piece2[maximum(max)];
redim preserve piece3[maximum(max)];
for i := 1 to maximum(max) do
(
output := output & " " & piece1 & " " & piece2 & " " & piece3 & ","
);
output := left(output,len(output)-1);
);
output


LOL ... that whole thing makes me laugh BUT, I tested it on each of your examples and it works

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[neo86]

CoSpringsGuy,

You are awesome ... it works like a dream ... Thanks a lot .... I would have treated you for this if I got a chance .... Thanks a Tonne

 

[neo86]

CoSpringsGuy,

Thanks a lot for the help but I am sorry if its too much to ask , I figured out the rest but can you tell me what this part of ur code is doing. I want to learn and am very much interested how this works .....


local numbervar array max := [p1,p2,p2];
if p1+p2+p3 = 0
then
(output := "Null"
else
(
redim preserve piece1[maximum(max)]; ????
redim preserve piece2[maximum(max)]; ????
redim preserve piece3[maximum(max)]; ????
for i := 1 to maximum(max) do
(
output := output & " " & piece1 & " " & piece2 & " " & piece3 & ","
);
output := left(output,len(output)-1); ?????
);
output

 

[CoSpringsGuy]

local numbervar array max := [p1,p2,p2]; created an array with the number of elements in each "piece" array
if p1+p2+p3 = 0 if you added the number of elements in the arrays together and it was zero, you wanted the Text to read Null
then
(output := "Null"
else
(
redim preserve piece1[maximum(max)]; ???? we already assigned values to the arrays but in order for my next for i to work they n
redim preserve piece2[maximum(max)]; ???? needed to be the same size. Maximum(max) gave me the highest number of elements for the
redim preserve piece3[maximum(max)]; ???? arrays created. Redim preserve keeps the values already in the array and adds blank values
for i := 1 to maximum(max) do to the end until it contains the same number as the highest value
(
output := output & " " & piece1 & " " & piece2 & " " & piece3 & ","
);
output := left(output,len(output)-1); ????? in the line above I added commas as seperators per your needs but i needed to strip the last one off
);
output


******It turned out to be a fun formula although probably a little unusual in the method *******


_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[neo86]

CoSpringsGuy,

I dont see why yu say its an unusual method .. your formula is awesome ... Thanks a lot again for the explanation ..

 

[neo86]

CoSpringsGuy,

I need your help again.

WhilePrintingRecords;
local stringvar array a1;
a1 := split({Command.FF},"/");
local numbervar i;
local stringvar R1;
for i := 1 to ubound(a1) do
(
(
R1 := R1 + " " + mid(a1,1) + ","
);
R1 := left(R1,len(R1)-1);
);
R1

I am using this formula to split a field and show on my report and if it has many entries then it should show as A , B, C

SO I want it to get rid of the final comma. But when I use the above formula it gets rid of all the commas
it displays as A B C. How Do I achieve it?

 

[CoSpringsGuy]

neo86 ... let me know if you have solved this issue ... I have been unable to spend any time on this and wont be until tomorrow or later today at the earliest.

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[neo86]

yes CoSpringsGuy I solved that issue ... Thanks for responding

 

[neo86]

I have a different question though ... does your formula handle the case which I mention below

DBfield1 DBFieldTest1 DBFieldTest2
bike, bike, car null(blank);2;3 abc;def;null(blank)

the above can be considered as the 8th case ......

this should display in the report as

bike Test2:abc, bike Test1:2 Test2:def, car Test1:3

should omit the null and just display the ones with the values ....

and just to be more clear null means null value in the DB ... it ll b blank .. Does your formula handle the above mentioned 8th case?

Thanks

 

[neo86]

sorry if it is null it ll b as NULL(text) not blank .. it ll have the value as NULL and I should not show that value ..... its more like

DBfield1 DBFieldTest1 DBFieldTest2
bike, bike, car NULL;2;3 abc;def;NULL

the above can be considered as the 8th case ......

this should display in the report as

bike Test2=abc, bike Test1=2 Test2=def, car Test1=3

should omit the null and just display the ones with the values ....

and just to be more clear null means null value in the DB ... it ll b blank .. Does your formula handle the above mentioned 8th case?

Thanks

 

[neo86]

sorry I copy pasted the last part in the above post ... so please ignore the last line which says null = blank value .... NULL is a text in DB and not blank and I want to omit it as shown above .... Does your formula handle that case?

 

[CoSpringsGuy]

no sir .... I will have to work on that when i get a minute ... I probably should have thought of null values within the array from the DB

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[neo86]

OK. we have made those null values to be displayed as NULL (- TEXT) in DB .. to make it easy ... when u get time help me out .. Thanks

 

[neo86]

we did that because without that we might not know which one is null


DBfield1 DBFieldTest1 DBFieldTest2
bike, bike, car NULL;2;3 abc;def;NULL

without the NULL text it would look like this


DBfield1 DBFieldTest1 DBFieldTest2
bike, bike, car 2;3 abc;def

which is reallly gonna mess things up ....

so we had the text as NULL for those null values .....

this should display in the report as

bike Test2=abc, bike Test1=2 Test2=def, car Test1=3

should omit the values having NULL text and just display the ones with the values .... is that achievable? hoping for some help .. Thank you

 

[CoSpringsGuy]

This is crazy code but it works so i dont care Did you change your out put to be an = instead of : as in your last post? If you did you will have to fix that in the following .. Had to make a few changes so We could probably recode the entire thing now since all your fields will have 3 elements. Some of that code was to fix the fact that we didnt know how many elements would be in the fields.. so give this a shot.. I will just post the entire thing and highlight the changes. This is assuming you used the text NULL like you indicated..

local numbervar i;
local stringvar output;
local stringvar array piece1;
local numbervar p1 := ubound(split({@dbfield1},","));
for i := 1 to p1 do
(
Redim preserve piece1;
piece1 := split({@dbfield1},",")+ " ";
);

local stringvar array piece2;
local numbervar p2 := ubound(split({@DBFieldTest1},";"));
[highlight #CE5C00]for i := 1 to p2 do
(
Redim preserve piece2;
if split({@DBFieldTest1},";") = "NULL" then
piece2 := split({@DBFieldTest1},";") else
piece2 := "Test1:" & split({@DBFieldTest1},";");
);[/highlight]

local stringvar array piece3;
local numbervar p3 := ubound(split({@DBFieldTest2},";"));
[highlight #CE5C00]for i := 1 to p3 do
(
Redim preserve piece3;
if split({@DBFieldTest2},";") = "NULL" then
piece3 := split({@DBFieldTest2},";") else
piece3 := "Test2:" & split({@DBFieldTest2},";");
);[/highlight]
local numbervar array max := [p1,p2,p2];
if p1+p2+p3 = 0
then
(output := "Null"
else
(
redim preserve piece1[maximum(max)];
redim preserve piece2[maximum(max)];
redim preserve piece3[maximum(max)];
for i := 1 to maximum(max) do
(
output := output & " " & piece1 & " " & piece2 & " " & piece3 & ","
);
[highlight #CE5C00]// there are several instances where the output is messed up so here is my attempt to fix.
output := left(output,len(output)-1); //Remove the last comma same as before
output := replace(output," , ",""); //fix another comma issue
output := replace(output," NULL",""); //remove the word NULL
output := replace(output," ,",", "); // shifts a comma around if needed
output := replace(output," "," "); //fix double spaces[/highlight]
);
output

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[neo86]

Thanks a lot CoSpringsguy .... and I would like to know one more thing ... Why is it I dont see an option to edit my post .... If i want to make any changed to the post tht I already posted I dont see an edit option .... I have to type in the whole thing again with the changes .... or am I not seeing the edit option?