Gareth Horton
July 12th, 2002, 02:31 PM
I found a way to handle this, in case it helps anyone. By the way, I'm using Monarch V6 Pro.
Because of the varying record length, simply capturing on rows and columns didn't work. Finally, I trapped on 3 lines only. Trapping more in the detail template would cause the record following a shorter record to get absorbed into the first/shorter record, causing Agency 4 to get trapped as part of Agency 3's data. So, in the detail trap, I trapped on the agency name, captured the agency name and agency code, and then captured the next two lines of data, each as a long string. I then used a calculated field to check for the presence of the strings "TOTAL ORDERS" and "RETURNS", and extracted the data preceding those strings. Then for my appends, I also trapped on the agency name (since using the exact same trap will keep the data with the current record rather than appending it to the next), but then for each the horizontally referenced fields:
- Captured one line, all three columns
- Went to the Advanced tab for the field, selected the "Preceding string:" button under "Start field on", and put in the text, for example, "complaints".
I did this for complaints, compliments, suggestions, and referrals. I still had to use calculated fields to look for the string "/TYPE #" and extract the number preceding it. Using the Instr(,) function I was able to find the data and put in into the correct category and type.
We get a lot of reports with this type of complexity (and more). I imagine there are others dealing with complicated ADP, SIS, etc. types of reports, so I hope this is helpful. If anyone would like, I can e-mail the sample text file and the model.
************************************************** ****
John Fitzgerald wrote:
> I'm having a problem creating a suitable trap. The records contain normal data referenced by columns, but also horizontal references that vary from one record to the next: If there is no data for a particular field, the row is just left out. Here is a sample:
>
>
> ************************
> AGENCY NAME AGENCY CODE
>
> Agency 1 12345
> 600 / TOTAL ORDERS
> 8 / RETURNS
> COMPLAINTS 3 / Type1 7 / Type3 6 / Type8
> COMPLIMENTS 12 / Type6
> SUGGESTIONS 5 / Type3 8 / Type4
> REFERRALS 1 / Type2
>
> Agency 2 23456
> COMPLAINTS 12 / Type1 6 / Type2
> COMPLIMENTS 16 / Type4 2 / Type7 12 / Type9
>
> Agency 3 34567
> 7 / TOTAL ORDERS
>
> Agency 4 45678
> 842 / TOTAL ORDERS
> 8 / RETURNS
> COMPLIMENTS 6 / Type4 7 / Type3 6 / Type8
> SUGGESTIONS 2 / Type7
>
> Agency 5 56789
> COMPLAINTS 6 / Type7
>
> *****(NOTE: In fixed text, columns line up neatly at the "/" character.)
> My column headings in the output file will be Agency Name, Agency Code, Total Orders, Returns, Type1 Complaints, Type2 Complaints... etc, with a zero wherever there is no data present for that type. I'm using calculated fields to put the data into those columns. Example: if(Col1ROW1 = "COMPLAINTS" .And. Col3Row1="Type1"),Col2Row1,0)
>
> Here is my problem: If I have a 2-line trap in my template, data (COMPLAINTS, COMPLIMENTS, etc.) will be missing for Agency 1, 2 and 4. If I have a 7-line trap, Agency 1 is captured correctly, but Agency 3 data is trapped in the Agency 2 record, and the first line of Agency 5 (i.e. the trap) is caught in Agency 4. Since Complaints, Compliments, etc. are horizontal lables, it doesn't help to capture each column from line 2 to the end of the template, since there is then no way to match this value with the information that follows it in the row. Is there any good way to capture the information in the longer records? Is there some utility that will insert a few blank rows at the end of (or before) each record, so that I can have a 7-line trap without ever spilling into the next record? If anyone can tell me how to do that, or come up with a different solution, I'd greatly appreciate it!
Because of the varying record length, simply capturing on rows and columns didn't work. Finally, I trapped on 3 lines only. Trapping more in the detail template would cause the record following a shorter record to get absorbed into the first/shorter record, causing Agency 4 to get trapped as part of Agency 3's data. So, in the detail trap, I trapped on the agency name, captured the agency name and agency code, and then captured the next two lines of data, each as a long string. I then used a calculated field to check for the presence of the strings "TOTAL ORDERS" and "RETURNS", and extracted the data preceding those strings. Then for my appends, I also trapped on the agency name (since using the exact same trap will keep the data with the current record rather than appending it to the next), but then for each the horizontally referenced fields:
- Captured one line, all three columns
- Went to the Advanced tab for the field, selected the "Preceding string:" button under "Start field on", and put in the text, for example, "complaints".
I did this for complaints, compliments, suggestions, and referrals. I still had to use calculated fields to look for the string "/TYPE #" and extract the number preceding it. Using the Instr(,) function I was able to find the data and put in into the correct category and type.
We get a lot of reports with this type of complexity (and more). I imagine there are others dealing with complicated ADP, SIS, etc. types of reports, so I hope this is helpful. If anyone would like, I can e-mail the sample text file and the model.
************************************************** ****
John Fitzgerald wrote:
> I'm having a problem creating a suitable trap. The records contain normal data referenced by columns, but also horizontal references that vary from one record to the next: If there is no data for a particular field, the row is just left out. Here is a sample:
>
>
> ************************
> AGENCY NAME AGENCY CODE
>
> Agency 1 12345
> 600 / TOTAL ORDERS
> 8 / RETURNS
> COMPLAINTS 3 / Type1 7 / Type3 6 / Type8
> COMPLIMENTS 12 / Type6
> SUGGESTIONS 5 / Type3 8 / Type4
> REFERRALS 1 / Type2
>
> Agency 2 23456
> COMPLAINTS 12 / Type1 6 / Type2
> COMPLIMENTS 16 / Type4 2 / Type7 12 / Type9
>
> Agency 3 34567
> 7 / TOTAL ORDERS
>
> Agency 4 45678
> 842 / TOTAL ORDERS
> 8 / RETURNS
> COMPLIMENTS 6 / Type4 7 / Type3 6 / Type8
> SUGGESTIONS 2 / Type7
>
> Agency 5 56789
> COMPLAINTS 6 / Type7
>
> *****(NOTE: In fixed text, columns line up neatly at the "/" character.)
> My column headings in the output file will be Agency Name, Agency Code, Total Orders, Returns, Type1 Complaints, Type2 Complaints... etc, with a zero wherever there is no data present for that type. I'm using calculated fields to put the data into those columns. Example: if(Col1ROW1 = "COMPLAINTS" .And. Col3Row1="Type1"),Col2Row1,0)
>
> Here is my problem: If I have a 2-line trap in my template, data (COMPLAINTS, COMPLIMENTS, etc.) will be missing for Agency 1, 2 and 4. If I have a 7-line trap, Agency 1 is captured correctly, but Agency 3 data is trapped in the Agency 2 record, and the first line of Agency 5 (i.e. the trap) is caught in Agency 4. Since Complaints, Compliments, etc. are horizontal lables, it doesn't help to capture each column from line 2 to the end of the template, since there is then no way to match this value with the information that follows it in the row. Is there any good way to capture the information in the longer records? Is there some utility that will insert a few blank rows at the end of (or before) each record, so that I can have a 7-line trap without ever spilling into the next record? If anyone can tell me how to do that, or come up with a different solution, I'd greatly appreciate it!