looping through records

[BB69]

Hello,

I am using CR11 with Macola progression. I have a list account numbers which, for each number, I want to search through a list of notes and if it finds a matching number, display the note corresponding to the number. I have tested the strcmp line on it's own with a value of <>0 and it works but I cannot get the loop to work properly.
I get the error "a loop was evaluated more than the maximum number of times allowed." It seems to not go to the next record and just evaluates the current record always. I have changed the while condition to = "" and it exits but I get the value of True instead of my strcmp value. I have also tried for the while condition not onlastrecord but I still get the error above. Removing the Not exits the loop and the value True shows again.

If the loop needs an array, I am not very familiar with them so I would need help in preparing that as well.


do (
if strcmp({@MergeAccount},{SYSNOTES.SYS_NOTE_NAME_VALUE},1) = 0 then {SYSNOTES.SYS_NOTE_1}
next({SYSNOTES.SYS_NOTE_NAME_VALUE})
)
while ({SYSNOTES.SYS_NOTE_NAME_VALUE} <> "")

Thanks for any help
BB

 

[lbass]

If the notes field is in a separate table, why not just link the two tables together with an equal join?

Instead of showing a solution that doesn't work, I think you should show some sample data at the detail level using the fields you mention, and also show the content of your nested formula {@MergeAccount}. Then show a sample of the results you are trying to achieve.

-LB

 

[BB69]

The way the information is stored in the tables for the account numbers is 3 columns. Each column can have a range from 0 to 8 alphanumeric characters. If an account is configured to use only 4 numbers per column, for example, then the software fills the column with lagging zeros so there will always be eight characters in a column. The mergeaccount formula merges the 3 columns into 1 line. Now the way the notes are stored in the other table is the 3 columns are merged into a single column so I cannot link the tables at all. I also cannot modify the tables by adding columns.

Example of accounts
table 1 (account numbers)
main department cost center
53300000 22430000 00000000
53300000 30000000 35100000
53300000 40000000 40100000

Table 2 (notes on account numbers)
note_name_value sys_note_1
533000002243000000000000 87445-5551-55512
533000003000000035100000 1254-55-5526
533000004000000040100000 dfd-0d52s-sd5

What I want to do is for each account number in table 1(using {@MergeAccount} to combine the accounts), search the note_name_value in table 2 and display the corresponding value in sys_note_1. I have hundreds of account numbers with just a few notes for now.
Here is the code I have so far

shared stringvar notes := "";
do (
if strcmp({@MergeAccount},{SYSNOTES.SYS_NOTE_NAME_VALUE},1) = 0 then (notes :={SYSNOTES.SYS_NOTE_1})
else notes := "";
next({SYSNOTES.SYS_NOTE_NAME_VALUE});
)
while (isnull({SYSNOTES.SYS_NOTE_NAME_VALUE}));
notes

My mergeaccount formula looks like this:
{SYACTFIL.SY_ACT_MAIN_NO}&{SYACTFIL.SY_ACT_PC_NO}&{SYACTFIL.SY_ACT_DP_NO}


I do get a result now with the above code but only in one field. The second line in the account numbers are shown. I also do not understand why the while loop only works when the isnull is used since the data in the column is not a null.

BB

BTW. I posted my earlier code so someone could tell me what is wrong with it.

 

[lbass]

I would just insert a subreport and use the {@MergeAccount} formula in the main report to link to the {table2.note_name_value} field in the subreport. I don't think it would be any slower than your current approach. Then only the corresponding note would display.

-LB

 

[BB69]

I tried to do a subreport but it is taking very long to browse the data. It took 1 minute after clicking jump to last page (49) for it to go there. I do get the results correctly but the time is too long.

In my example I got it to work, however, the first account in the list shows 7 times then the next account and every subsequent account shows 5 times. I made 7 notes for 7 different accounts and they show up except the first found account shows up on the first list, the second found on the second list.

Example of what I mean
533000002243000000000000 112233-522-55--5
533000002243000000000000
533000002243000000000000
533000002243000000000000
533000002243000000000000

533000003000000035100000
533000003000000035100000 12544-77441-888
533000003000000035100000
533000003000000035100000
533000003000000035100000

533000004000000040100000
533000004000000040100000
533000004000000040100000 1452-1452-1987
533000004000000040100000
533000004000000040100000

Has anyone seen that before