Crystal Report is not updating the report fields with the correct data from a table.

I am trying to do a crystal XI R2 report that I have attached.  I just attach the detail section and not the header. I also have a key that is in the main table and sub report table that I have them link on. They want element of BMP, Potential Problem, How to fix Problem, and who will address problem Columns hard coded on the report. The Y/N field is in a table. It can be Yes, no, Satisfactory, or non-Satisfactory. Comments are also in a table. The main table has a one to many relationships with this table. There can be anywhere from 17 to 30 records for the one record in the main table. The element of BMP and Potential Problem is actually in the table but I have to search for a dash and then split the field into the Element of BMP and Potential problem columns. My problem with this is they will have to key the dash just right. I added a sub report and set the form up like you see it on the attachment then tried to set up fields for the ones that need updating. That did not work as I thought it would.   I tried another process were I created one line in the sub report but set up formulas to search for a word in the sentence and then print the right How to fix the problem and who will fix the problem. That reports work but if they change, the word I am searching on this will not work. I tried setting this up on the main report in the detail section then have the sub report pass the information back to the main report but that does not work correctly, since it is so many records. Do anyone have any ideas how to accomplish this I would appreciate it.

Thanks
Steve
RoofDisconnectProblem_edited.jpg
RoofDisconnectOriginalForm.JPG
stevenofvaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
If you have a string field that contains a dash and you want to split the field into the parts before and after the dash, you may be able to use the Split function.  Simplest case, you could use Split ({your field}, "-") [ 1 ] and Split ({your field}, "-") [ 2 ] to get the parts before and after the dash.  But if the field could be empty/null, or might not contain a dash, then you would need to add some checks to avoid getting an error.  And if the field could contain more than one dash, how would you want to split it?

 James
0
stevenofvaAuthor Commented:
Thank you James. Now I just need to get the report layout to work. I need to get the main report to update the fields line by line correctly from the sub report. I don't think i can design a form layout in a sub report and get it to fill out correctly.  For example I need the Y/n columns to print either Y, N, S... I can strip off the first character with no problem to get the Y or N. The problem is trying to get the correct status to print with the correct  potential problem horizonal columns. I have tried grouping on the main report and updating the Y/N column from the sub report but that does not work correctly. I have put the report in the deatail section of the main report and tried to update the Y/N column from the sub report but that fail too.
0
mlmccCommented:
THe subreport is the last thing to run in a section so unless the subreport displays the value it won't update until the next record.

Do you need the subreport to display anything or are you trying to use it solely to generate the value?

If it is to solely generate the value try this idea
Make sure the subreport doesn't display anything
Add a second detail section
Right click the detail section in the left margin
Click INSERT SECTION BELOW

Move the section up
CLick the DETAILS B in the left margin and drag it up
  Alternatively use the section expert

Put your subreport in the blank details a section
In the section Expert set details A to SUPPRESS BLANK SECTION (thus the need for the subreport to show nothing

Use a formula to display the shared variable in the main report

mlmcc
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

stevenofvaAuthor Commented:
Thank you mlmcc. I did what you said. Now I get the last record in the sub report. I have attach what the table records look like that I am using in the sub report. The ac_text field is the field I am splitting to get the Element BMP and the potential problem column. The Y/N field contains what is in AC_PASS_TY field. In this particular type of form it is 17 records in the table.  The AC_Number field change from 1 to 17. The AC_AI_ID field that contains 11 is untique for this type of form and will change depending on report type. I have set up fields for Element of Bmp, Potential problem Y?N and Comments. I can get those to print correctly. The How to fix problem and who will address problem  information is suppose to be hard coded on the form. This information is nowhere in a table. Originally I search for example Mosquito in the potential problem field then print the correct how to fix problem. I have them set up in a formula with IF statements, Is there a better way to code those fields on the form instead of a formula?
Table-for-Roof.JPG
Roof-Disconnect-Form.JPG
0
mlmccCommented:
Are they hard-coded (never change) or do they depend on other values?

mlmcc
0
stevenofvaAuthor Commented:
Hi Mlmcc,
 These two fields are hard coded The How to fix problem column and who will fix column. They are depended on the element of BMP column and potential problem colum. These two column are one record in the table and contain in one field called AC_Text. I am splitting the field to get the Element of BMP and Potential problem. The Y/N column and the comment column is in the same record as the Element of BMP and Potentail problem. I have tried goruping them on the main report and tried to update from a subreport but everytime I add the hard coded fields the other fields will not update correctly. Thank you for your help.

Steve
0
mlmccCommented:
How are you trying to update from the subreport?

mlmcc
0
stevenofvaAuthor Commented:
I tried setting up the static fields on the main report  to look like the attachment call Roof disconnect problem. I then use formula in the subfile to update the dynamic fields like the Y/N, Eleement BMP, Potential Problem, and comment. I can not get them to print in the right line. If you look at  the attachemtn Rooftop Disconnect original it will show how the fields are suppose to line up correctly with the Element and the potential problem.
0
mlmccCommented:
Can you upload the report file?

mlmcc
0
stevenofvaAuthor Commented:
Hi Mlmcc, I checked and I cannot post the report online. Sorry.
Thanks
Steve
0
mlmccCommented:
I don't need to see the data, just trying to understand how you are updating the main report from the subreport

mlmcc
0
stevenofvaAuthor Commented:
I attach a file with the formula from the subreport for one Y/N field on the report. I was thinking i would have to have different named ones all the way down the report to get it to correctly update the right one.

Thanks
Steve
RoofDisconnect.rtf
0
mlmccCommented:
Yes, you will need one shared variable for each value you want to pass back.  If they are all Y/N fields you could do it by sharing an array.

I cleaned up the code since the rtf file you attached has RTF formatting in it making it difficult to read.

WhilePrintingRecords;
Shared Stringvar ItemCheckList1;

ItemCheckList1:= If {SMBMPIC.AC_PASS_CD} = 0 Then 
	Left ({SMBMPIC.AC_PASS_TY},1 )
Else If {SMBMPIC.AC_PASS_CD} = 1 Then  
	Left ({SMBMPIC.AC_PASS_TY},1 )
Else If {SMBMPIC.AC_PASS_CD} = 2 Then  
	Left ({SMBMPIC.AC_PASS_TY},3 )
Else If {SMBMPIC.AC_PASS_CD} = 3 Then  
	Left ({SMBMPIC.AC_PASS_TY},1 )
Else If {SMBMPIC.AC_PASS_CD} = 4 Then  
	Left ({SMBMPIC.AC_PASS_TY},1 )
Else IF {SMBMPIC.AC_PASS_CD} = 5 Then  
	Left ({SMBMPIC.AC_PASS_TY},1 );

Open in new window


mlmcc
0
James0628Commented:
FWIW, your formula could be shortened to:

WhilePrintingRecords;
Shared Stringvar ItemCheckList1;

ItemCheckList1:= If {SMBMPIC.AC_PASS_CD} in [ 0, 1, 3, 4, 5 ] Then
 Left ({SMBMPIC.AC_PASS_TY},1 )
Else
  If {SMBMPIC.AC_PASS_CD} = 2 Then
   Left ({SMBMPIC.AC_PASS_TY},3 );

Open in new window



 In the RTF file, you said that you had a formula in the main report that assigned the value in ItemCheckList1 to a second variable named ItemCheckList.  Are you actually using ItemCheckList (the second variable) somewhere, or is that formula just there to display the value from the subreport (ItemCheckList1)?  If you're just displaying the value right after the subreport, then you don't need the second variable.  You can just use

WhilePrintingRecords;
Shared StringVar ItemCheckList1;
ItemCheckList1

  or actually just

WhilePrintingRecords;
Shared StringVar ItemCheckList1


 James
0
stevenofvaAuthor Commented:
Thank you Mlmcc and James, The Y/N field can contain Yes,No, N/A, Satisfactory and so on. I attach a screen shot of the flied that makes up Y/N column on the report. Each one is a separate potential problem. I am pulling just the first character of most of them unless like N/A I pull all three characters. I have never done a shared variable. I will have to look that one up.  
Thanks
Steve
Status-Field-for-Storm-.JPG
0
James0628Commented:
I have never done a shared variable.
The formulas in the RTF file that you posted use shared variables (eg. Shared Stringvar ItemCheckList1).  Did you maybe mean an array?

 If so, you don't necessarily have to use an array.  It will depend a lot on how many different values you need to store at the same time.  For example, if the subreport needs to store up to 10 different results each time it is run, you could use 10 different shared variables (ItemCheckList1, ItemCheckList2, ItemCheckList3, and so on through ItemCheckList10).  Or, you could use an array named ItemCheckList with 10 elements (referenced using ItemCheckList [ 1 ], ItemCheckList [ 2 ], ItemCheckList [ 3 ], and so on).  If you need to store many more values than that, then an array can be a lot simpler than using X separate variables.

 FYI, arrays are limited to 1000 elements in CR.  If you need to store more values than that, you can use multiple arrays.

 James
0
stevenofvaAuthor Commented:
Thank you James for the information. I did mean array. I had several things going on yesterday.  I have 17 records in this report. Other report I have some as high as 25 records in the check list. I will have to read up on how to create an array and how to get the different items from them for my report.  

Thanks
Steve
0
stevenofvaAuthor Commented:
I set the array formula up in the subreport and the printing the Array from the subreport in the main report. The problem I am having is all I get when I print formul on the main report is True.  I have tried some suggestions from the web but they are not working.   Some of the records in AC_Pass_TY field will be null until it is assign a value. I have the Array to get the information from the table in the detail section of the sub report. I tried having the main report Array to print what in the array in the header section and the detail section but I get the same results. Can anybody tell me what I am doing wrong please.
 
Main Report Formula:

WhilePrintingRecords;
Global StringVar CheckListA;
Global NumberVar RecordCounter;
Local NumberVar Index;
Local StringVar ArrayCheckList;
For Index := 1 to RecordCounter do
 ArrayCheckList :=  CheckLista[Index];

Sub Report Formula:

WhilePrintingRecords;
Global NumberVar RecordCounter;
Global StringVar Array CheckListA;
RecordCounter := RecordCounter + 1;
Redim Preserve CheckListA[RecordCounter];
If ({SMBMPIC.AC_PASS_TY}) in CheckListA Then
CheckListA[RecordCounter]:= {SMBMPIC.AC_PASS_TY};
0
James0628Commented:
The first thing is that if you want to use the same variable in the main report and a subreport, it has to be shared, which means that it has to be declared as Shared in every formula that uses it.  Global variables only exist in one report (the main report, or a specific subreport).  If you use a global variable with the same name in the main report and one or more subreports, they're actually all separate variables.

 Also, you left Array out of the declaration for CheckListA in the main report formula.  FWIW, you would have gotten an error on one of those declarations (since one is an array and the other is not), but since you declared them as Global, they're separate variables (so CR doesn't care if one is an array and the other is not :-).

 So, those variable declarations (in both formulas) should be:

Shared NumberVar RecordCounter;
Shared StringVar Array CheckListA;


 FYI, you have to be careful with variable declarations in CR, because it doesn't try to catch mistakes for you.  For example, if you misspell a variable name in one formula, CR just creates a new variable with that name.  And each variable scope (Local, Global or Shared) is separate.  If you used Shared StringVar var1 in one formula and Global StringVar var1 in another formula, CR won't complain about it.  It will just create 2 separate variables (one that's shared and one that's global).  So, simple mistakes can cause some real confusion, especially since you may not get any errors, so everything seems fine.

 In case you were wondering, you didn't get an error when you used CheckLista[Index] in the main report formula for a couple of reasons.  First, since you made RecordCounter global, it was presumably just 0 at that point, so the For loop didn't run at all (For Index := 1 to 0).  Also, you can treat a string as an array of single characters, so CheckLista[Index] is valid syntax, even if CheckLista is just a single string.  For example, "abc" [ 2 ] would give you "b".

 Anyway, getting back to your report, what you're doing in the main report formula is probably wrong.  You have

For Index := 1 to RecordCounter do
 ArrayCheckList :=  CheckLista[Index];

 That's just going to put each value from CheckListA in ArrayCheckList, with each new value replacing the previous one.  At the end of the loop, ArrayCheckList will just be the last value in CheckListA.  What are you really trying to do there?  If you're trying to somehow combine the values in the array, you could look at the Join function.  Join will combine the values in a string array into one string, using a separator that you specify.  For example, Join (CheckListA, " ") or Join (CheckListA, ",").  If you wanted to combine the strings in the array, and have each one come out on a separate line on the report, you could use Join (CheckListA, ChrW (10)).  That puts a line feed character (ChrW (10)) in between the strings from the array.  Put that formula on the report and set the "Can Grow" option in the field format, and the values will come out in a column.

 James
0
stevenofvaAuthor Commented:
Thank you James I appreciate the help. I am just learning about arrays. I got the information off the internet. I am trying to have on he main report a variable for each record from the Subfile for Checklist. Some of the Records wil just have null in the AC_PASS_TY and others will have No, Yes, N/A.... I need to print these fields in a list from top to bottom like a book. Will doing a join give me that or what is the best way? I was going to set the form up on the main report because of the static fields then have the dynamic fields updated from the array like so.
Yes
No
N/S
Satifactory
Thanks
Steve
0
stevenofvaAuthor Commented:
Sorry James I missread your answer. I am going to give that a shot. I had a lot of things going on at the time. I am going to try the join and see if that will work.

Thanks
Steve
0
stevenofvaAuthor Commented:
Hi, I have got it to work with both of your suggestions. My only problem is trying just to get the first character of the check list item in column 3.  I have tried left, trim, and split and moving the result to a new variable even but nothing works. I get a blank field. I could not get the spit to work at all.  I have my code below  that was the last thing I tried. I attached a screen shot with the fields showing Yes, no, N/A for example. I just need the Y from Yes. The N from no. I need all of N/A. as before. SMBMPIC.AC_PASS_CD controls what is printed.  For example if the field equals 2 then it would be N/a,  Zero is no, and 1 is yes.  I am using the array Checklista [5] then Checklista{[6] for the example to get each element. I have a different Checklist from Checklist 1 thourhg 11 so I can stack the result verically like in the attachment.   Do anybody know what I am doing wrong please?
Formula for CheckList5:
Shared StringVar Array CheckListA;
Shared StringVar Checklist5;
Shared StringVar CheckList5P;
CheckList5:= CheckLista[5];
Ltrim(CheckList5);

CheckList5P:= If {SMBMPIC.AC_PASS_CD} = 0 Then Left (CheckList5,1 )
Else
If {SMBMPIC.AC_PASS_CD} = 1 Then  Left (CheckList5,1 )
Else
If {SMBMPIC.AC_PASS_CD} = 2 Then  Left (CheckList5,3 )
Else
If {SMBMPIC.AC_PASS_CD} = 3 Then  Left (CheckList5,1 )
Else
If {SMBMPIC.AC_PASS_CD} = 4 Then  Left (CheckList5,1 )
Else
IF {SMBMPIC.AC_PASS_CD} = 5 Then  Left (CheckList5,1 );
CheckList5P:= If {SMBMPIC.AC_PASS_CD} in [ 0, 1, 3, 4, 5 ] Then
 Left (CheckList5,1)
Else
  If {SMBMPIC.AC_PASS_CD} = 2 Then
   Left (CheckList5,3 );
CheckLista[5]
RoofTopDisconLatest.JPG
0
mlmccCommented:
Where do you initialize the array CheckListA?

mlmcc
0
stevenofvaAuthor Commented:
Hi Mlmcc, I am not intializing the array to zero anywhere. This report will only print for one main record  for a particular asset.  I have the array loaded in the deatil section of the main report. Then I have the form printing in the group footer. If I did not answer your question please let me know.
Thanks

Steve
0
mlmccCommented:
Can you show the code for that fills ChecklistA?

YOu are setting the value for Checklist5p  then displaying CheckLista[5], is that what you really want?

It is very difficult to troubleshoot the report when we can't see it.  You are having a problem in the group footer but the data is added in other areas.

Perhaps for troubleshooting purposes add formulas to display ChecklistA, Checklist5p  in the report footer.

You can display a string array with JOIN (Checklista,chr(13)) that will put each element on its own line

mlmcc
0
James0628Commented:
As mlmcc said, that formula sets CheckList5P, but then displays CheckLista[5] at the end.  That could be correct, but it seems a little odd.

 Why are you specifically using the fifth value in the array (CheckLista[5] ), and only that value?  That also seems odd, but I don't know what you're putting in the array, so maybe that's correct.

 Also, you're setting CheckList5P twice, once using your original set of If statements and once using my shorter version.  Unless I'm missing something, they both give you the same results, so there's no harm is using both.  It's just an extra step that you don't need.

 Even if you're really only interested in specific values in the array (like the 5th one), using Join to display the contents of the array could be a good diagnostic tool, to let you see exactly what's in there.  In case you're wondering, I suggested using ChrW (10) and mlmcc suggested Chr (13), but the results will display the same either way (10 is a line feed and 13 is a carriage return, either of which will split the values onto separate lines).  And ChrW and Chr are just two names for the same CR function (I got the impression that Chr had been deprecated and ChrW was the new name, so I've always used ChrW).

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevenofvaAuthor Commented:
I was trying different  variable like checklist5p. What I was trying to do is just get the first letter of hte Y/n column. The field can contain  as shown in the attachment No, Yes, N/A, Satisfactory, monitor, unsatisfactory/MNaint Needed. On the report I just need "Y" for Yes, "n" for No, N/A and tThe the first letter for the rest of the status. I am addressing each element so I can put them on the form in the right order.   Column Element of BMP, Potential Problem, How to Fix problem, and who will address the problem are all hard coded on the form. Right now since this is test Compensatory device do not have anything in the y/n, how to fix problem, who will address the problem columns. I can not get it to print right veritcally using the join and Chr(13) so I am addressing the elements individually.  The lines are just drawing objects.   The code I shown as a attachemnt earlier has variables like Checklist5p that I did not need  it was just trying to capture the first letter and did not remove them when I took the screen shot. My problem is I can't grab the first letter of the various statuses or for number 2 the three letters in the screen shot YesnoStatus.jpg. I had tried using Left and addressing the element like ChecklistA[5] to try and just get the first letter  of "Yes" .  The code I am using in CheckList5 in the group footer to print the entire element is below. I just show everything in CheckList5 yesterday but the Extra Cehcklist5a(5) at the bottome is commented out as well as the long if statement.  
Current code for Checklist5:
Shared StringVar Array CheckListA;
Shared StringVar Checklist5;
Shared StringVar CheckList5P;
CheckLista[5];
Roof-Disconnect-form-2132018.JPG
YesnoStatus.JPG
0
mlmccCommented:
Are there multiple detail records?

Since you are doing this in the group footer, only the last record in the group is available.  Perhaps it is the wrong record to be using?

mlmcc
0
stevenofvaAuthor Commented:
Thank you James and Mlmcc. I have the array being updated in the detail section of the main report. I set up a formula to check easch elemnet of the array to see if it was like No for example and then print N. The reason I had to just print the first letter of the status was space limitations. I appreciate all the help. Sorry I could not send the report.
0
stevenofvaAuthor Commented:
Thank you James and Mlmcc for all your help.
0
James0628Commented:
So, you got the report displaying the correct values?  Can you explain what was wrong?  I'm curious.  You mentioned updating the array in a detail section.  Was that what you were missing?

 James
0
stevenofvaAuthor Commented:
Hi James, In the Detail section I put the formula that is in the attachment label Load detail array. In the group footer below group AI_ID number I put the code that is found in the attachement Array Print. AI_ID is the group that link all of the check list items together. For this check list the AI_ID number would be 11 and there is 17 check list items.  The group below the AI_ID  group is a group call fake group. It sole purpose is to get the header to print on each page. If you look at screen shot label Roof disconnect 02142018 that  I attached you will see the Y/N column and what I was trying to achieve. I tried everythign I knew like the left function to get the first letter of each status to print that loaded into the array but nothing worked. I tried the like operator and would was able to compare each element of the array. I did not like doing the compare but it works.  If you see something wrong let me know please.

Thanks
Steve
Load-detail-Array.JPG
Array-Print.JPG
Roof-Disconnect-Form-2142018.JPG
0
James0628Commented:
First of all, when you're posting a formula, it makes things a lot easier if you post it as text (as you did in some of your earlier posts), so people don't have to view the image in a separate window/tab, and then come back to this page to look at your post and write a reply.

 I think I see one error in the Load-detail-Array image.

 You increment RecordCounter and then check to see if the field is in the array, and add it if it's not.  The potential problem with doing that is that you always increment RecordCounter, even if the value is already in the array, so you'll end up with empty elements.  For example, if the first 3 records had "Y", "Y" and "N", then the array would end up with "Y", "" and "N".  The "" is because you incremented the counter for the second "Y", but didn't put anything in that spot (because "Y" was already included).  If you want the array to just include each value once, and nothing else, then the extra empty values might be a problem.  The fix for that is to simply move the increment and Redim into the If statement.  If you don't know, you can use () to include multiple statements in an If-Else.

if <your conditions> then
(
  statement1
  statement2
  ...
);

 OTOH, if you want a one-to-one relationship between the records and the array (the value from the first record is in the first slot, the value from the 5th record is in the 5th slot, etc.), then incrementing the counter each time is correct.


 For the record, a subreport formula that you posted earlier did the same thing (incremented the counter, and then checked to see if the field was in the array), but I didn't notice it at the time.  Also, I just noticed that the earlier formula was actually only putting the field in the array if it was already in the array, so that formula was never actually going to add anything to the array.  You've fixed that in the formula in your last post (by adding Not).  Sorry that I didn't notice that earlier.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.