New User Variable Row Count (guru trap)?

[rwells7947]

Hello All,

I'm a new Monarch user but have some experience with other similar software (OCIE).

I'm assisting a department that has been using old Monarch 8 models but no longer knows how to modify them for changes to their reports. :o

Specifically, I have a report that has a default record size of 3 rows which can expand to 15 rows.
I was reading an article on a so-called guru trap that seems promising but I'm having trouble implementing (plus I like how the author and originator think).(http://excelwithmonarch.com/extract/experimentation-produces-monarch-success). :cool:


I've got my detail setup to grab the first 3 lines and I'm interpreting the guru method to say I need to create append templates using the same trap or start masc as my detail for my potential 12 other lines...but when I create the append template the field highlight is showing me I'm on line 1 (i.e. line one of my original detail)...how do I tell this thing I need lines 4-15 only if they're populated and there's been no blank line?

Any help, comments, insights, good jokes, etc are appreciated. :)

Regards,

Robert


Sample of 2 records below:


1. 012345 1234567 ADAMS A 08/01/95 62 07/03/95 error
2. 15 / 10 620 31 43 01/01/94
3. 12345678 011 07/09/08 12 01/01/93 good
4. $4500 09 04/01/92 good
5. 06 01/02/09
6. 05 01/01/09
7. 06 01/03/91
8. 02 01/03/06

1. 123456 2345678 BROWN B 08/01/95 12 01/01/08

[Data Kruncher]

Hi Robert. Welcome to the Monarch forum!

Our initial experience with Monarch is very similar in that my introduction was to get to know how to use models that other people supplied to me that had been supplied to them, so when I asked about making changes, they had no answers for me. Time to fend for myself. Little did I know what was coming...

On that note, thanks for pointing out the link. It's nice to read comments like yours about that article.

As to your sample, am I correct in thinking that you have a three line detail template? We could append the lines 4 to 8 in one big field (which can then be sliced and diced with the TextLine function) using a guru trap append template.

It might be useful if you could post a slightly larger sample using the CODE tags to provide a more representative sample of your original layout.

Kruncher

[Grant Perkins]

Hi Robert,

When you say the default detail record size is 3 lines is that the smallest record size? Is there a 4th line available (possibly a blank line?)

The Guru trap most often seeks to work with name tagged fields using preceding strings to ID the fields but there are other options if the report plays into your hands. You seem not to have 'name tags' available for the fields so the other options may be required. Kruncher's block text field extract followed by a slice and dice seems a likely approach, though how you know what the 4 lines that look like dates are is another matter ...

Kruncher's request for a slightly larger data sample would be welcome at my end as well. If the sample is not as per report format please let us know. Sometimes the layout is part of the solution. Not sure here yet.


Grant

[rwells7947]

Good Morning Grant and Kruncher,

Thank you both for getting back to me so quickly.

I've posted a mockedup version of this report below.
As you can see, the action code line can expand quite a bit (max of 15 lines).

Thank you for your help. :D


[CODE]SMSP1-5DX Y O U R C O M P A N Y N A M E 02/20/04
FINAL REPORT GENERIC EDR ELECTRONIC DEFAULT REPORTING PAGE 1
------------------------------------------------------------------------------------------------------------------------------------
SERVICER SERVICER LN NO SHORT NAME DUE DATE ACTION DATE DESCRIPTION / STATUS OR EVENT BK CHP FC STP PER L MIT
NUMBER OCC / PROP COND INV DAYS PDUE CODE (S OR E) BK STA FC STA CLS OFFC
INV LOAN NO REASON CD DDLPI *------* COMMENTS *------*
BPO/APPRAISAL
------------------------------------------------------------------------------------------------------------------------------------
30 AND 60 DAYS

012345 1234567 ADAMS A 08/01/95 62 07/03/95 VETERANS AFFAIRS -- NO BID S 0 3
15 / 10 620 31 K3
0123456789 011 07/01/95


123456 2345678 BROWN B 08/01/95 0 4
03 / 10 852 31 Z7
1234567890 07/01/95

012345 1111145675 POTTER H 06/01/09 HD 10/12/09 MODIFICATION IN REVIEW E 2 F E
03 / 04 123 169 H4 10/11/09 SOLICITATION LETTER SENT E A FR
123344455 007 05/01/09 HE 10/09/09 INELIGIBLE FOR MODIFICATION E
100,000 H7 10/06/09 HAMP IN REVIEW E
H6 10/06/09 ELIGIBLE FOR HAMP E
H5 11/06/09 POSITIVE SOLICITATION RESPONSE E
HD 11/04/09 MODIFICATION IN REVIEW E
HD 11/02/09 MODIFICATION IN REVIEW E
74 10/12/09 BPO OR APPRAISAL OBTAINED E
43 10/15/09 FORECLOSURE STARTED S

[Grant Perkins]

Hi Robert,

Plan F.

Based on the mock up report ... and up to 15 line (more possible but you mentioned 15) of s or E descriptions.

Single template required. 3 line sample - or maybe 4 of that 100,000 on the 4th line of the last record is significant.

Map the fields as required.

For the description create a single field that from the Action code column right across to the S or E column. We can split the field later if required.

Go to the field Advanced properties and set the end on parameter to 'blank field values' or something else that gives you what you want should that highligh some anomalies.

See what you get. For multi row description use the MENU>WINDOW>Show field contents option. To extract the single rows see the TEXTLINE() function. To split further see the Text processing FUNCTIONS but come back here if you want a quick introduction to what sort of things are possible ...

Now all we need to know is what your output has to look like ... ;)

In a rush but hope this heads you in the right direction.



Grant

[rwells7947]

Hey Grant, thanks for getting back to me so quickly.

The template is highlighting all the data I need but I found a couple of problems when trying to verify.
I should have given you a sample of a few pages of data as it turns out that occassionally there is no blank line between records or page headers so the template is highlighting header and page break info but 99% of the records look clean.

Perhaps this is a good time to talk about exporting this data. :) In other systems I've just learned to live with pulling raw data and cleaning it up so if we have to do that here no biggee. :)

Again thank you for the help, this is great stuff. BD

[Grant Perkins]

Hi Robert,

Have you set up a page header template? The Page Header templat ehas some special properties that make it 'invisible' to the processing of the detail template and so with luck it should allow a record to wrap around - or not if effectively the last line of the detail record and the first line of the header record simply meet rather than wrap.

This works MOST of the time but on here we have in the past seen some interesting reports where the positioning (and indeed content!) of page headers are not entirely consistent page on page and therefore call for other tweaks to segregate them. You can also try a diffrent 'end of field' option. If all else fails 'none of the above' usually gives a result. Alterntaaively one may accept there there is no way to avoid the crud but that it can be excluded from the final in other ways. Filtering is an option when faced with challenging template selections but for data it is usually more likely that conditional processing is required. (So for example something like (if the field (or maybe field line in a multiline field) does not start with "xx ", we don't want it - that sort of thing.

Basically I would clean up in Monarch (hinting at that with the previous paragraph) before export. It's a Monarch strength. For export options you have a simple cut and paste through to fully automated options with various output types suported - especially if you have the Pro version. Or you could print the resulting table or summary/summaries as a report or searchable PDF file or ....

A little care is required if you are using V10 todevelop models but the users are limited to V8. If that is not an issue because all are up to V10 you can orget I mentioned it. If there is a mix and you nneed to be backward compatible I can't recall off the top of my head what needs care. Others may have an instant input for that based on experience.

HTH.


Grant

[OllyInMunich]

Hello Robert,

It is annoying when reports don't behave consistently - the cleverest choice of templates and subtlest trapping doesn't get you the data you want...

There's three things to check - Options > Input has a button to ignore unused print control characters, which may be messing up the page & line breaks, another approach is to use the Prep utility to either pad extra blank lines everywhere or remove duplicate blank lines, and a third would be to run a script against the data.

There's an extreme option which is to filter your way out of trouble... Make a preliminary model which has a single line detail that traps every single line of your report. Then add calculated fields for Page() and Line(), filter out the page header lines and blank lines you don't need, export as a fixed width text file from a summary grouped on page and line, and then you should be able to use the key fields from the summary to control the behaviour of the appends/footers in a new model.

HTH,

Olly

[rwells7947]

Hello Olly and Grant,

The page header and print character removal ideas together appear to have done the trick and the correct data elements are highlighted.

A moment ago I typed that I wasn't sure on where to find the calculated fields...I appear to have put my brain back in gear and realized calculated fields equals tables, lol...which promptly led me to find them. :D
Based on other posts, I am closing in on getting my first sample together but I did have one question...does the textline function have a maximum number of characters that it can hold?
I seem to lose some lines if I include too much text but if I keep to just a code and date value I can so far hold 120 characters.


Thank you all for your help, you gentlemen are awesome. :)

[OllyInMunich]

Hello Robert,

If you create a field called, say, MyField, defined as Textline(Sourcefield;1), it should contain the entire first line of the source field. If you double click on the fields, you'll see in the Field Properties the Data Length option - you should be able to get this up to 254 characters in both.

If you need to go beyond that, you'll need to join multiple fields together as a Memo field.

HTH,

Olly

[Grant Perkins]

Hi Robert,

"I appear to have put my brain back in gear and realized calculated fields equals tables, lol...which promptly led me to find them. :D"

You bring back memories for me ....

Don't know about you but I find things stick around a little longer if I have had need to discover them rather than be told explicitly what to do to get there. Monarch, discovery and experimentation fit well together in my experience.

Apart from that - what Olly said.

Your calc field for an entire SINGLE line of the DESC field should not need to be bigger than the original field width using TEXTLINE as I envisaged it. You mentioned you have possibly 15 lines of desc fields? (Maybe more?) You would need one calc field per line to get them all separated. Having got the first field DUPLICATE the field and then change the line to 2, next one 3, then 4 extc for each new field.

You can also split that if you need to separate the codes and descs and stuff. But you will need more fields ....

In the table window check out the Show Field Contents option I hinted at earlier. Its useful to have familiarity with it.

HTH.


Grant